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.

Monday, August 4, 2014

How to Connect to SQL Server When Lost SA Password


As we know, there are 2 authentications to choose from when connecting to SQL Server. One is Windows Authentication and another is SQL Server Authentication. When connecting to SQL Server with SQL Server Authentication you are required to login with SA password. The problem is how to connect to SQL Server when SA password is lost.


SQL Server lost SA password 


The SA user is the super administrator user in SQL Server. If lost the SA password you will have no privilege to open and create SQL Server database. The SA user plays an important role in SQL Server just like the Windows administrator user plays an important role in Windows. So, you’d better not lose or forget it.


How to Connect to SQL Server when lost SA password ?


When lost SA password and cannot connect to SQL Server, you can connect to SQL Server with Windows Authentication and change the SA user password. Then you can login to SQL Server with the new SA password.

Important: If you even failed to connect to SQL Server with Windows Authentication, then you have to reset the SA user password by another way.


Reset SA password without login to SQL Server


When lost SA password and cannot to connect to SQL Server with Windows Authentication, you can use SQL Password Geeker to reset SA password without login to SQL Server. 

  1. Install SQL Password Geeker on your PC.
  2. Stop SQL Server services.
  3. Open SQL Password Geeker and import SQL Server master.mdf file.
  4. Select the SA user and reset its password.
  5. Close SQL Password Geeker and start SQL Server services.
  6. Connect to SQL Server with SQL Server Authentication with the new SA password.