12

Is it possible in SQL Server to determine whether mixed mode authentication is enabled without logging on to SQL Server?

15

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
| improve this answer | |
18

You can check it in the registry:

HKLM\Software\Microsoft\Microsoft SQL Server\ [instancename] \MSSqlServer

The current mode is recorded in the LoginMode key.

Values (DWORD) can be:

  • 1 = Windows Authentication mode
  • 2 = SQL Server and Windows Authentication mode

Note:

  • 0 is equivalent to 2
  • You still need access to the Registry (and the server)
| improve this answer | |
2

You can also do this via PowerShell:

import-module sqlserver
$sqlserver = "ServerName"
$srv = Get-SqlServer -sqlserver $sqlserver
$srv.LoginMode

This will return output like this:

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.