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:
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:
Here are the steps to enable it:
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:
To switch to Mixed mode, you have to use the following T-SQL statement and enable sa login in the process:
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.
Types of Authentication Modes in SQL Server
As already mentioned, there are two authentication modes in SQL Server, which are:
- Mixed Mode
- Windows Mode
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
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
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
Code:
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'STRONG-PASSWORD' ;
GO
