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


3 Answers 3


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 LoginMode key.

Values (DWORD) can be:

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


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

You can also do this via PowerShell:

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

This will return output like this:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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