MDF File Password Confusion

, 2016-04-07

I had never seen this, but I ran across a blog that mentioned an MDF File password here. The post really looks at ways to reset the administrator password for the “sa” account in SQL Server. However it has some mistakes and issues. I tried leaving a comment, but comments are disabled.

With that in mind, I decided to respond to a few things and clear up confusions.

With regards to the post, I think it’s confusing in that the text notes an MDF file password, but all the instructions are really about resetting the “sa” account password. sa is the built in sysadmin account in SQL Server, which isn’t related to the MDF file. The MDF file is the extension of the main data file for a database. You can change this, but there isn’t a good reason to do so. Note, the .ndf files are the same format, though by convention, these are the 2nd, 3rd, and other files added to a database.

There also isn’t a password on these files. I can open them in notepad (not recommended) or xvi32, and there isn’t any requirement if I have read access in NTFS to the file. It doesn’t matter if this is the master database or any user database. If you have NTFS permissions, you can read the file.

Now interpreting is different. SQL Server interprets this, and it requires permissions itself to access the server process, either sysadmin, or normal login. However, you can use ORCAMDF or MDF Viewer, or some other tool to read the files. The information contained in an mdf/ndf file is just formatted in a certain way. If you spend a lot of time, you will understand how to interpret the format.

Changing the sa password requires that the SQL Server service be running and you connect in some way. The post gets the methods right, but says that you must stop the service, which is only needed if you access the file some other way (ORCAMDF, xvi32, etc.). If you want to change the sa password, there are a few choices:

  1. USE SSMS
  2. Use SQLCMD
  3. Use osql
  4. Use one of the above methods with SQL Server restarted in single user mode
  5. Use a third party utility.

Any of these first four will work, and feel free to use whichever fits your situation. The last one is one I do not recommend as I can’t be sure any third party products will work correctly here.

Ultimately I’m a little embarrassed by this post, as it appeared through our syndication process on SQLServerCentral. We don’t review these posts, so there is no quality control. Most of the posts on this blog are good ones, but this one appears to be by a guest author and it’s one I’d ignore.

Filed under: Blog Tagged: security, sql server, syndicated

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads