How to change Server Authentication mode in SQL Server?

During the installation process of SQL Server, user is prompted to choose the Authentication mode which is one of two:

  • Window Authentication mode
  • SQL Server and Window Authentication mode

It is recommended to use the second option – mixed mode. However, assuming that you have chosen Window Authentication mode and later on you decided to change to mix mode. This can be done and the easiest way to do is by following these few steps:

1. Open SQL Server Management Studio (SSMS) and login using Window Authentication:

2. Once logged in, create a new Login user or enter a password for the user sa. To do this, go to:
Security folder > Logins > sa  and double click on the selected sa user. Enter the password and click OK:

3. Now that we have a SQL server user, we can change the Authentication mode to mix mode. Right click on SQL Server icon and choose Properties:

4. From the left side menu, choose Security page and under Server authentication choose SQL Server and window Authentication mode. Click OK to finish:

5. A message box will appear that says you must restart SQL Server before changes take effect. To do this go to Run and type in services.msc then Click OK:

6. Windows Services dialog box will appear. Search for SQL Server under the list of names. Then select it and click on the re-start button.

7. Go back to your SSMS and create a new Database Engine connection. Under Authentication, choose, SQL Server Authentication and type sa under Login followed by the password in the password box. Then click on connect:

8. Finally, we can see that we are connected using the user sa:

You must be logged in to post a comment.


Cheap Reseller Hosting

Best European ASP.NET 4.0 Hosting Provider

Best Blog Hosting Provider