Chapter 3
Key Management
Abstract
This chapter is all about encryption keys in the SQL Server database and how to setup and secure those keys.
Keywords
Enterprise Key Management
Encryption Keys
Key Management
Service Master Key
Database Master Key
Information in this chapter
• Service master key
• Database master key
• Encryption password management
• Enterprise key management
• High availability and disaster recovery for key management
This chapter is all about encryption keys in the SQL Server database and how to setup and secure those keys.
Service Master Key
Encryption keys within Microsoft SQL Server are configured in a hierarchy structure so that keys at the top of the hierarchy can open keys lower in the hierarchy as shown in Figure 3.1. At the top of the hierarchy is the Service Master Key, which is protected by the Windows Database Protection API (DPAPI).
Figure 3.1 Encryption hierarchy within SQL Server.
In Figure 3.1 we can see all the different objects that can be used within the data encryption process including the Service Master Key, the Database Master Key, Certificates, Asymmetric Keys, Symmetric Keys, and Passwords. At first glance this graphic can be a little bit confusing; however, if you start reading the graphic from the bottom up it can make a little more sense. If we start from the bottom with the user data that we have encrypted we can see all the various ways that we can secure that data. We can secure that data either using a Symmetric Key or a password.
The Symmetric Key that we use to secure the encrypted data can be secured via a certificate, an Asymmetric Key, an Enterprise Key Manager, Another Symmetric Key, or a password. At this point things start to get a little more complex. If we store the Symmetric Key using another Symmetric Key that Symmetric Key can be secured any of the ways that any other Symmetric Key can be secured (which isn’t shown all that well in the graphic to be honest, but putting nesting relationships in a printed graphic is pretty hard). If the Symmetric Key is secured by an Asymmetric Key that Asymmetric Key can be secured via the Database Master Key, a Password, or an EKM. If the Symmetric Key is secured via a certificate that certificate can be secured via a Password or the Database Master Key. The Database Master Key can be secured via a Password or the Service Master Key and the Service Master Key can be secured via DPAPI.
The DPAPI is an operating system component that is used to encrypt and decrypt the Service Master Key. The Service Master Key is created automatically by the SQL Server instance at the time of installation. The Service Master Key can be regenerated if needed; however, this should be a rare event. Events which might require that the Service Master Key be regenerated would include if the Service Master Key was compromised, if the account which runs the SQL Server service is changed and the new account cannot read the old Service Master Key for some reason.
In some cases the SQL Server service can’t regenerate the Service Master Key without losing access to data which has been encrypted. In this case there is a FORCE keyword which can be included in the ALTER SERVER MASTER KEY command which will force the SQL Server service to reset the Service Master Key even if it means that data can no longer be decrypted using the Service Master Key. If using the FORCE keyword be very careful as this may prevent access to some data forever as you can no longer decrypt the data using the Service Master Key. If you still have access to the password which secured the Database Master Key then the data is still decryptable. You would need to open the Database Master Key using the password then rebuild the Database Master Key using the new Service Master Key (this is discussed later in this chapter).
The Service Master Key is used to encrypt all other layers within the data encryption process, even if passwords are used as well. This Service Master Key is used so that the SQL Server service itself can open these keys when the SQL Server starts. As the SQL Server doesn’t have access to the passwords that can also be used to encrypt these values, without the Service Master Key no encrypted data would be accessible until an administrator logged onto the server after each reboot in order to unlock the system.
The Service Master Key should be backed up using the BACKUP SERVER MASTER KEY statement each time the Service Master Key is changed. This should be done to ensure that if the Service Master Key is lost, due to a reinstallation of Windows and/or the SQL Server Service that the data which is encrypted within the database can be restored. The backup of the Service Master Key is encrypted by a password. This password should be a strong password with at least 16 characters in it to reduce the risk of anyone gaining access to the Server Master Key. The backup of the Service Master Key should be stored offsite in a secure location, when at all possible in a different physical location than the database backups as the Service Master Key gives an attacker access to all the encrypted data on the system.
Database Master Key
The Database Master Key is used to encrypt all the encryptable objects within the database that contains the Database Master Key such as Symmetric Keys, Asymmetric Keys, Certificates, etc. Each database which will contain encrypted data within it will have to have a Database Master Key created within the database before the objects needed to secure the data are created. The Database Master Key is created within the database by using the CREATE MASTER KEY command. The Database Master Key is secured by both the password, which is specified when running the CREATE MASTER KEY command, but also by the Service Master Key so that either the Service Master Key or the password can be used to open the Database Master Key.
In ultra-secure environments you can configure the Database Master Key to not be encrypted by the Service Master Key. Doing so will require that a Database Administrator log onto the SQL Server instance every time the database is closed and opened (reboots, if you have auto-close enabled, etc.) and use the OPEN MASTER KEY command to open the master key and allow other services to use the Database Master Key.
You can change the Database Master Key by using the ALTER MASTER KEY statement and using the REGENERATE WITH ENCRYPTION BY PASSWORD phrase as shown in Example 3.1.
Encryption Password Management
The most important thing when encrypting data within a Microsoft SQL Server database is to properly manage the passwords, certificates, and any other identifier that is used to secure the data. Without these securables for the encrypted data, the data is lost and the application is useless.
This presents a massive amount of risk, but this is a risk that we can manage by properly securing the passwords and the backups for these keys. The most important step is to backup these keys when they are changed. The backups of these keys should then be stored offsite. In a perfect world the keys will be stored in a different physical location than the backups of the encryption keys so that if the backups are compromised from the offsite backup location only the backups are compromised and not the encryption keys.
Copies of the passwords for the various keys must be kept onsite as well as offsite. The passwords need to be stored in a secure location which is easy to access when needed, but not too easy to access. A perfect location for these passwords, is not to have them stored on a server in the data center or server room. Instead print them on paper, or put them in a text file and burn them to two different CDs or DVDs. The paper or CD/DVD should then be placed into an envelope (a padded envelope if using disks). The envelope should be sealed, the seal signed, and a piece of shipping tape placed over the signature. The envelope should then be secured in an executive’s desk, or better yet a safe in someone’s office (the Human Resources people usually have a safe in someone’s office).
Enterprise Key Management
Enterprise Key Management (EKM) involves using a third-party platform to secure the database encryption keys. These keys are created and stored within the third-party platform and given to the SQL Server automatically when the SQL Server needs the key. There are a variety of Enterprise Key Management systems available on the market. Some are easy to setup, others are hard to setup. Some are physical appliances within the data center, others are software packages that can run within a VM either in your data center or in the Cloud.
One of these platforms that is available for all situations is the Alliance Key Manager from Townsend Security. This Enterprise Key Manager comes as a physical appliance that you can deploy as a single unit or as a highly available solution. It is also available as a virtual appliance which you can deploy to either Microsoft’s Hyper-V or VMware’s vSphere hypervisor platform. The virtual appliance is also easy to deploy to either Amazon’s AWS cloud or to Microsoft’s Azure cloud.
Enterprise Key Management systems give a more fine-grained control over the key creation process. This becomes very important in a lot of compliance situations. Enterprise Key Management solutions are built around the concept of dual control where one team setsup the key management system, while another uses the key management system to get a key and use that key. The team which manages the key management system that needs no access to the line of business system (Microsoft SQL Server in our case) and the database administrator needs no access to the key management system. This separation allows the data to stay secure because the team with the key can’t access the data, and the team with access to the data can’t access the keys. This also follows the concept of separation of duties. The dual control requirements also make it so that both the team that manage the keys and the team that uses the keys needs to be involved in order to create or change keys preventing any one person from being able to access data or prevent access to data by changing the keys.
Getting a Microsoft SQL Server instance setup to work with an Enterprise Key Management system is actually a pretty straightforward process. Before working with an Enterprise Key Management system ensure that you are using a version and edition that supports Enterprise Key Management. Enterprise Key Management support was added to the Microsoft SQL Server product starting in SQL Server 2008 and in SQL Server 2008 through SQL Server 2014 has only been available in the Enterprise Edition of Microsoft SQL Server.
Before you can start within SQL Server the security team who managed the Enterprise Key Management needs to create the key, which will be used within SQL Server. How this is done will vary depending on your Enterprise Key Manager. Within the Townsend product you connect to the Enterprise Key Manager and they have a branch within their menu specifically for Microsoft SQL Server as shown in Figure 3.2.
Figure 3.2 Command Entry Menu for Alliance Key Manager.
After selecting the “Create EKM Key” option from the menu shown on the left of Figure 3.2 the security administrator needs to give the key a name. This key should be unique within the Enterprise Key Management environment. After naming the key the length of the key is then selected. Longer keys are more secure but require more CPU power to encrypt and decrypt the data while shorter keys require less CPU power but are less secure. Whenever possible use a longer key for better data protection. After the fields are filled out the administrator who manages our Enterprise Key Management server clicks submit to create the key. The key then needs to be activated by using the “Enable Key for EKM” option from the menu on the left shown in Figure 3.2. The key name is then given to the database administrator so that the database administrator can tell the SQL Server where the key is and how to access it.
Before the encryption key can be setup within the Microsoft SQL Server instance, the instance needs to be configured to allow for third-party encryption keys. This is done using the sp_configure stored procedure and turning on the “EKM provider enabled” setting as shown in Example 3.7.
High Availability and Disaster Recovery for Key Management
Our important applications have high availability and disaster recovery requirements. This means that our Enterprise Key Management solutions need to have the same high availability and disaster recovery requirements. Without having the Key Management Solution in place after a high availability or disaster recovery event there is no point in having the application online as without the key the application won’t be available.
When using keys within SQL Server they will be failed over with the SQL Server instance. When using a third-party Enterprise Key Manager one that supports high availability within the data center as well as replication out to a unit or virtual machine at a second data center is a must as without the keys the application will be down even though the SQL Server service is up and running.
Conclusions
Key Management, and especially Enterprise Key Management, has typically been considered to be a complex solution to a complex problem. It doesn’t have to be. Key management can be a straightforward process, provided that it’s been done in a way that makes sense, is documented and the keys are backed up and securely stored in at least two sites both of which are properly secured.
Some Enterprise Key Management systems are more complex to setup than others, but much of this complexity comes around the job that they perform. Properly securing these systems is a complex problem, but like most complex problems when properly laid out and documented they don’t have to be big complex and scary solutions.