Categories
Tech

Changing Server authentication mode in SQL Server

Right click instance name > Properties > Security > Server authentication

Short version

GUI: Right click instance name > Properties > Security > Server authentication

Registry:

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\

Change LoginMode from 1 to 2

Full version

You’ll need SQL Server Management Studio (SSMS) to connect

Connect to SQL server

Changing Security authentication mode

Right click the instance name and click Properties

Select the Security page

Change Server authentication to Windows Authentication mode for Windows logins only or SQL Server and Windows Authentication mode to allow local SQL logins and Windows logins. Click OK to save changes.

Restart SQL

Right click the instance name and click Restart

You will see a UAC prompt and a SSMS prompt to confirm the restart

Adding users

Go to instance name > Security and right click Logins then select New Login…

Enter Login name and select authentication type. If you’re creating a SQL user you will also need to enter a password.

Uncheck Enforce password policy (you probably shouldn’t) if you want to use a password that does not follow your machine/domain password policy.

Setup any Server Roles and User Mappings if needed. Click OK.

Registry

You can also make this change in the registry if you can’t log in with windows authentication.
Go to
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<SQL version>\MSSQLServer
I’m using SQL Server 2019 in this example so it would be

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer

Change LoginMode from 1 to 2 then restart the SQL service.
1: Windows Authentication mode
2: SQL Server and Windows Authentication mode

Error 18456

If you’re getting error 18456 when connecting after changing security authentication modes make sure to restart SQL after the change.

Still locked out? See this post for creating a local SQL admin user.

Leave a Reply

Your email address will not be published. Required fields are marked *