Voice of the DBA

Author: way0utwest

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

  • Quick Encryption with Always Encrypted

    What do you need to do in order to access data in a SQL Server that’s encrypted with Always Encrypted? It’s not much, and it’s really simple.

    1. The certificate used for encryption
    2. A parameter in the connection string

    That’s it. It’s a small list of things.

    I was experimenting with this, and I set up encryption on a VM, then copied the certificate backup to another VM and installed it in the Certificate Store.

    2016-03-28 17_56_00-Settings

    This is all I changed on my C# application to enable encryption.

    strConnstring += “; Column Encryption Setting = Enabled”

    I had a connection string built, and I added this one little option to the end and when I queried my encrypted table, I could read the data.

    There are certainly more caveats and more to learn about encryption, but this shows how easy it can be to change your application. Just alter the connection string.

  • Track Your Consultants

    If you pay attention to security issues in software, you’ve probably heard about man-in-the-middle attacks. These can occur more frequently than you expect, though inside of a company, it’s more unlikely that you’ll experience one if you have some fairly basic security controls on your network.

    However, if you invite someone inside of your network, such as a consultant of some sort, you should be more vigilent. There’s a great post showing how someone can execute a MITM attack against SQL Server. It’s in depth, showing exactly how packet captures lead to the ability to hijack a session and create a new login.

    If this seems like a lot of work, it is. However once the attack is built, this could easily be run by anyone inside of your network. I could see consultants running this type of attack and storing credentials they’ve created for use many months later. These could be sold later to someone that might use them in a website or other application to gather data from outside of your network.

    Defending against these types of attacks is hard. Certainly not allowing free access for consultants is key, though a quick command line execution of a script might not be something that’s easily noticed. At the very least, sysadmin logins should be monitored, and any changes to this list investigated immediately. However, I’d also say any built in server role changes should be checked and verified as being valid alterations. Even your SQL logins shouldn’t change without administrators being aware.

    Monitoring your systems is a big part of security. You might not prevent many of the attacks, but knowing they’ve taken place allows you to respond and potentially protect sensitive data.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Locking sa

    When I started working with SQL Server, the sa account was “the” account used for admin operations. This was the default account for many DBAs and as a result, it couldn’t be locked out.

    This changed in SQL Server 2005, which is a good thing. We don’t want unlimited attacks on the sa account with brute force password guesses. I wasn’t aware of this, as I haven’t had an issue with attacks in a long time. However Jeff Moden pointed out to me recently that we can lock out sa.

    I decided to test.

    First, I went to the local policy on my desktop and checked the security policy. No lockout was set, which probably makes sense for consumer OSes. I took a minute to then set my lockout to 5 attempts with a 30 minute timeout.

    2016-03-28 17_46_26-Settings

    I then restarted my SQL instance. I couldn’t get this to lock me out at first, so I decided to ensure the policy applied.

    I then tried logging in with the sa account 6 times with the the wrong password. Each time I got this message. Including the 7th time with the correct password.

    2016-03-28 17_39_02-SQLQuery1.sql - JOLLYGREENGIANT_SQL2014.master (sa (51)) - Microsoft SQL Server

    No note about being locked out. However when I check the properties for sa, I find the login is locked out.

    2016-03-28 17_39_25-Settings

    I could uncheck the box, but I can easily use T-SQL as well.

    ALTER LOGIN sa  WITH PASSWORD=’test’ UNLOCK

    Please don’t use a password like this. I actually ran this to test and then reset the password to something more complex.

    Reference

    ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

    SQL Authority – http://blog.sqlauthority.com/2009/04/23/sql-server-fix-error-18486-login-failed-for-user-sa-because-the-account-is-currently-locked-out-the-system-administrator-can-unlock-it-unlock-sa-login/