New application needs a SQL login but our instance was installed Windows authentication only, login attempts fail with error 18452 style refusals.
How do I switch to mixed mode and what should be done properly around the switch?
How to change the authentication mode in SQL Server
Solved Email & Outlook
AJ
Andrew Jackson August 21, 2018
2 replies
11,850 views
Reviewed by moderators
The switch is two clicks and a restart, so the value below is mostly in the properly around it:
1
In SSMS right click the server in Object Explorer, Properties, Security page, and switch Server authentication from Windows Authentication mode to SQL Server and Windows Authentication mode. OK saves it but nothing changes yet.
2
Restart the SQL Server service for the mode to take effect, SQL Server Configuration Manager, right click the instance service, Restart, in a moment that suits the applications riding the instance. Until this restart SQL logins keep failing exactly as before.
3
Create the application its own login rather than reaching for sa: CREATE LOGIN appname WITH PASSWORD = 'strong one', CHECK_POLICY = ON, then a user in the target database mapped to it with the minimum role that satisfies the application, db_datareader plus db_datawriter beating db_owner until the vendor proves otherwise.
4
Leave sa disabled, which mixed mode does not automatically change: verify with SELECT name, is_disabled FROM sys.server_principals WHERE name = 'sa', keep is_disabled at 1 or set it with ALTER LOGIN sa DISABLE, plus a rename for depth, since every attacker's first guess at a mixed mode instance is sa and a disabled renamed sa answers none of those guesses.
The no SSMS variant for completeness: the mode lives in the registry as LoginMode under the instance's MSSQLServer key, 1 for Windows only and 2 for mixed, editable when the graphical route is unavailable, same restart required. And error 18452 or 18456 persisting after the restart usually means the login's password or the database mapping rather than the mode, the error state number in the SQL error log distinguishes which.
Switched, restarted in the lunch window, application connected with its own least privilege login. sa confirmed disabled and renamed while I was in there. The 18456 state number note already paid off diagnosing a typoed password.