Simple Methods to Recover master.mdf File Password

Overview

MS SQL Server is the relational database management system used to store information in form of tables, views, indexes, triggers, constraints and stored procedures. Master Database File (MDF) is the main database file with the file extension .mdf used in SQL Server. These MDF files are protected with password in order to avoid unauthorized access. Sometimes, users forgot their password and they are unable to access their own database. The blog will be discussing ways to recover MDF file password to access the data stored in SQL Server database.

What If I Lost My master.mdf File Password?

The default user who created the database Server is SQL Administrator (SA). Administrator has right to create several other users and SQL Server stores their password in the Master MDF file. If any user forgets their SQL password, then they can ask SA, which can reset the user’s password. There are manual methods through which the user’s password can be changed i.e. by using SQL Server Agent Service or by using SQL Server Configuration Manager. Now, what will happen if we lost the password of MDF file that contains all the information of the users? We cannot reset the password since MDF file is the main file managed by the SQL Administrator. In this case, where the Password of MDF file is compromised or forgotten, we will not be able to access the SQL Server Database.

How to Recover master.mdf File Password?

First, we need to stop the SQL Server Service to prevent from blocking the Master data File (MDF) database of the SQL Server. Some manual methods that can help in recovering Password of MDF File, which are stated below:

1. Using SSMS Management Console

Steps for this method are as follows

  • To launch SSMS Management Console, go to Start > All Programs > Microsoft SQL Server(with version you are using) > SQL Server Management Studio Express
  • Login into SQL Server using Windows Authentication
  • In Object Explorer, Go to Server Name > Security > Logins. Right-click after selecting SA Account and go to Properties
    Using ssms
  • Login Properties window will be opened. Enter desired password in the ‘Password’ and ‘Confirm Password’ box and click OK.
    Enter Desired Password

2. Using SQL Script

Steps are as follows:

  • Launch SQL Server Management Studio
  • Open a new query and Type the command below:

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

Execute the query

3. Using Command Prompt

Method using Command Prompt involves following steps:

  • Go to Windows button and type cmd to launch Command Prompt
  • Type osql –L on the command prompt
  • Copy the full name of the SQL Server and type OSQL -S -E
  • Execute the following query: sp_password NULL, ‘’,’sa’
  • Then GO

Method with SA disabled

Method when you have SA disabled involves the steps that are as follows:

  • Start SQL Server as single user using –m flag using command line
  • Run as Administrator
  • Go to ServerRoles page and select sysadmin to reset password
  • Conclusion

    The blog discusses about the SQL Server Database that contains main storage file called MDF file. The master.mdf file is managed by SQL Server Administrator that contains all the users’ details and their passwords. Users protect the master.mdf files using password to protect it from unauthorized access. The blog gives possible solutions that would help users to recover master.mdf File password in case of lost or forgotten password. If the user is still not able to access the database after using these methods, the free SQL Server password recovery tool can be used to recover the password of the Master database file (MDF).

    Advertisements
    This entry was posted in Security, SQL, T-SQL. Bookmark the permalink.

    One Response to Simple Methods to Recover master.mdf File Password

    1. A few points:
      1) While MDF does stand for “master data file” and that is the best practice & default, none of the data files in fact have to have the MDF extension.
      2) Unless your database is “contained” it will not have any password information. That is stored in the master database (and yes in the master.mdf but don’t let that confuse you) which is a system database and a special case.
      3) If the SQL Server Service is shut down you won’t be able to connect at all. If you have any way to connect (sql server authenticated or windows authenticated) that has sysadmin permissions you can change the password without any effort in the manner you suggested.
      4) If your instance is in “Windows Authentication Mode” the sa login is disabled and can’t be re-enabled so changing the password is useless.
      5) Sysadmin is a role. Roles don’t have passwords.

      Lastly here is a link you can read about recovering admin access to an instance where all system administrators are locked out: https://msdn.microsoft.com/en-us/library/dd207004.aspx

    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