Determine whether mixed mode auth is enabled without logging in?

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)

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 also do this via PowerShell:

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

This will return output like this: