How to Recover SQL Server Password Manually?


SQL Server is a database which is used to store and retrieve the data. It runs on transact – SQL. In this the SQL administrator has a right to create a number of users with a unique username. By default, the password is same for all users and the user can change the password individually. The SQL Server stores its password as a hash in the Master.MDF file.

Different version of SQL server:

  • SQL Server 2005 code named “Yukon”
  • SQL Server 200 8 code named “Katmai
  • SQL Server 2008 R2 code named “Kilimanjaro”
  • SQL Server 2011 code named “Denali”
  • SQL Server 2014
  • SQL Server 2016

sql password recovery

If the user forgets the SQL password, then the user can ask the SQL SA (system administrator). It is because the SQL SA can easily reset the user’s password. But, what need to do if the administrator forgot the password?

First, we know the methods through which the user’s password changes. You can apply any of the below manual procedures:

Method 1: Using SQL Server Agent Service

Step 1: First, click Start and then go to All Programs -> Microsoft SQL Server (Your SQL version) -> Configuration Tools -> SQL Server Configuration Manager.

Step 2: Click on SQL Server Services, in the SQL Server Configuration Manager.

Step 3: In the details pane, right-click on the SQL Server Agent (<instancename>) -> Properties.

Step 4: A SQL Server Agent Properties dialog box open and here, on the Log On tab, type the Password and Confirm Password and then click on OK button. 

Method 2: Using SQL Server Configuration Manager

Step 1: Click Start, then All Programs -> Microsoft SQL Server (your SQL version) -> Configuration Tools.

Step 2: Now, click on SQL Server Configuration Manager. Here, click on SQL Server Services.

Step 3: Right click on SQL Server (<instancename>) -> Properties in the details area. Here, on the Log On tab, enter a Password and Confirm Password, and then click on OK.

Note: On an SQL Server, stand-alone instance the password change immediately, without restarting SQL Server. And, on an SQL Server clustered instance, you might take the SQL Server resource offline, and need to restart it.

Above, we’ve discussed about the manual procedure to reset the user’s password. Now, switch to the Master.MDF file. The Master.MDF is managed by SQL SA and this file stores all the information about users. If you lost your SA password then there are many ways to recover the password manually.

Note: To prevent from blocking the MASTER.MDF, stop the SQL Server service.

Method 1: Using SSMS management console

Step 1: Open SSMS management console. Check under Start -> All Programs -> Microsoft SQL Server (write version here without bracket) -> SQL Server Management Studio Express.

Step 2: Login into SQL Server using Windows Authentication.

Step 3: In Object Explorer, under Logins folder open Security folder. Right click on SA account and go to Properties.

using Windows Authentication

Step 4: Now, enter desired password in front of “Password” and “Confirm Password” field and click OK.

Windows Authentication.

Method 2: Using SQL Script

Step 1: Open the SQL Server Management Studio.

Step 2: Then, open a New Query and copy, paste, and execute the following:

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
GO

Note: Here, NewPassword is the password you would like to use in SA account.

Method 3: Using command prompt

Step 1: Go to the command prompt of the server and osql –L.

Step 2: Then, copy full name of SQL Server and type: OSQL -S <insert_servername_here> -E.

Step 3: Execute the following query: sp_password NULL, ‘<insert_new_password_here>’, ’sa’.

Step 4: Then, GO.

Method 4: Use this method, when you have SA disabled

Step 1: Start SQL server as single user using -m flag via command line.

Step 2: Run as Administrator. On the Server Roles page, select sysadmin and reset password.

 

After following, all the above methods, if you’re still unable to login to the SA account. Then, you have one option through which you can easily and effectively recover your SA password. Use third party SQL Server Password Recovery Tool. This tool has many unique features including auto detect, preview of MS SQL server users, crack password for individual as well as SA, etc. Watch the full demo of SQL Server Password Recovery Tool.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s