SQL Server Encryption Certificates Overview


By:   |   Updated: 2007-08-29   |   Comments (3)   |   Related: More > Encryption


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:

select from sys certifcates

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:

select from sys certifcates

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


Last Updated: 2007-08-29


get scripts

next tip button



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips





Comments For This Article




Thursday, October 18, 2012 - 1:42:12 PM - Jim Back To Top (19984)

Can you tell me the pros/cons between encrypting data with a certificate and encrypting data with a symmetric key?


Monday, September 10, 2012 - 12:17:44 PM - Rob Back To Top (19453)

For those of you using SQL Server 2012, the "Can I Backup The Certificates?" section is different.

 

See here: http://msdn.microsoft.com/en-us/library/ms178578.aspx

 

The code for 2012 should be:

 

BACKUP CERTIFICATE sales05 TO FILE = 'c:\storedcerts\sales05cert'
    WITH PRIVATE KEY ( FILE = 'c:\storedkeys\sales05key' , 
    ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' );
GO

 


Monday, August 20, 2012 - 3:15:28 AM - Ruchir Back To Top (19118)

Thanks - very helpful!



download





Recommended Reading

SQL Server Column Level Encryption Example using Symmetric Keys

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Storing passwords in a secure way in a SQL Server database

Where Does SQL Server Store Its Certificates

SQL Server Database Encryption for GDPR Compliance with DbDefence








get free sql tips
agree to terms


Learn more about SQL Server tools