DB-유

SQL Server Authentication Mode: Windows vs Mixed Mode

· mssql, security, windows

Last month a vendor sent me an installer that only knew how to connect with a SQL login, and the instance it was supposed to talk to was Windows-only. That's the usual story. Every instance runs in one of two modes: Windows Authentication, where only Windows accounts and groups get in, or Mixed Mode, where SQL Server also keeps its own logins and passwords. You pick it at setup, but it isn't permanent. I get asked to flip it fairly often, either because a vendor insists on a SQL login, or because someone chose Mixed Mode "just in case" and the security team now wants it locked down.

It works the same on 2012, 2014 and 2016. You need sysadmin and the right to restart the service, since the change only applies after a restart.

Which mode am I in?

The quickest way is a T-SQL query using SERVERPROPERTY:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
         WHEN 1 THEN 'Windows Authentication only'
         WHEN 0 THEN 'Mixed Mode (Windows + SQL Server Authentication)'
       END AS AuthenticationMode;

You can also see it in SQL Server Management Studio: right-click the instance in Object Explorer, choose Properties, then the Security page. The setting is under Server authentication.

What each mode gives you

Windows Authentication uses Kerberos or NTLM to authenticate the caller. Passwords are never sent to SQL Server, password policy and lockout are handled by Active Directory, and you can grant access to an AD group instead of individual users. For an application running under a domain service account, this is the cleanest setup.

Mixed Mode adds SQL Server logins such as sa or app_user. These are needed when:

  • clients are not domain-joined (a Linux web server, a workgroup machine, a vendor appliance)
  • the application only supports a connection string with User ID and Password
  • you need a login that works even when the domain controller is unreachable

The cost is that you now have passwords to manage inside SQL Server, and the sa account becomes a real attack surface.

Switching the mode

In SSMS, open Server Properties → Security, pick the mode, and click OK. SSMS tells you the change takes effect after a restart.

I usually script it anyway, so the change is written down somewhere. The setting is a registry value named LoginMode under the instance's MSSQLServer key. For a default instance of SQL Server 2016 the path is:

$key = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer"
# 1 = Windows Authentication only, 2 = Mixed Mode
Set-ItemProperty -Path $key -Name LoginMode -Value 2

For a named instance, replace MSSQL13.MSSQLSERVER with MSSQL13.<InstanceName>. The MSSQL13 prefix is for 2016; 2014 uses MSSQL12 and 2012 uses MSSQL11. If you are not sure, look under HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL.

Then restart the service:

Restart-Service -Name MSSQLSERVER -Force

-Force is needed because dependent services such as SQL Server Agent must stop first. For a named instance the service name is MSSQL$InstanceName.

The sa account

If you switched to Mixed Mode, the sa login is now usable. It is disabled by default when the instance was installed in Windows-only mode, and I'd keep it that way. Create a dedicated SQL login for the application instead:

CREATE LOGIN app_user WITH PASSWORD = 'A-long-random-password-here!',
    CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
USE AppDb;
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;

If sa must exist for some reason, at least rename it and set a strong password:

ALTER LOGIN sa WITH NAME = [srv_admin_x];
ALTER LOGIN [srv_admin_x] WITH PASSWORD = 'AnotherLongRandomPassword!';

Going the other direction, from Mixed Mode to Windows-only, does not delete SQL logins. They just stop working, and somebody's nightly job will tell you about it. Check who is going to be affected first:

SELECT name, is_disabled, create_date
FROM sys.sql_logins
WHERE is_disabled = 0
ORDER BY name;

Did it work?

After the restart, run the SERVERPROPERTY query from the top again. Then test a SQL login with sqlcmd:

sqlcmd -S localhost -U app_user -P "A-long-random-password-here!" -Q "SELECT SUSER_NAME(), AUTH_SCHEME FROM sys.dm_exec_connections WHERE session_id = @@SPID"

AUTH_SCHEME shows SQL for a SQL login and KERBEROS or NTLM for a Windows login, which is a handy way to see how your Windows clients are actually authenticating.

Errors I keep seeing

  • Login failed for user 'app_user'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. (Error 18456, State 58) — the mode is still Windows-only, or you forgot to restart after changing it.
  • Login failed for user 'app_user'. Reason: Password did not match that for the login provided. (State 8) — the mode is correct, the password is not. State 8 is only visible in the SQL Server error log; the client always sees a generic 18456.
  • Login failed for user 'DOMAIN\svc_app'. Reason: Could not find a login matching the name provided. (State 5) — a Windows account with no login mapped. Either create a login for it or add it to a group that already has one.
  • Cannot use Kerberos, falls back to NTLM — usually a missing SPN for the service account. Run setspn -L DOMAIN\svc_sql and register MSSQLSvc/host.domain.local:1433 if it is not listed.

My rule: Windows Authentication wherever the client is domain-joined, Mixed Mode only when a specific application can't live without it, and sa disabled either way.