Encrypting Your Valuable Data With SQL Server 2005: Part 1

Security has become extremely important in today's fiercely completive business environment. Industry standards require you to implement firm techniques to secure your data. In SQL Server 2005, security has improved for authentication, authorization, and encryption. Encryption is so much improved that it is almost a new feature in SQL Server 2005.

Encryption is the last barrier against a hacker. Technically, authentication and authorization methods should be strong enough to stop hackers before they can view data. But if they do get through, the last trump card for system designers is data encryption. You will not encrypt you all of your data, only very important data like passwords, credit card number, etc.

In the days of SQL Server 2000, you had two options: find third party tools to handle data encryption, if available, or use the built-in encryption, which was very limited.

There were two stored procedures in SQL Server 2000 that supported data encryption. They are undocumented and many gurus advocate not using undocumented stored procedures. Those stored procedures provided only hash encryption. This means that after encryption, you would not be able to return its original value. This might be good enough for passwords and the like, but for credit card and Social Security numbers, you had a problem.

In SQL Server 2005, almost no options to perform data encryption operation are available with the object explorer of the SQL Server Management Studio. So you have to write Transact-SQL scripts for these features.

As this is a completely new area for SQL Server 2005, there are many things that we can discuss. Rather than loading all the functionalities into a single article, I have written two. This first article will provide an overview of data encryption, service master keys, and database master key management. The next article will discuss the implementation of encryption with certificates, symmetric keys, and asymmetric keys.

Encryption Hierarchy

SQL Server 2005 uses hierarchical database encryption.

The following image shows that there are three ways to encrypt your data. They are Certificates, Symmetric Keys, and Asymmetric Keys. (I will this describe the pros and cons of those methods in the next article.)

Source: Books Online, SQL Server 2005

All the above methods take place at the database level and are created by using a Database Master Key (DMK). A Service Master Key (SMK) is generated at the server level. The Windows Data Protection API (DPAPI) automatically encrypts and secures SMKs by using the credentials of the SQL Server service account.

Service Master Key (SMK)

As you can see from the above image, the SMK is the root of all the encryptions. Because of that, they say it is the mother of all SQL Server encryption keys. The SMK is automatically generated when an SQL Server 2005 instance is installed. SMK is a symmetric key. A Symmetric key is used for both encryption and decryption. You can regenerate the SMK, but as it is the root of the SQL Server 2005 encryption hierarchy, it involves decrypting and re-encrypting the complete hierarchy. As you can imagine it is a resource intensive operation and should be scheduled at off-peak load times. Due to the importance of the SMK, the best practice is to back up and store a copy in a secure, off-site location. This is the first action you should perform after installing an instance of SQL Server 2005.

BACKUP SERVICE MASTER KEY TO FILE = 'c:\keys\service_master_key'
ENCRYPTION BY PASSWORD = '$QL-$erVer-PerF0RMA&CE.C0m';

The above Transact-SQL code will create a service_master_key file in C:\keys folder. Once it is created, you cannot overwrite it by running the same query. However, you can create another backup by providing a different file name.

The next important functionality is restoring the SMK.

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\keys\service_master_key'
DECRYPTION BY PASSWORD = '$QL-$erVer-PerF0RMA&CE.C0m' FORCE

You need to provide a valid password; otherwise, decryption of the SMK will fail. You need to store the password in a secure place alongside the SMK backup. As I said before, restoring will re-generate all the encrypted keys and encrypted values. However, if you try to restore the same SMK that is currently available, SQL Server is smart enough to understand it and does not perform unnecessary decrypting and re-encrypting. Instead, it will give you a message telling you that the old and new master keys are identical and that no data re-encryption is required.

The FORCE parameter will ignore the errors when it tries to decrypt current values. But this introduces the risk of data loss.

There are no CREATE and DROP statements linked with the SMK as it is built automatically with the installation of SQL Server. You need CONTROL SERVER permission on the server to backup and restore the SMK. CONTROL SERVER permission grants the equivalent of system administrator privileges.

By executing the following code the in master database, you can view information such as creation date, modified date, and algorithm for the SMK.

SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##'

Database Master Key (DMK)

The Database Master Key is the next level in the SQL Server 2005 encryption hierarchy. Unlike the SMK, the DMK will not automatically be created when the database is created. Where a database server has only one SMK, it can have many DMKs — one for each database, if created.

You need CONTROL permission on the database to perform operations with the DMK except for CLOSE DMK. CONTROL permission confers owner-like permissions that effectively grant all defined permissions to the object and all objects in its scope, including the ability to grant other grantees permissions.

The DMK is a symmetric key used to protect the private keys of certificates and asymmetric keys (which we will discuss in detail in the next article) that are present in the database. When the DMK is created, it is encrypted using the Triple DES algorithm with a password that was supplied while creating it.

USE EncryptionDB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'encrypt10N4DMK';
GO

You cannot create the DMK again for the same database without dropping the existing one in both the database and the master database. To drop the DMK:

USE EncryptionDB;
DROP MASTER KEY;
GO

Just after the creation of the DMK, make sure that you back it up and store it in an off-site location like the SMK.

USE EncryptionDB;
BACKUP MASTER KEY TO FILE = 'c:\keys\DMK EncryptionDB'
ENCRYPTION BY PASSWORD = 'DMKPa$$w0rD';

After backing up the DMK, the next obvious operation should be restoring it. However, this is bit different from the SMK. For the DMK, you have to provide two passwords.

USE EncryptionDB;
RESTORE MASTER KEY
FROM FILE = 'c:\keys\DMKEncryptionDB'
DECRYPTION BY PASSWORD = 'DMKPa$$w0rD'
ENCRYPTION BY PASSWORD = 'ENC=DMKPa$$w0rD' FORCE;
GO

DECRYPTION BY PASSWORD is the password that you used to encrypt the DMK and ENCRYPTION BY PASSWORD specifies the password that is used to encrypt the DMK after it has been loaded into the database.

The FORCE parameter acts the same as with the SMK. Like the SMK, restoring the DMK will re-generate all the certificates and keys that were encrypted by the DMK. As this is a resource-intensive operation, you should schedule it accordingly.

If there is no master key in the current database, RESTORE MASTER KEY creates a new DMK. You also have the option of altering the DMK.

USE EncryptionDB;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'encrypt10N4DMK';
GO

Rather than changing the password, you can add another password so you can use both passwords to open the DMK. However, you cannot use the same password twice.

USE EncryptionDB;
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'S2encrypt10N4DMK'
GO

The new DMK will not be automatically encrypted with the SMK. But you can do this.

USE EncryptionDB;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

If the DMK was encrypted with the SMK, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.

Similarly, you can DROP this by using DROP ENCRYPTION BY SERVICE MASTER KEY.

Mind you, this is a resource intensive operation.

By executing the following code in the database, you can view information such as creation date and modified date for the DMK.

SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'

Summary

Encryption, the last wall that hackers have to penetrate, has significant features in SQL Server 2005. This article has introduced Service Master Key and Database Master Key operations. The next article will describe implementation. All the encryption methods are based on the Service Master Key and the Database Master Key.