Implementing Data Encryption With Symmetric Keys in Microsoft SQL Server
Starting with the 2005 version, Microsoft SQL Server has included new encryption capabilities that all administrators, programmers and database analyst should be aware of. Since then SQL Server has been able to natively support both hashing and encryption. When planning your encryption or hashing solution, first you need to decide if you will be storing an encrypted version of the data or a hashed copy of the data. The difference is that encrypted data can be decrypted, while hashed data cannot be decrypted.
I recently had the opportunity to employ SQL Server 2005 data encryption. I was working on a project where we were moving locally hosted databases to a central government-run data center. When the database server was managed locally, it was subject to the data owner’s implementation of the applicable security guidelines. However when the databases were moved to servers located at the data center, they became subject to the information security rules of the data center manager, specifically Microsoft SQL Server 2005 Database Security Checklist (zipped PDF) under the Defense Information System Agency (DISA) Security Technical Implementation Guide (STIG). The move to the data center prompted a review to ensure that the databases conformed to the implementation approved for the new location. Specifically, we sought to bring the system into compliance with STIGs DG0067, DG0165 and DM6183 in the security checklist.
These databases supported a web application that allowed users to register for logins. The login information for these web-only users was stored in two tables within the database. The passwords with these logins were used to access the web application itself. Additionally these passwords were only needed for initial registration until the users’ accounts were mapped to public key infrastructure (PKI) identities. However, it was determined to err on the side of caution and implement encryption on these passwords.
There is an encryption hierarchy in SQL Server 2005. At the operating system level is the Windows Data Protection API, or DPAPI. It protects the server’s Service Master Key, which is the root key for each instance of SQL Server installed on the server. The Service Master Key in turn protects the Database Master Key, which is required in each database in which you will encrypt data. This key plays a role similar to that of the Service Master Key but for an individual database rather than for the entire SQL Server instance. The Database Master Key in turn protects the user-created keys such as certificates, symmetric keys, and asymmetric keys.
Certificates or passwords are used to protect asymmetric keys and symmetric keys. symmetric keys provide a higher level of encryption, than symmetric keys. The encrypted data in SQL Server 2005 must be decrypted before it is transmitted to the calling application. As a result, each level of encryption adds to the usage of resources by the server. Symmetric encryption uses one public key in order to decrypt data. Asymmetric encryption uses a private key to encrypt the data and a public key to call the decryption algorithm. This form of encryption can be very resource intensive. Microsoft recommends that to encrypt data, symmetric encryption be utilized with the keys protected by asymmetric encryption. The asymmetric keys would be protected by either a certificate, a password, or the Database Master Key.
If you are working within SQL Server 2008 or 2008 R2, the encryption hierarchy is almost exactly the same. The primary difference is the addition of the Extensible Key Module, which “stores symmetric and asymmetric keys outside of SQL Server”. SQL Server 2008 and 2008 R2 also employ an encryption methodology called Transparent Data Encryption (TDE). It must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM.
The data center’s security policy required that asymmetric keys should be protected by means of PKI certificates. This meant that every data call made by the web application would require three levels of decryption to access this data from the databases, so essentially it would require the usage of considerable server resources to protect this data. In order to comply with the encryption requirement while not placing an unnecessary burden on the system, it was decided to employ a symmetric key which would be secured by a database certificate created in SQL Server, which would be secured by the Database Master Key. Upon reviewing the data center’s security guidance with regard to symmetric keys, we decided this approach was compliant with the policy.
Once this decision was made, the implementation was straightforward:
First, we created a Database Master Key using a T-SQL statement like this:
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name =
N'##MS_DatabaseMasterKey##';
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'$EncryptionPassword12';
GO
Then we created a certificate which, by default, is based on the Database Master Key:
CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT =
'Password Fields';
GO
Next we created a symmetric key:
/*************** CREATE SYMMETRIC KEY *********************************/
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;
Now we could encrypt our data. We created a new VARBINARY column in the table and ran an update to populate it with the newly encrypted data:
/*************** ENCRYPT SENSITIVE DATA *********************************/
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
Update tblData
Set encyptField =
EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), OldField);
Close SYMMETRIC KEY PasswordFieldSymmetricKey -- Always be sure to close the key when done
Then we changed all the stored procedures accessing this data by adding the following logic:
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
SELECT f1, f2, f3, CONVERT(nvarchar, DecryptByKey(encryptField))
AS OldField FROM tblData
CLOSE SYMMETRIC KEY PasswordFieldSymmetricKey
In conclusion, it was determined that using a symmetric key protected by a self-signed SQL Server certificate which itself is protected by the Database Master Key would provide encryption protection while not consuming the server resources that an asymmetric key would have required. Thus we were able to simply encrypt our data to comply with the applicable STIGs while minimizing the server resources utilized to access the data.
This post was written by:
Edward
Senior Engineer
For more information on this post, the technologies discussed here, or Zekiah’s database integration and information security services, please e-mail us at contact@zekiah.com