How to Configure the sa User Password in SQL Server

This article outlines how to change the server authentication mode in SQL Server and, specifically, how to configure the password for the sa user, using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL). During SQL Server installation, the Database Engine is configured for either Windows Authentication mode or SQL Server and Windows Authentication mode (mixed mode). You can modify this authentication mode at any time post-installation.

Initially, if Windows Authentication mode is chosen during setup, the sa login is disabled, and the installation process assigns a password. Even when you later switch to SQL Server and Windows Authentication mode, the sa login remains disabled by default. To utilize the sa login, you must explicitly enable it and set a new password using the ALTER LOGIN statement. The sa login can only connect to the server using SQL Server Authentication.

Important Considerations Regarding the ‘sa’ Account

The sa account is a well-known SQL Server account and is frequently targeted by malicious users. It is strongly recommended not to enable the sa account unless absolutely necessary for application requirements. If you must enable it, using a strong, complex password for the sa login is crucial for security.

You can enable the sa login and configure its password using either SSMS or Transact-SQL.

Using SQL Server Management Studio (SSMS):

a. In Object Explorer, expand Security, then expand Logins, right-click on sa, and select Properties.
b. On the General page, you might need to create and confirm a new password for the sa login. Ensure this password is strong and securely stored.
c. Navigate to the Status page. In the Login section, select Enabled, and then click OK.
Using Transact-SQL (T-SQL):

The following example demonstrates how to enable the sa login and set a new, strong password. Important: Replace <enterstrongpasswordhere> with a robust and secure password before executing this script.
```
ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterstrongpasswordhere>';
GO
```

Changing the Server Authentication Mode using SQL Server Management Studio

Connect to your SQL Server instance using SQL Server Management Studio.
In Object Explorer, right-click on the server instance name (the root node) and select Properties.
In the Server Properties dialog, navigate to the Security page.
Under Server authentication, choose the desired new server authentication mode. Select Windows Authentication mode for Windows Authentication only, or SQL Server and Windows Authentication mode (Mixed mode) to enable both. Click OK.
A SQL Server Management Studio dialog box will appear, informing you that the authentication mode change requires a server restart. Click OK to confirm.
In Object Explorer, right-click on the server instance again, and then click Restart. If the SQL Server Agent service is running, it should also be restarted for the changes to fully take effect.

Examples of Changing Authentication Mode via Transact-SQL

Caution: The following examples utilize an extended stored procedure to directly modify the server registry. Incorrect modifications to the registry can lead to severe problems, potentially requiring operating system reinstallation. Modify the registry at your own risk. Microsoft cannot guarantee that issues resulting from incorrect registry modifications can be resolved.

The permissions needed to change the authentication mode are sysadmin fixed server role or CONTROL SERVER permission.

Example A: Switching to Windows Authentication Mode Only

Execute the following Transact-SQL command to change the server authentication to Windows Authentication mode only:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 1;
GO

It’s best practice to disable the sa account when using Windows Authentication mode exclusively for enhanced security:
```
USE [master]
GO
ALTER LOGIN sa DISABLE;
GO
```

Example B: Switching to Mixed Mode Authentication (Windows and SQL Server)

Before enabling mixed mode, it’s essential to enable the sa account and set a strong password if you intend to use it. Remember to replace <enterstrongpasswordhere> with a strong, secure password.
```
USE [master]
GO
ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterstrongpasswordhere>';
GO
```
Execute the following command to change the server authentication mode to Mixed Mode (SQL Server and Windows Authentication):
```
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 2;
GO
```