SQL Server 2005 Encryption Key Hierarchy

10/2/2010 7:11:36 PM
Before we talk about the types of encryption supported in SQL Server 2005, it is important to understand the function of the master keys and how these keys support your encryption implementation. As will be discussed throughout the remainder of this chapter, the relationships you choose to define between the service master key, the database master key, and the private keys of asymmetric key pairs and certificates will impact how you will implement your solution and the level of protection you are placing on the data.


This is a light discussion of the master keys in SQL Server 2005. SQL Server Books Online details master key backup and restore functionality, and Laurentiu Cristofor's blog ( is an excellent resource for the master keys.

Service Master Key

Each installation of SQL Server 2005 has a single service master key, which is created when you install SQL Server 2005. The service master key is used to protect linked server logins and credential secrets, and can be used to encrypt the database master key.

This symmetric key is stored in the master database and is protected by the Data Protection API (DPAPI) using the service account credentials and the machine credentials, as depicted in Figure 5-1. Certain common events may cause either the service account credentials or the machine credentials to become invalid. On a clustered installation, a failover will invalidate the machine credential protection. If the service account is changed, the service credential protection is invalidated. With two forms of protection, the chances that the service master key is invalidated due to an external event are greatly minimized. When the server is started, SQL Server will check both credentials, and if one of these is proven to be invalid during the startup, SQL Server will re-create the invalid key based on the other valid key.

The SQL Server 2005 master key hierarchy

If you choose to rely on the transparent key management in SQL Server 2005,  the service master key is the keystone of your encryption hierarchy. The database master key is also protected with a password, which significantly minimizes any chance that you will be unable to decrypt your data—unless the service master key is corrupt and you also forget your database master key password. The greatest risks of a corrupt or invalidated service master key are to your linked server logins and credential secrets. It is important that you have a backup of your service master key to ensure that you do not lose these entities that are protected using the service master key.

Database Master Key

Each user database can have only one database master key. A database master key is a symmetric key that may be used to protect the private keys of certificates and asymmetric key pairs. If your encryption implementation does not use certificates or asymmetric keys, you will not need a database master key, unless you have implemented Service Broker. If you choose to protect the private keys of certificates and asymmetric keys with a password, you may also forgo a database master key.

By default, the database master key is protected using the service master key and a password, as shown in Figure 5-1. You can choose to drop the encryption by the service master key, but the database master key must always have at least one password. If you have required password complexity on your server, SQL Server will require a strong database master key password.


When the database master key is protected by the service master key, SQL Server can automatically and transparently decrypt the database master key without requiring a password from a user or application. This default nature is sufficient if your primary concern is to protect your data in the event that the files are stolen, which is known as data at rest. If someone were to steal the database files and attach or restore them to a different instance, he or she would need to know the database master key password to reencrypt the database master key with the service master key on the new instance. When you create a strong password, it will be extremely difficult for these evildoers to guess your password and gain access to the data. A database master key protected by the service master key is easy to implement, as you do not need to explicitly open the database master key when you open dependent private keys. Users are only granted permissions on the asymmetric keys or certificates; you do not need to grant CONTROL permissions on the database to open the database master key.

While it is easier to manage your keys when the database master key is protected by the service master key, it does expose the sensitive data and encryption keys to anyone in the sysadmin fixed server role and db_owner database role. If your business requires a higher level of data protection, you will drop the service master key protection, which will require a password to open the database master key. This will create an environment to support protecting encrypted data from the sysadmin, but a db_owner continues to have control over the database master key and associated securables.  Execute the following statement in the user database to drop the service master key protection:


When the database master key is protected only by a password, you will explicitly open the database master key prior to using a certificate or asymmetric key that is dependent on the database master key. A user must have CONTROL permission on the database to open the key, which gives the user full rights on the database.


After a key is open, it is available for use within the session.  Your biggest hurdle in this implementation will be opening the key with an elevated user account. If your security requirements dictate that the application role be a low-privileged user, you may prefer to protect your asymmetric keys and symmetric keys with a password, as I will discuss in the sections "

If you are motivated to protect the sensitive data from individuals in the sysadmin fixed server role, dropping the service master key protection is only the first step. You will need to consider how to pass the passwords from the application to open the database master key without exposing the password to the sysadmin. A sysadmin may run Profiler on an instance, which can be an issue if you are managing all database code in stored procedures. Profiler will not display text from encryption functions (as in the OPEN MASTER KEY statement), but will return any input parameter passed to a stored procedure. A sysadmin may open the text of any stored procedure, exposing any passwords hard-coded in the procedure. To properly protect sensitive data from a sysadmin, you will manage key passwords in the application code or in a configuration file where the sysadmin does not have access and open the database master key directly in the application code.


Be aware that although you may be protecting your sensitive data from exposure within Profiler, machine administrators and network administrators may still have access to sensitive data and passwords using tools to access data stored in server memory or as it crosses the network.

The implementation of the database master key is directly dependent on the level of protection your business demands. If you are simply interested in protecting your data at rest, the default behavior with the database master key encrypted by the service master key is an easy implementation to provide the level of protection required. If you require a level of protection from a sysadmin, you can remove the service master key encryption and simply require a password to open the database master key. This implementation carries a number of issues, including elevated database permissions for an application user to open the database master key. As will be discussed in subsequent sections, alternative implementations will not require a database master key and will provide a more flexible implementation to secure your data from both the sysadmin and the db_owner, if required.

Most View
Hitachi Ultrastar A7K2000 1TB and Hitachi Deskstar 7K4000 4TB
Sharepoint 2010 : Metadata Architecture (part 1)
11 Essential Outdoor Apps (Part 2)
Lian Li PC-CK101P - The Best Motorized Train PC
Phanteks PH-TC90LS - A Dual-Tower Animal
ASP.NET 4 in VB 2010 : The Data Controls - Sorting and Paging the GridView
The drive toward DSLs : Running the Scheduling DSL
TuneAudio Marvel – Loudspeaker (Part 2)
Something You Should Know About Iphone 5 (Part 1)
Installing and Configuring SharePoint 2013 : Creating the Farm (part 7) - Configuring the App Management Service - Configuring the User Profile Service
Top 10
New Camera For You – Nikon 1 AW1
Phase One IQ250, Hasselblad H5D-50c - Medium-format Media Systems: Bigger Gets Better
Kaveri APU - AMD A10-7700K
Fujifilm X-M1 – Review April 2014
Fujifilm X-T1 : Good To Go
Dedicated Gaming Monitors BeNQ XL2720Z
Cooler Master HAF Stacker Case
NZXT Phantom 530 – Shiny Case
Hart Audio Evo1 Active Loudspeaker Review (Part 2)
Hart Audio Evo1 Active Loudspeaker Review (Part 1)