Changing SQL Server Authentication Mode with SSMS: A Complete Guide

Changing SQL Server Authentication Mode with SSMS: A Complete Guide

Authentication is necessary for establishing connection between two servers or databases. In SQL server, you’re going to find two different authentication modes. This article is about how to switch authentication modes using SSMS.

Types of Authentication Modes in SQL Server

As already mentioned, there are two authentication modes in SQL Server, which are:
  • Mixed Mode
  • Windows Mode
Mixed Mode is one of the two authentication modes in SQL Server. It offers two ways to connect to the database. One is through SQL Server Logins at SQL Server layer. And the other is via Windows supplemented with local SQL Server user accounts. In the second case, the login in both created and mapped on SQL Server Instance.

Windows Mode only allows one type of database connection. And that is through a valid Windows user account. This mode doesn’t support access to SQL Server logins.

Change SQL Server Authentication Mode using SSMS

Depending on the scenario, you may have to switch between the two modes. You can do that via SSMS or SQL Server Management Studio.

Here are the steps involved:
  • Launch SSMS
  • Connect to the SQL Server instance
  • From the SQL Server Management Studio Object Explorer, right-click on the server and then select Properties
  • Navigate into the Security pages and scroll to the Server authentication section
  • Find the two authentication modes and select your preferred choice. Then click on OK
  • When a popup appears, click on OK once again. It’d restart the SQL Server
  • Lastly, go to Object Explorer and right-click on the server and select Restart. If SQL Server Agent is also functional, you’d have to restart that as well
Admins who are making the switch from Mixed mode from Windows mode have to enable SQL Server login sa account. That’s because it gets disabled when you’re in Windows mode.

Here are the steps to enable it:
  • Navigate to Object Explorer > Security > Logins
  • Right-click on sa and click on Properties
  • Then go to General page and create a password for sa login
  • Next head over to the Login section from the Status page
  • Click on Enabled and then OK
Change Authentication Mode using T-SQL Statement

Microsoft offers another way you can change authentication mode in SQL Server. For this, you need to have T-SQL Statements. You just have to run a few scripts to change the mode. But before that it’s recommended to take a backup of the Windows registry to avoid losing files in case of mishaps.

Then run the following scripts to change the authentication mode to Windows:

Code:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 1
GO
To switch to Mixed mode, you have to use the following T-SQL statement and enable sa login in the process:

Code:
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'STRONG-PASSWORD' ;
GO
So that’s how you change the SQL Server Authentication Mode using SSMS and T-SQL Statement. For further assistance, contact the hosting support team.
Author
kumkumsharma
Views
1,683
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from kumkumsharma

Top