Monday, August 11, 2014

Causes and Solutions for SQL Server Login Failed for User SA


Dear compatriots! Have you ever encountered the situation where you connected to SQL Server but was stuck at the error message saying "Login failed for user 'sa'..."? I have had such an experience for many times. Fortunately the errors got resolved and I regained access to my SA account using. Now let’s have a look at various common errors, causes and solutions for SQL Server SA login failed for user sa depending on different error messages.

Error message 1: 

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection…



Cause: SQL Server Authentication mode is disabled.

Solution: 

Step 1: Login to SQL Server with Windows Authentication.
Step 2: Right click on your SQL Server instance and select Properties.
Step 3: Select Security page. Check SQL Server and Windows Authentication mode and click OK to save changes.



Step 4: Restart SQL Server services and connect to Server with SQL Server Authentication. Then you can login with the SA account.

Error message 2: 

Login failed for user 'sa'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470).



Cause: Obviously, your SA account is disabled.

Solution:

Step 1: Login to SQL Server with Windows Authentication.
Step 2: Select Status page. Check Enabled and click on OK to save changes.



Step 3: Restart SQL Server.

Error message 3: 

Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)



Cause: The entered SA password is incorrect.

Solution:

1. Re-type a correct SA password.
2. If forgot or lost the SA password, then select one of the following 2 methods to reset the SA password.

Method 1: Reset SA password by Windows Authentication

Step 1: Login SQL Server with Windows Authentication mode.
Step 2: In Object Explorer, under the instance folder go to Security -> Logins -> sa.
Step 3: Right click on sa and select Properties.
Step 4: Select the General page. Change the SA password and click on OK to save changes.



Step 5: Restart SQL Server and login with the new SA password.

When Windows Authentication mode is disabled, use the second method below to reset SA password.

Method 2: Reset SA password with Asunsoft SQL Password Geeker.

Step 1: Stop SQL Server services.
Step 2: Install SQL Password Geeker.
Step 3: Import the master.mdf file.
Step 4: Reset the SA password.



Step 5: Restart SQL Server services and re-login SQL Server with the new SA password.

Error message 4:

Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.



Cause: You may have had too many failed login attempts with wrong password which results in SA account locked out.

Solution: 

Step 1: Login to SQL Server with Windows authentication mode
Step 2: Go to Security -> Logins -> sa.
Step 3: Double click on sa.
Step 4: Select the General page. Uncheck Enforce password policy and click on OK to save changes.




Step 5: Click on New Query. Type alter login sa with password = ‘yourpassword’ unlock and click on Execute



Then your sa account should have been unlocked.

Error message 5:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The Server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections….



Cause 1: SQL Server is configured to allow local connections only.

Solution: 

Step 1: Click on Start -> All Programs -> SQL Server -> Configuration Tools -> SQL Server Surface Area Configuration.
Step 2: Click on Surface Area Configuration for Services and Connections.



Step 3: Expand the instance folder. Go to Database Engine -> Remote Connections.
Step 4: Check Local and remote connections. Then re-login to SQL Server.



Cause 2: SQL Server services are not running.

Solution: 

Open SQL Server Management Studio to start SQL Server services and then re-login to SQL Server.