Wednesday, July 16, 2014

SQL Server 2005 Forgot SA Password, How to Retrieve?

When forgot SA password and failed to login to SQL Server what can you do? As you know, the SA account is the super administrator account, without which you will have no privilege to create database in SQL Server. If forgot SA password, how can you retrieve the SA password to unlock the SA account and login to SQL Server with SQL Server Authentication.  To retrieve lost SA password, firstly look at the 2 situations below and see which situation you are in.

Situation 1: Forgot SQL Server SA password but still can login with Windows Authentication
Situation 2: Forgot SQL Server SA password and failed to login with Windows Authentication

If you are in the first situation you just need to login to SQL Server with Windows Authentication and change SA password. If you are in the second situation, should it be the only option to reinstall SQL Server? Of course not, you still can reset SA password without any login. Now take some minutes to browse through this article to see how to retrieve forgotten SA password on SQL Server 2005. If you are using SQL Server 2008/2012/2014, the methods will also perfectly work.

Aiming at the 2 situations above you will have 2 options to retrieve the SA password.

Option 1: Retrieve SA password by Windows Authentication
Option 2: Retrieve SA password by SQL Password Geeker

Option 1: Change SA password by Windows Authentication

When forgot SA password but you still can login to SQL Server with Windows Authentication, then you can change the SA password.

Step 1: Open the SQL Server Management Studio. Log in to with Windows Authentication.

Step 2: Click on New Query on the tool bar.



Step 3: Type sp_password Null, 'NewPassword', 'sa' on the blank space in Query window. Then click on Execute. It appears a message saying "Command completed successfully" which means your SA password has been changed successfully.




Tips: In the command sp_password Null, 'NewPassword', 'sa', the "NewPassword" is your new sa password and "Null" means your forgotten sa password. And you have executed this command to retrieve a new password.


Option 2: Retrieve SA password by SQL Password Geeker


When forgot SA password and failed to login to SQL Server with Windows Authentication, use SQL Password Geeker to reset the SA password.

Step 1: Stop SQL Server services.

Step 2: Download, install and run SQL Password Geeker.
Step 3: Click on Open File button. Navigate to the disk where your SQL Server is installed. And go to Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf. Select the master.mdf file and click on Open button to import the file to SQL Password Geeker.




Step 4: Select the sa user and then click on Reset button.




Step 5: Type a new password for your sa account. Click OK to finish. A dialog saying "password changed successfully" will pop up to tell you have successfully reset the sa password.

By the steps above you have retrieved a new sa password. Then do not forget to restart SQL Server services and log in to SQL Server by the new sa password.


how to change SQL Server Windows authentication mode to mixed mode


When connecting to SQL Server, you will need to select one authentication mode. Some users chose Windows Authentication mode when installation, but later needed to change it. This article explains how to change SQL Server Windows authentication mode to mixed mode.

You need to:
  1. Change SQL Server Authentication mode.
  2. Enable SA account.

Change Authentication mode with SQL Server Management Studio


If the SQL Server Management Studio is installed when installing SQL Server, you can change the authentication with it.

Step 1: Launch SQL Server Management Studio. (Generally, when SQL Server installation is complete, SQL Server Management Studio will be added to your Windows Start screen. Just click on Start -> SQL Server Management Studio to quickly launch it.) Then enter server name and select Windows Authentication mode to connect.


Step 2: In the Object Explorer, right click on the root directory and select Properties.



Step 3: The Server Properties dialog box pops up. Select the Security page. Pick the "SQL Server and Windows Authentication mode". Then click on OK.



Now you have changed Windows Authentication mode to mixed Authentication mode.
Important: To connect to server with SQL Server Authentication mode you firstly need to enable the sa user which is disabled by default.

Enable SA account

  1. In Object Explorer, expand the root directory and go to Security -> Logins -> sa
  2. Double click on the sa to open login properties dialog. 
  3. Select General page. Remove the default sa password and type a new one.
  4. Select Status page. Check Enabled.
  5. Now you can disconnect Object Explorer and exit SQL Server. Then re-connect with SQL Server Authentication mode. And you will need to type sa password to login.

How to Recover Lost SA Password on Microsoft SQL Server 2012


"I lost my SA password on SQL Server 2012 and have no any other system administrator accounts. Now I can’t connect to SQL Server instance. What can I do? Is there any easy method to recover SA password? Please help! Thank you in advance!"

When SQL Server lost SA password, you can reset SA password by login to SQL Server with Windows Authentication. If the Windows Authentication is disabled or you failed to login with Windows Authentication, you can recover SA password by SQL Password Geeker.

What can SQL Password Geeker do?

  1. With SQL Password Geeker you can view all SQL Server users without login, so that you can select any one user to reset its password.
  2. SQL Password Geeker can reset SA user or other users passwords for Microsoft SQL Server 2012/2014/2008/2005/2000.

Recover SA password by SQL Password Geeker


Take several steps to use SQL Password Geeker to recover SA password in SQL Server 2012.

Step 1: Stop SQL Server services. (Go to "Start -> All Programs ->Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager".)

Step 2: Download and install SQL Password Geeker on your computer.

Step 3: Run SQL Password Geeker. Click Open File button.



Go to the disk where your SQL Server is installed. Navigate to Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Select the master.mdf file and click on Open button.


Step 4: Now the .mdf file is mounted in "Asunsoft SQL Password Geeker" and all SQL Server users including the sa user are displayed. Click and highlight the sa user whose password is unknown and then click on Reset button.

select sa user and click reset

Step 5: In the Reset Password dialog box, type a new password and click OK.




It pops up a small dialog box saying "Password successfully changed" and you can see your sa password has changed to "asunsoft123" from "Unknown". Click on OK to close the dialog.


So you have recovered the SA user. Close the SQL Password Geeker.

Important: When sql server sa password recovery is performed successfully. Don't forget to restart SQL Server services before you connect to your SQL Server instance. 

SQL Server SA Account Locked Out, How to Unlock It?


"I just logged in to SQL Server 2008 with SQL Server Authentication mode but was stopped by the error message saying "Login failed for user 'sa' because the account is currently locked out. The System administrator can unlock it." What’s the root cause? How can I unlock my SA account? Anybody can help? Thanks in advance!"

To this problem, there are 2 possible causes to SA account locked out:

Cause 1: The SA account is enforced to use password policies.


Cause 2: The entered SA password is incorrect

2 solutions to SA account locked out:

Solution 1: Change sa login properties in SQL Server Management Studio
Solution 2: Reset SA account password


Solution 1: Unlock SA account in SQL Server Management Studio


Change SA account login properties in SQL Server Management Studio to unlock SA account.

Step 1: Run SQL Server Management Studio.

Step 2: In the Object Explorer, expand the root directory. Go to Security -> Logins.

Step 3: Double click on the sa user.

Step 4: Select General page. Enter a new password.

Step 5: Select Status page. Uncheck "Login is locked out". Then click OK.




Solution 2: Reset SA password to unlock SA account


Use Asunsoft SQL Password Geeker to quickly reset forgotten/lost SA password.

Step 1: Stop SQL Server services.

Step 2: Download, install and then launch Asunsoft SQL Password Geeker.

Step 3: Click on Open File button to import a master.mdf file which is generally located in D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf. ("D" is the disk where your SQL Server is installed.)

Step 4: Select the SA account and then click on Reset button.




Step 5: Type a new password then click OK.

The SA password will be changed successfully. Remember the new password and use it to unlock your SA account.

Note: Do not forgot start all SQL Server services before connecting to SQL Server.

2 Methods to Change SA Password in SQL Server 2008


There are many cases where we need to change SQL Server SA password. For example, when forgot or lost the SA password and locked out of SA account you will need to reset the sa password, or when you enable the SA account for the first time you need to change its default password which is unknown to a new one. Normally, there are 2 methods to change SA password for SQL Server 2008/2005/2012/2014.


Method 1: Use SQL Server Management Studio to change SA password


Log in to SQL Server Management studio by Windows Authentication mode and change SA password. Do as following steps:

Step 1: Launch SQL Server Management studio. Login to with Windows Authentication mode.

Step 2: Under the root directory in Object Explorer, go to Security -> Logins -> sa folder. Double click on sa account to open Login Properties dialog.

Step 3: Select General page and change the SA password.



When SA password has been changed. Make sure the SA account has been enabled and all SQL Server services are running. Then you can login to SQL Server with the new SA password with SQL Server Authentication mode. 

Method 2: Use SQL Password Geeker to change SA password


SQL Password Geeker is third party software, with which you can reset SA user or other users passwords for SQL Server 2000/2005/2008/2012/2014.

Step 1: Download and install Asunsoft SQL Password Geeker.

Step 2: Stop services in SQL Server.

Note: To reset SQL Server SA password by SQL Password Geeker, it is a required step to firstly stop SQL Server services. (Open SQL Server Configuration Manager to stop services.)

Step 3: Launch Asunsoft SQL Password Geeker. Click Open File button to open the master.mdf file (.mdf).

Tips: The .mdf file is located in D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf and the "D" is the disk where your SQL Server is installed.

Step 4: Select the SA account. Click on Reset button.



Step 5: Type a new password and click on OK. Your SA password will be changed successfully.

Important: When your SA password is changed successfully, do not start SQL Server services before you login to SQL Server with the new SA password.