Voice of the DBA

Tag: encryption

  • Backing up the Service Master Key

    Making a backup of your Service Master Key (SMK) should be one of the first things that you do if you are enabling encryption. This key encrypts and secures your other keys, and it will be needed when you have to rebuild the server.

    Backing up the key is simple. You use the BACKUP SERVICE MASTER KEY command and it only has two arguments

    • File – The path and name of the file for the backup.
    • Password – provides security for the backup file. This is needed when restoring the file.

    That’s it. Performing a backup is as simple as running a command like this: 

    -- Backup the SMK
BACKUP SERVICE MASTER KEY
 TO FILE = 'c:\sqlbackup\MainServiceMaster.key'
 ENCRYPTION BY PASSWORD = 'S3cureP@ssword!sneeded'

    Securing this file, however, is a little harder, and managing it is really hard. Ideally you do not want this key stored with the backup files for the server, because you don’t want anyone to have this and the data. However if you must do that, and that might be the practical thing to do so that it’s available when you use this tape/disk to recover the server, I recommend you do not keep the password with this file. Store it in an admin system somewhere else, preferably a protected system with something like KeePass or Password Safe.

    A couple things to note. Just like with a backup, the service account needs write permissions on the path (local or UNC) to create the file. This command requires the CONTROL SERVER permission, so not anyone can use it.

  • Creating a Service Master Key in SQL Server

    The basis for all the encryption in SQL Server is the Service Master Key, which is the top of the encryption hierarchy. You can see the image below from Books Online of how this is setup.

    The service master key is automatically created when you create a key that needs it, usually a database master key. So there is no CREATE SERVICE MASTER KEY command.

    This key is encrypted and protected by the Windows DPAPI and the linked to the SQL Server service account. Since it secures all other keys in the encryption hierarchy, if you regenerate it, or restore it, all other keys it protects must be decrypted and re-encrypted. That can be a resource intensive operation, so don’t do that lightly.

    You can regenerate a service master key, and you do that with the ALTER SERVICE MASTER KEY command like this: 

    ALTER SERVICE MASTER KEY REGENERATE

    That’s it. If it works, no errors, no results. If an error occurs, you’d need to deal with it. However this isn’t something you should run often, and if you encounter errors while doing this, I’d suggest you immediately stop, backup all databases and master keys, and then work through the issues.

    In another post, I’ll talk a little about the alterations you can make to the Service Master Key and how to back it up or restore it.

  • Encryption Works

    If you have better encryption than this, it will work.

    I don’t know that the government hires the best people or they have the best tools to work with, but they have some expertise. There’s a short note from Bruce Schneier that full disk encryption (FDE) does appear to by preventing unauthorized access to computer systems by police in many cases.

    If you read the comments, there are all sorts of flaws and potential holes with encryption, which are valid arguments. However that doesn’t mean that you shouldn’t implement any encryption on your removable or portable drives, especially those in laptops. Those devices are like the locks on your home or car. They can be defeated by determined professionals, but all too often the casual criminal doesn’t bother if they are in place.

    Keep in mind many laptops are lost, stolen, or sold without wiping the drives. Most people that received the drive wouldn’t bother to try and decrypt it unless they were sure it had something valuable on the drive. Since it’s no guarantee that a laptop grabbed at Starbucks or from a car has valuable data, most likely the target is the hardware, not the disk contents.

    I think disk encryption is a good layer of defense in your security strategy and worth implementing. My laptops are encrypted, mostly because it’s an easy security mechanism, and I’m not always sure if something I have on there is sensitive enough to worry about. My recommendation is that you implement FDE if you can.

    You should also make sure you are backing up your data. Losing the laptop might not result in the release of sensitive data, but losing the data itself could be a major problem.

    Steve Jones

    The Voice of the DBA Podcasts

  • How Many Times Will This Happen?

    Laptops are being stolen with important data on them. Unencrypted.

    This editorial was originally published on Jun 6, 2006. Steve is traveling in the UK this week and we are reprinting a few pieces.

    Or is it a sign of the times and something that we’ll have in the news on a regular basis for the next few years? Another laptop theft with data, this time from Hotels.com. It was actually an Ernst & Young laptop, the firm that audits hotels.com, but still.

    This makes me feel silly for complaining about JD Edwards laptops and the required Pointsec encryption we had to put on every laptop. I didn’t like it at the time, but it wasn’t a horrible intrusion and these days I think a necessary one.

    With all of the hype on identity theft, data privacy, and portable computers, I cannot believe that every company that allows any data to go outside of their physical office, whether on a backup tape or a laptop, doesn’t require encryption or some type of protection. At least do something to protect it. Most thefts are random and the thief probably wasn’t looking for credit card data, so a simple password might protect the data in most cases.

    If governments want to do something about identity theft and privacy, pass that law. Force everyone to encrypt their data and us in IT to come up with good ways to protect it. I didn’t like encrypting backups with Litespeed, the only choice a few years ago, but it was worth it to protect backup tapes. Now with more choices at very reasonable prices, and MSDE/SQL Express able to use them, every database that contains individual data, and especially financial data, should have its backups encrypted.

    Steve Jones