Speak to IT security experts and ask questions about what they consider to be one of the most difficult challenges they face and coming quite close to the top of the list, along with user education, will probably come encryption key management.

Not normally associated with the role of the DBA, encryption keys enable us to secure databases but still provide managed access to approved users.

Securing SQL Server Data
Of course it is silly sending a person data from a SQL Server database that is encrypted without giving them the means to decrypt the data and view it in plain text. This decryption process is facilitated by the use of cryptographic keys. In fact there are two types of keys - symmetric and asymmetric. With symmetric keys the same key is used to both encrypt and decrypt the data. In an asymmetric model different keys are used to encrypt and decrypt the data.

Why bother with two separate keys and the asymmetric model? Well, keys are at the heart of the encryption algorithm. If a key used to encrypt data is the same as the key to unencrypt data then anyone with access to the key can unlock my cipher text. In the asymmetric model I issue a public key that enables a user to encrypt the SQL Server data but only I have the private key to decrypt the data. That way I can receive secured data in the knowledge that only I can see the plain text data.

SQL Server 2005 supports three types of data encryption.

• Symmetric key encryption. As we have seen this is a risky encryption method to support as the key used to encrypt and decrypt the data is the same. It can have a use if you are using encryption to secure data wholly inside the SQL Server, and the use of AES or Triple DES algorithms is recommended.
• Asymmetric encryption. SQL Server uses the RSA algorithm and supports 512-bit, 1,024-bit and 2,048-bit keys
• Certificates, which is similar in approach to asymmetric key encryption. SQL Server uses the IETF (Internet Engineering Taskforce) X.509v3 specification along with RSA for data encryption.

As keys are so crucial to SQL Server encryption, their careful management is vital.

Key Management
Imagine I have encrypted some SQL Server data using my private key. I then decide to leave the company and go and work elsewhere, taking my private key knowledge (i.e. my password) with me. The data I leave behind is all in cipher text and is now lost to my former colleagues. Imagine another scenario where the DBA has encrypted a SQL Server table and stored the data on a backup tape. A few years later there is a requirement for discovery, due to a legal action, and the data needs to be recovered. If the private key has been lost then the data can not be accessed, which will cause a storm of legal issues.

Even the basic issuing of keys is fraught with difficulty. Many years ago vetted couriers were dispatched by airplane, with a briefcase secured to their wrist containing the month's keys for that remote office. Now key distribution can be facilitated automatically but, even so, it is very easy to make mistakes and get into a horrible confusion.

SQL Server 2005 Key Management
We have already discussed some of the difficulties with key management and the knots that you can end up being tied in if your key management should go wrong. The good news is that SQL Server does provide some basic key management tools, reducing the need for other key management products in the more simple deployments. In more complex deployments, SQL Server can be a good citizen of third party key management products.

Keeping private keys secure is vital to the integrity of a secure SQL Server. Many wonderfully complex encryption algorithms have been rendered useless as the associated keys have been leaked. SQL Server 2005 uses an encryption hierarchy to protect its keys.

The first security layer is the Windows Data Protection API, referred to as DPAPI. This enables keys to be secured discretely in Windows and provides support for the Crypto API. It was introduced with Windows 2000 and protects the SQL Server 2005 service master key which is the root key for each instance of SQL Server installed on a specific computer. If this root key is compromised then all other keys on that computer will be vulnerable. The service master key needs little maintenance by the DBA but should be backed up and stored securely away from the SQL Server it applies to. The service master key is a bit like energy - it can't be created or destroyed. There is only ever one per instance of SQL Server. The key is secured using the credentials of the logged in user, and is managed under the same account as the SQL Server service. Therefore, anyone that has access to that service account will have access to the service master key so be careful in allocating accounts and users.

Next in the hierarchy is the database master key. This is a database-specific version of the service master key and, as such, secures all keys in a specific database and protects all the user keys, symmetric /asymmetric keys and certificates. If this is compromised then all other keys in the same database will be vulnerable.

The built in keys (service master key and database master key) are not generally used directly for encryption but will be used internally by SQL Server as part of the internal key management infrastructure.

The scale of a database key hierarchy can be as straightforward or complex as you like, but bear in mind the more complex a key hierarchy the more that can go wrong, as well as possible performance implications if a large key hierarchy needs to be traversed on a regular basis.

User keys in SQL Server comprise certificates, asymmetric keys, symmetric keys and pass phrases. These are the keys that the DBA will use to protect database data.

A certificate is a digitally signed object that keeps the public key associated with the owner of the private key. SQL Server can create certificates for use within the database server but certificates for use outside the server need to be obtained from one of the trusted third-party certificate issuers. Certificates can be created and managed using T-SQL in much the same way that you create and manage other database objects.

Asymmetric keys are created and managed much the same way as certificates and are useful if you don't want the full overhead of certificate management but still want to issue public keys.

Symmetric keys are useful when performance may be an issue, as they require less processor cycles to implement and manage - asymmetric key management algorithms can take up significant server resources. The significant downside of symmetric keys is the fact that the keys need to be shared, and shared secrets are difficult to protect - as many people know!. That said, symmetric key encryption does have a part to play when securing data inside a SQL Server, as the key never actually leaves the server.

Pass phrase keys are useful if you are happy to look after a suitable pass phrase yourself, outside the remit of SQL Server. They are implemented using the T-SQL functions EncryptByPassPhrase and DecryptByPassPhrase.

Encryption and key management in SQL Server 2005 need not be too difficult as long as you take a sensible and measured approach. Not all solutions will need every last bit of data encrypted and if you do need to implement encryption think through the possible performance implications. Like everything in the world of databases, there is always a compromise, but for many security must, quite rightly, be a number one objective.