Categories
Tech

Changing Server authentication mode in SQL Server

Right click instance name > Properties > Security > Server authentication

Short version

Right click instance name > Properties > Security > Server authentication

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.

Error 18456

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

Leave a Reply

Your email address will not be published.