Sunday, August 22, 2010

Diagnosing SQL Logon Failures

Today I had problems logging on to MSSQL 2008 R2 through SQL Server Management Studio. I read this really good blog post from the Microsoft SQL Product Manager Il-Sung Lee:

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

He said that the error state you receive is always 1 to prevent information disclosure to unauthenticated clients. To get the real error state you must go to the SQL "ERRORLOG" file located:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

On Il-Sung Lee blog post he has a list of all error states to do with authentication and a description of what the error state is.

In the ERRORLOG file it showed me what my problem was, so I installed the SQL Authentication component.

2010-08-23 09:52:29.05 Logon Error: 18456, Severity: 14, State: 58.
2010-08-23 09:52:29.05 Logon Login failed for user 'websense'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 172.16.1.14]

Very handy post by Il-Sung Lee

No comments:

Post a Comment