Is it possible in SQL Server to determine whether mixed mode authentication is enabled without logging on to SQL Server?
Another approach is to try logging in, using SQL authentication, with an obviously fake account that won't succeed. You can then use notepad to open up the
ERRORLOG file in
C:\Program Files\Microsoft SQL Server\$instance folder$\MSSQL\Log\ and see either this error...
Error: 18456, Severity: 14, State: 58.
Login failed for user 'polly_wants_a_cracker'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
This means mixed mode authentication is not enabled.
...or this one...
Error: 18456, Severity: 14, State: 5.
Login failed for user 'polly_wants_a_cracker'. Reason: Could not find a login matching the name provided.
This means mixed mode authentication is enabled.
I don't know that there is possibly a way to tell without either:
- logging in successfully
- having physical access to the box (or remote registry) in order to check either the registry configuration (as Julien describes) or the error log for the state/message when logging in unsuccessfully
You can check it in the registry:
HKLM\Software\Microsoft\Microsoft SQL Server\ [instancename] \MSSqlServer
The current mode is recorded in the
Values (DWORD) can be:
- 1 = Windows Authentication mode
- 2 = SQL Server and Windows Authentication mode
0is equivalent to
- You still need access to the Registry (and the server)