Recently I had to show that data can be encrypted in SQL Server 2008 database. The purpose was to not store the raw value in a table where anyone with access could see the sensitive data. The original process truncated the destination table and then populated the destination table with data pulled from the source table. Seeing that as an issue, I made a change to the process in addition to adding encryption.
The main change to the process involved a few things. First, I added a staging table to the process. This table is where the data would initially get populated before going to the destination table. Second, I changed the datatype in the destination table in order to store the new encrypted value. Third, I added a new column to the staging table to store the encrypted value. Then, I added a step to update all the columns from the staging table into the destination table with the changes being that the raw value column was replaced with the encrypted value. Lastly, after a successful copy, I truncated the staging table.
Microsoft has some examples on their website here (until the link dies). For my implementation, I used symmetric encryption that included an authenticator. I’ll quickly go over the involved with one way of getting this encryption method working:
- Create a master key if the database does not already have one.
- Create a certificate (or load one into the database if you already have one).
- Create a symmetric key.
- Open symmetric key.
- Encrypt data.
- Close symmetric key.
The purpose of the master key is to protect the certificates’ private keys and asymmetric keys in a particular database. The command to create the master key is:
USE DATABASE; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'put.real.password.here'; GO
Of course, you would replace database with the database name that has the table with data you want to encrypt. Replace the put.real.password.here with a real password. The stronger the password the better since this password will be used to encrypt the master key. With the master key created, we can use this key to encrypt the certificate we will create in the next step.
There are multiple ways to create and/or use a certificate. In this example, I’ll show how to create a simple certificate that is encrypted by the master key:
CREATE CERTIFICATE certificate_name WITH SUBJECT = 'certificate_subject_name'; GO
Again, the certificate_name above would be replaced by the real certificate name. The ‘certificate_subject_name’ should be replaced with something helpful to know why or how the certificate is going to be used. Refer to Books Online for the argument options for this command. In this case, the purpose of this certificate is to encrypt the symmetric key in the next step.
Now we create the symmetric key used to encrypt the actual data. A symmetric key has to be encrypted by one or more of either a certificate, password, symmetric key, asymmetric key, or provider. Again to keep things simple, I’ll show one way to create a symmetric key:
CREATE SYMMETRIC KEY key_name WITH ALGORITHM = REAL.algorithm ENCRYPTION BY CERTIFICATE certificate_name; GO
In the example above, replace key_name with the name of the key. Replace real.algorithm with one of the acceptable algorithm types, like AES_256; refer to Books Online for the full list of the acceptable algorithm types. Lastly, replace certificate_name with the name you gave the certificate in the last step. With this key in place, you will not be ready to encrypt some data.
Up until this point, getting ready for encryption would follow the same steps. To actually encrypt the data is where this encryption version is different from the standard version. Using an authenticator is optional. Encrypting the data with an authenticator adds another layer of security on the encrypted data. Using this method, data decryption is only successful when the authenticator and the key are correct.
Before we can encrypt a column, we need to open the key we created in the last step. Here is one way to open the key following the work shown earlier:
OPEN SYMMETRIC KEY key_name DECRYPTION BY CERTIFICATE certificate_name; GO
In the example above, replace key_name with the name used to create the symmetric key in the last step. Replace certificate_name with name of the certificate created two steps ago. With the key open, we can now encrypt data.
I will show how to use an authenticator to encrypt the data. This assumes your table has a column to store the encrypted value:
UPDATE DATABASE.schema.tablename SET encrypted_column = EncryptByKey(Key_GUID('symmetric_key') , column_to_encrypt, 1, HashBytes('SHA1', CONVERT( varbinary , authenticator_column))); GO
In the example above, replace database.schema.tablename with the actual database name for your database, schema name for the table, and the table name that has the encrypted column. I personally use database.schema.tablename to ensure I’m in the correct database, using the proper schema, and have the table that I want to modify. Replace encrypted_column with the name of the column that will store the encrypted data. Ensure this column has the varbinary datatype since the EncryptByKey function returns the varbinary datatype. Replace symmetric_key with the name of the key created two steps ago. Replace column_to_encrypt with the column name that has the data you want encrypted. Replace authenticator_column with the name of a column that has a unique value for each row. An example would be if the database table uses an incrementing integer that identifies the row as unique. Once this command is executed, the column will have encrypted the encrypted version of the original data.
Once the command above is complete, be sure to close the symmetric key; leaving it open is a security risk since it is the key to encrypted data. Here is the way to close a single symmetric key:
CLOSE SYMMETRIC KEY key_name; GO
Again, we would replace key_name with the name of the key created three steps ago. At this point, the table would have a column with the raw data and another column with the encrypted data. Here are all the commands used so far put together on one code batch:
USE DATABASE; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'put.real.password.here'; GO CREATE CERTIFICATE certificate_name WITH SUBJECT = 'certificate_subject_name'; GO CREATE SYMMETRIC KEY key_name WITH ALGORITHM = REAL.algorithm ENCRYPTION BY CERTIFICATE certificate_name; GO OPEN SYMMETRIC KEY key_name DECRYPTION BY CERTIFICATE certificate_name; GO UPDATE DATABASE.schema.tablename SET encrypted_column = EncryptByKey(Key_GUID('symmetric_key') , column_to_encrypt, 1, HashBytes('SHA1', CONVERT( varbinary , authenticator_column))); GO CLOSE SYMMETRIC KEY key_name; GO
From here, it would be up to you to decide what to do with the encrypted data. If we follow what I did, this is the point where I copied all columns except for the raw data that I encrypted into a destination table. Since all of the encryption objects are at the database level, they will work on all tables. So if the data is encrypted in one table and the encrypted data is subsequently copied to another table, the objects used to encrypt the data are used to decrypt the data.
In a future post, I’ll show one method to display the decrypted data.