SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

MDF File Password Confusion

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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...