Problem
I have a Database Master Key created in the database. Can I start encrypting data in my database now? The answer is “not yet”. A Database Master Key per se’ cannot encrypt data. You either need another key or a certificate. In this second tip of a four-part series we’ll cover all things Certificates.
NOTE – For the first tip in these series, please reference – Managing SQL Server Master Keys for Encryption.
Solution
What Can Certificates Be Used For?
Certificates, which are second in line in the encryption hierarchy, can be used to create symmetric keys for data encryption or to encrypt the data directly. Information on certificates currently in the database can be retrieved through the sys.certificates DMV:
How Can I Create a Certificate?
There are a number of ways to create certificates. SQL Server 2005 shipped with the ability to create self- signed certificates, so if you’re in a hurry to get things up and running then create one using the following syntax:
USE MSSQLTIPS CREATE CERTIFICATE certMSSQLTIPS WITH SUBJECT='MSSQLTips Certificate', EXPIRY_DATE = '08/12/2012'
If an expiration date is not specified in the creation statement then a default expiration date of one year from the creation date will be applied. Also, the certificate created by the statement above will be encrypted using the Database Master Key. If you would prefer that a password be used instead of the Database Master Key then the statement ENCRYPTION BY PASSWORD='<Password>’ can be used. You can see that the certificate is encrypted using the password in the sys.certificates view:
Another method of creating a certificate is by using .cer and .pvk files issued from a Certificate Authority. Since most, if not all, Active Directory domains have a Certificate Authority in the infrastructure, you can request a certificate by using an Advanced Request. Certificates can also be created by using signed executable files and dll’s.
Can I Backup The Certificates?
Certificates can be backed up the same way as keys can be backed up. Remember that certificates and keys should always be backed up to secure media that can be stored offsite in case of disaster. The syntax for backing up certificates is:
USE MSSQLTIPS BACKUP CERTIFICATE certMSSQLTIPS TO FILE = '<FileLocation>', ENCRYPTION BY PASSWORD='<Password>'
How Do I Restore a Certificate From a Backup?
A certificate can be restored from a previous backup, but there is no “restore certificate” command. You must use the CREATE CERTIFICATE command and specify the file location and password used during the backup process.
What Permissions Does A User Need To Use The Certificate?
In order to use a certificate for decryption a user must have VIEW DEFINITION and CONTROL permissions on the certificate. These permissions can be assigned either individually or through Database Roles. To grant permissions to users and groups, use the following syntax:
USE MSSQLTIPS GRANT VIEW DEFINITION ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role> GRANT CONTROL ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role>
This syntax differs slightly from traditional GRANT statements in that the scope qualifier (::) must be used.
Next Steps
- Read more information on:
