Change SQL Server Authentication Mode Using SSMS

Authentication is a process that assists in the establishment of a secure connection. SQL Server provides two distinct authentication modes for connecting to database instances, as illustrated below:

• Mixed Mode:

This mode provides two distinct methods for successfully connecting to a database. While one makes use of SQL Server logins at the SQL Server layer, the other makes use of Windows domain users verified through active directory, where the login is generated and mapped to the SQL Server instance.

• Mode Windows:

This mode provides a single method for establishing database connections. Prior to creating database connections, we must map Windows accounts on SQL Server. Additionally, this mode disables access to SQL Server logins.

Additionally, the authentication mode is configured during the SQL Server instance’s installation. Even so, after installation, we can change the SQL Server authentication mode.

Additionally, when using Mixed mode, the encrypted SQL Server Authentication login password must be transmitted across the network during the connection process. Occasionally, applications will store the password on the client.

How to Change the Authentication Mode of SQL Server Using SQL Server Management Studio

The following steps describe how to change or set the authentication mode in SQL Server:

1. To begin, open SSMS and establish a connection to the target SQL Server instance.

2. Then, in the SQL Server Management Studio Object Explorer, right-click the server and select Properties.

3. Following that, navigate to the Security page, where you’ll find both authentication modes listed under Server authentication. We can choose any option that meets our requirements and click OK.

4. Following that, when a popup window prompts us to restart the SQL Server service, click Ok.

5. At last, in Object Explorer, right-click the server and select Restart. If SQL Server Agent is also running, it must be restarted.

If we are switching from Windows to Mixed mode, we must allow the SQL Server login sa account, as it is disabled by default in Windows authentication mode.

The SQL Server login sa account can be enabled by following these steps:

1. To begin, go to Object Explorer > Security > Logins and right-click sa, then select Properties.

2. After that, on the General page, generate and confirm a password for the sa login.

3. Then, go to the Status page’s Login section and click Allowed, then Ok.

Changing Authentication Mode in SQL Server through T-SQL Statement

Microsoft provides an extended stored procedure that enables us to alter the SQL Server authentication mode using T-SQL statements. Before running the following script to change authentication mode to Windows mode, we suggest making a backup of the Windows registry:

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

Even so, If we want to switch to Mixed mode, we must first enable sa login. This can also be accomplished using the T-SQL statement:

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

Are you looking for an answer to another query? Contact our technical support team.