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.

SQL Server supports two authentication modes: Windows Authentication and Mixed Mode. Windows Authentication uses Windows credentials to connect to the server, while Mixed Mode also allows SQL Server logins for applications and users that require SQL authentication. If you need to change SQL Server authentication mode or are looking for how to change SQL Server authentication mode after installation, SQL Server Management Studio (SSMS) provides a simple way to update the setting. In this article, we’ll show you how to change SQL Server authentication mode using SSMS and enable Mixed Mode if required.

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.

Prerequisites

If you plan to change SQL Server authentication mode after installation, make sure you have the following before making any changes:

• Administrative or sysadmin privileges on the SQL Server instance.
• Access to SQL Server Management Studio (SSMS).
• Permission to restart the SQL Server service.
• A maintenance window if you are updating a production server, as restarting the service temporarily disconnects active sessions.

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.

Verify the Authentication Mode

After you change the authentication mode in SQL Server, verify that the new setting has been applied successfully.

1. Connect to the SQL Server instance using SSMS.
2. Right-click the server name and select Properties.
3. Open the Security page.
4. Confirm that the selected authentication mode matches the one you configured.
5. If Mixed Mode is enabled, test a SQL Server login to confirm that it works as expected.

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

Common Issues After Changing the Authentication Mode

If you cannot change SQL Server authentication mode, one of the following issues could be preventing the update.

Issue	Possible Cause	Quick Fix
SQL Server login fails	SQL Server service was not restarted	Restart the SQL Server service.
Login failed (Error 18456)	Incorrect login credentials or login disabled	Verify the username, password, and login status.
Unable to change the authentication mode	Insufficient permissions	Sign in using an account with sysadmin privileges.
SQL logins are unavailable	Windows Authentication is still enabled	Confirm that Mixed Mode is selected and restart the SQL Server service.

Security Best Practice

After you change authentication mode SQL Server, follow these practices to help protect your server.

• Use Windows Authentication whenever it meets your application requirements.
• Enable Mixed Mode only when SQL Server logins are required.
• Create strong passwords for SQL Server accounts.
• Disable or remove SQL logins that are no longer in use.
• Review login permissions regularly and follow the principle of least privilege.

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

Conclusion

Now that you know how to change SQL Server authentication mode, you can update the server settings through SSMS, restart the SQL Server service, and verify that the new authentication mode is active. If your applications require SQL logins, you can also change SQL Server to Mixed Mode authentication by following the same process.