Voice of the DBA

Tag: security

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

  • Pride and Security

    Do you love your job? I do.

    The weakest link in most security schemes is the human. We know that there are regular breaches of trust by employees, mistakes made (fat fingers, misconfigurations and more), and supposed favors done by someone with trusted access that send data to criminals. Social engineering, in a variety of forms, preys on the trusting nature of most people to gain unauthorized access, and unfortunately, it often works.

    Part of social engineering is the inherent trust for others that most of us have. Part of it is the desire that most people have to help others. However I think a part of it is also the attitude that many workers have when they aren’t treated well. When employees don’t feel they are a part of the company family and just work for a paycheck, they are less vigilant or caring about safeguarding the digital assets, and sometimes physical assets, of the company.

    If you had more pride in your employer, wouldn’t you be a little more careful in caring for the company and its assets? I think most people would. I don’t have any data on this, but I bet that the companies where people take pride in their work are more secure. Employees probably know more about each other, and problem recognize a larger percentage of the company. Workers will be a little more observant and protective if they feel that the company is “theirs.”

    One of the best things management can do to raise the level of security at the company, and build a better organization, is to ensure they are creating an environment that people enjoy and take pride in. That comes from showing respect, consideration, and fair treatment of all employees. It’s not even that hard to do, just be a decent human that does what’s best for everyone in the company, not just for the CEO.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Be careful with your smartphone

    I love my iOS device and find it very handy.

    More and more tech professionals I know are carrying smartphones. There seems to be a split among technical people between iOS, Android, or WP7 platforms, but it seems that all the mobile OSes are extremely handy for system administrators. I know that I appreciate the ability to connect to email and other people when I’m away from my desk. If I were a production DBA, the ability to potentially fix something remotely, without the need to go to the office or back home, would be extremely valuable.

    However, carrying around a smartphone isn’t without a risk. The features and capabilities of the various mobile operating systems are a double edged sword. The power of the smartphone can easily be used for attacks against your network. This piece talks about hackers targeting the mobile platforms as another attack vector. That’s scary, especially as most of these mobile OSes were not designed to be secure.

    One of the very attractive parts of the Android platform is it’s openness. Anyone can build an app and sell it in the Android Marketplace. However that openness also means that the marketplace is a target for malware. While you might not like the Apple review process, it does offer some security. I’m not sure how the Windows Phone 7 platform is affected, but I would hope there is some security review. I also hope third party stores, like the Amazon Android store, will do some kind of security review to prevent malware.

    Ultimately if you are a privileged user on a corporate network, and you connect from your smartphone, you need to be extra careful. Set a password on your phone, don’t save passwords to trusted systems in your mobile browser, and be especially careful about scanning your system regularly.

    Steve Jones

    The Voice of the DBA Podcasts