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
  
• Login Properties window will be opened. Enter desired password in the ‘Password’ and ‘Confirm Password’ box and click OK.
  

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:

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).