Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server 2005 Encryption Certificates Overview

MSSQLTips author Tim Cullen By:   |   Read Comments (3)   |   Related Tips: 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 2005 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. Click here for additional information on requesting certificates from a Certificate Authority. 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 Update: 8/29/2007


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, October 18, 2012 - 1:42:12 PM - Jim Read The Tip

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 Read The Tip

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 Read The Tip

Thanks - very helpful!




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.