The error Login failed for user 'SA' frequently occurs when trying to establish a connection to a SQL Server database. In SQL Server, the 'SA' (System Administrator) account is the default administrative account. Users may encounter this error when their login credentials are not correct. This post will guide you through the error's potential causes and solutions.
What is the 'SA' Account?
The 'SA' account is a built-in SQL Server account with complete administrative access to the SQL Server instance. It is frequently utilized while configuring connections or during SQL Server installation. It's usually advised to disable this account or use it infrequently for security reasons.
Causes of the 'Login failed for user SA' Error
The login failed for user SA error occurs due to the following reasons:
- Incorrect Password: Using an incorrect password for the 'SA' account is the most frequent reason for this error.
- SQL Server Authentication Disabled: There are two modes of operation for SQL Server: Mixed Mode (Windows + SQL Server Authentication) and Windows Authentication mode. The 'SA' account cannot be used if SQL Server Authentication is turned off.
- 'SA' Account Disabled: During installation, the 'SA' account might be turned off by default for security reasons.
- Wrong Server Name: This error may appear if you attempt to connect to an incorrect or unavailable SQL Server instance.
- Locked Account: If there are too many failed attempts at login, the 'SA' account may be momentarily blocked.
Solutions to Fix the 'Login failed for user SA' Error
Check SQL Server Authentication Mode
If SQL Server runs in Windows Authentication Mode, the 'SA' account won’t work. You need to enable Mixed Mode to use SQL Server Authentication.
- Open SQL Server Management Studio (SSMS).
- Next right-click on your SQL Server instance in Object Explorer and choose the Properties option.
- Now go to the Security page.
- Next under the Server Authentication tab, choose SQL Server and Windows Authentication mode option.
- At last click on the OK button and restart the SQL Server service for the changes to get implemented.
Reset the 'SA' Password
If in case you have forgotten the 'SA' password, please follow the steps below to reset it:
- First, connect to the SQL Server instance with the help of Windows Authentication.
- Next open New Query and execute the command mentioned above to reset the 'SA' password.
ALTER LOGIN SA WITH PASSWORD = 'NewPassword';
Note: Make sure that the password complies with the server’s password policy.
Enable the 'SA' Account
If the error is caused due to disable the 'SA' account, you will have to enable it with the help of these steps:
- Open SSMS and connect using Windows Authentication.
- In Object Explorer, expand Security and then Logins.
- Right-click on the 'SA' login and select Properties.
- Under the Status page, ensure that Login is set to Enabled.
- Click OK.
Unlock the 'SA' Account
If the 'SA' account has been locked due to an excessive number of unsuccessful login attempts, you can unlock it by running the following command:
ALTER LOGIN SA WITH PASSWORD = 'NewPassword' UNLOCK;
To unlock the SA account please follow the steps below:
- First, open the SSMS and connect it using Windows Authentication.
- Next, open a New Query window and run the command mentioned above:
Verify SQL Server Instance
Attempting to connect to the incorrect SQL Server instance may occasionally be the cause of the problem. Make sure you are entering the correct Server Name in the SSMS connection dialog. The default server name when installing SQL Server locally is typically localhost or (local).