Voice of the DBA

Tag: security

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

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

  • #SQLNewBlogger–Adding Local Accounts

     

    What do you do if you need a process running under Local Service to connect to your SQL Server? Most of the advice out there is to change the login account. I actually agree with that, but there are times you can’t, or don’t want to.

    There are certainly times when I’ve seen some automated process use one of these accounts:

    • NT Authority\Network Service
    • NT Authority\Local Server

    Often this is because someone doesn’t want to bother to learn how to enable other accounts for their application, which isn’t a good excuse. In my case, I had a local VSTS agent service running as part of a demo, where I had very limited rights. I couldn’t affect a change, and I needed to get a new login for SQL Server.

    I searched a bit, but most advice said to just change the account, after all, if you had a process connecting from another machine, Local Service won’t work. However I found one item on Stack Overflow that helped.

    Here’s my Login list. As you can see, I have Network Service, but not Local Service.

    2016-03-25 12_50_57-Alarms & Clock

    I the run this code:

    CREATE LOGIN [NT AUTHORITY\LOCAL SERVICE] FROM WINDOWS;

    This gives me a new login.

    2016-03-25 12_52_48-Alarms & Clock

    In my situation, I then had to add this to the dbcreator role, but I could treat this like any other login and assign the minimum privileges needed.

    SQLNewBlogger

    I had to solve this and decided to write about it. The writing took 10 minutes, the research was 15-20 minutes to find a good reference and experiment a bit.

    A good learning exercise, and all of you should know how to do this. Prove it with your own blog.