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:keysservice_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:keysservice_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##’