How to Change SQL Server Authentication Mode using SSMS

Change SQL server authentication mode using SSMS with a little help from the experts at Bobcares.

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Services.

Let’s take a look at how our Support Team is ready to help customers change SQL server authentication mode using SSMS.

How to change SQL server authentication mode using SSMS

Authentication is a process that helps establish a successful connection. SQL Server offers us two different authentication modes in order to connect to the database instances as seen below:

• Mixed Mode:

  This mode offers two different ways to establish a successful database connection. While one uses SQL Server logins at SQL Server layer and the other uses Windows domain user authenticated using active directory, where the login is created as well as mapped on SQL Server instance.
• Windows Mode:

  This mode offers only one way to establish database connections. We have to map windows accounts on SQL Server before creating database connections. Furthermore, this mode does not offer access to SQL Server logins.

Moreover, the authentication mode configuration takes place during the SQL Server instance installation. However, we can change the SQL Server authentication mode after the installation as well.

An additional risk while using Mixed mode is that the encrypted SQL Server Authentication login password has to be passed across the network during the time of the connection. In some cases, the applications tend to store the password at the client.

How to Change Authentication Mode in SQL Server via SQL Server Management Studio

We can modify or set authentication mode in SQL Server with the following steps:

1. First, launch SSMS and connect to the target SQL Server instance.
2. Then, right-click the server in SQL Server Management Studio Object Explorer, and click Properties.
3. Next, head to the Security page, to see both authentication modes under Server authentication. We can select any as per our requirement and click Ok.
4. After that, click Ok when a popup window asks us to restart the SQL Server service.
5. Finally, right-click the server in Object Explorer and click Restart. In case SQL Server Agent is also running, we have to restart it as well.

In case we are switching to Mixed mode from Windows mode, we have to enable SQL Server login sa account as it tends to get disabled in the Windows authentication mode.

According to our Support Team, we can enable the SQL Server login sa account with these steps:

1. First, head to Object Explorer > Security > Logins and right-click sa, and select Properties.
2. Then, create and confirm a password for the sa login on the General page.
3. After that, head to the Login section on the Status page and click Enabled followed by Ok.

How to change Authentication Mode in SQL Server via T-SQL Statement

Microsoft offers an extended stored procedure that allows us to change authentication mode in the SQL Server with T-SQL statements. Our Support Techs recommend taking a backup of the Windows registry before running the following script to change authentication mode to Windows mode:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 1
GO

However, if we want to switch to Mixed mode, we have to enable sa login. This can also be done with the following T-SQL statement:

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'STRONG-PASSWORD' ;
GO

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to change SQL server authentication mode using SSMS.