Encrypt SQL 2005 database with Certificates
Thursday, February 19th, 2009 | Author:

Using Certificates to Encrypt Data

Certificates are parallel with asymmetric keys in the SQL Server 2005 encryption hierarchy. A certificate is simply a method of using asymmetric encryption. Certificates bind public keys to individuals who hold the associated private key. Certificates use the same RSA algorithm as asymmetric keys; therefore, they are resource-intensive and their use is normally restricted to encrypting other keys. SQL Server contains an integrated certificate authority, which it uses to issue its own selfsigned, and industry standard X.509 certificates. Alternatively, you can import certificates from an external certificate authority.The use of external certificates allows you to use a wider range of key lengths, which can provide enhanced security. Certificates are the most secure way in which to encrypt data natively within SQL Server 2005.You can use the CREATE CERTIFICATE statement to create a certificate within SQL Server 2005.

The common syntax of the CREATE CERTIFICATE statement is as follows:

CREATE CERTIFICATE CERTIFICATE_NAME [AUTHORIZATION USER_NAME]
{FROM FILE = 'PATH_TO_PRIVATE_KEY'
WITH PRIVATEKEY [, ENCRYPTION BY PASSWORD = 'PASSWORD' |
, DECRYPTION BY PASSWORD = 'PASSWORD']}
WITH SUBJECT = CERTIFICATE_SUBJECT_NAME, |
[START_DATE = MM/DD/YYYY
END_DATE = MM/DD/YYYY]

Here are definitions of the arguments in this syntax:

FILE = PATH_TO_PRIVATE_KEY Specifies the directory and the file name to the private key.
ENCRYPTION BY PASSWORD = ‘PASSWORD’ Specifies the password that will be used to encrypt the certificate private key.
DECRYPTION BY PASSWORD = ‘PASSWORD’ Specifies the password originally used to encrypt the private key.
CERTIFICATE_SUBJECT_NAME A descriptive string that will be embedded into the certificate metadata.
START_DATE Specifies the date in which the certificate becomes valid.
END_DATE Specifies the date in which the certificate expires.

For a full listing of all statement arguments, please refer to SQL Server 2005 Books Online.You will need the CREATE CERTIFICATE permission within the database to create a certificate.The following syntax creates a certificate and encrypts the certificate private key with the supplied password:

CREATE CERTIFICATE Certificate01 ENCRYPTION BY
PASSWORD = '&7YuKj%4@)aSZ@'
WITH SUBJECT = 'Certificate to test encryption',
START_DATE = '8/13/2007',
EXPIRY_DATE = '8/13/2011'

Unlike symmetric and asymmetric keys, certificates can be backed up individually. To back up a certificate, you can use the BACKUP CERTIFICATE statement:

BACKUP CERTIFICATE CERT_NAME TO FILE = 'PATH_TO_FILE'
[WITH PRIVATE KEY
(FILE = 'PATH_TO_PRIVATE_KEY_FILE',
ENCRYPTION BY PASSWORD = 'ENCRYPTION_PASSWORD',
DECRYPTION BY PASSWORD = 'DECRYPTION_PASSWORD')]

Here are definitions of the arguments of this syntax:

CERT_NAME Specifies the name of the certificate to be backed up.
PATH_TO_FILE Specifies the directory path and the filename that will be used for the certificate public key backup.
PATH_TO_PRIVATE_KEY_FILE Specifies the directory path and the filename that will be used for the certificate private key backup.
ENCRYPTION_PASSWORD Specifies the password that will be used to encrypt the certificate private key backup.
DECRYPTION_PASSWORD Specifies the password that will be used to decrypt the certificate private key within the database.

To execute the BACKUP CERTIFICATE you will need the CONTROL permission on the certificate and the VIEW DEFINITION permission on the database. The following syntax uses the BACKUP CERTIFICATE statement to back up both the public and private key of your previously created certificate, and encrypts the private key backup file with a user-supplied password:

BACKUP CERTIFICATE Certificate01 TO FILE =
'C:\backup\certificates\Certificate01.pub'
WITH PRIVATE KEY
(DECRYPTION BY PASSWORD = '&7YuKj%4@)aSZ@',
ENCRYPTION BY PASSWORD = '9UyZ%E!b8%7Ly#',
FILE = 'C:\backup\certificates\Certificate01.prv')

For a complete listing of statement arguments and permission requirements, please see SQL Server 2005 Books Online.To restore a certificate from a backup file, you can use the FROM FILE argument within the CREATE CERTIFICATE statement, which we covered earlier.The following syntax restores your previously backed up public and private key:

CREATE CERTIFICATE Certificate01 FROM FILE =
'C:\backup\certificates\Certificate01.pub'
WITH PRIVATE KEY (FILE = 'C:\backup\certificates\Certificate01.prv',
DECRYPTION BY PASSWORD = '9UyZ%E!b8%7Ly#',
ENCRYPTION BY PASSWORD = '&7YuKj%4@)aSZ@')

Note that if you created Certificate01 previously, you will need to drop the certificate prior to running the preceding syntax.You can obtain a listing of all certificates present in your database by using the sys.certificates view:

Select * from sys.certificates

To change the properties of a certificate you can use the ALTER CERTIFICATE statement:

ALTER CERTIFICATE CERTIFICATE_NAME
REMOVE PRIVATE KEY |
WITH PRIVATE KEY (FILE = 'PATH_TO_PRIVATE_KEY' |
DECRYPTION BY PASSWORD = 'PASSWORD' |
ENCRYPTION BY PASSWORD = 'PASSWORD')
WITH ACTIVE FOR BEGIN_DIALOG = [ON | OFF]

Here are definitions of the arguments of this syntax:

CERTIFICATE_NAME
The name of the certificate to be altered.
REMOVE PRIVATE KEY Removes the private key from the certificate.
FILE = ‘PATH_TO_PRIVATE_KEY Specifies the directory and the file name to the private key.
DECRYPTION BY PASSWORD = PASSWORD Specifies the password in which to decrypt the private key.
ENCRYPTION BY PASSWORD = PASSWORD Specifies the password in which to encrypt the private key
ACTIVE FOR BEGIN_DIALOG Enables or disables a certificate for use with Service Broker.

To run the ALTER CERTIFICATE command you will need the ALTER permission on the certificate.The following syntax changes your certificate private key protection method from user-supplied password to database master key:

ALTER CERTIFICATE Certificate01
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = '&7YuKj%4@)aSZ@')

To encrypt data using the certificate public key, you can use the ENCRYPTBYCERT statement:

ENCRYPTBYCERT (CERTIFICATE_ID, 'PLAINTEXT')

In this statement, CERTIFICATE_ID specifies the ID of the certificate to be used for encryption. PLAINTEXT is the data string you wish to encrypt.

You will need the VIEW DEFINITION permission on the certificate to execute the ENCRYPTBYCERT statement.The following syntax uses the ENCRYPTBYCERT statement to encrypt the supplied string using your certificate:

SELECT ENCRYPTBYCERT(Cert_ID('Certificate01'), 'certificate encryption test')

Here are the results:

0x50BCA9702D6999578923DAEC2B3EE96E69174429EBF54C392A532919679624097CD050110CEEF4DDB3BF
22656549268848C2F6E6BA70C0E543DFB411B654302AB9582A525DB835940FB76F9AAC501BBC5E3D689FB0
431BA7AF3C51A4DCDC5BCB7D101324E466A23447DF916E80D026E2A2E6D5A433E75804ADF8E9B75BF0E097

As we mentioned earlier, the preceding results will differ from what you receive on your SQL Server.To decrypt the cipher text, you can use the DECRYPTBYCERT statement:

DECRYPTBYCERT (CERTIFICATE_ID, 'CIPHERTEXT', CERT_PASSWORD)

Here are the definitions of the arguments of this syntax:

CERTIFICATE_ID The ID of the certificate to be used for encryption.
CIPHERTEXT The string that was previously encrypted with the certificate public key.
CERT_PASSWORD The password that encrypts the certificate private key.

To execute the DECRYPTBYCERT statement, you will need the VIEW DEFINITION permission on the certificate.The following syntax uses the DECRYPTBYCERT statement to decrypt the cipher text and convert the results into the human readable varchar data type:

SELECT CAST (DECRYPTBYCERT(Cert_ID('Certificate01'),
0x50BCA9702D6999578923DAEC2B3EE96E69174429EBF54C392A532919679624097CD050110CEEF4DDB
3BF22656549268848C2F6E6BA70C0E543DFB411B654302AB9582A525DB835940FB76F9AAC501BBC5E3D
689FB0431BA7AF3C51A4DCDC5BCB7D101324E466A23447DF916E80D026E2A2E6D5A433E75804ADF8E9B
75BF0E097)
AS varchar)

Note that you should substitute the cipher text in the preceding statement with the cipher text that you obtained from the earlier ENCRYPTBYCERT statement. Here is what the results of running the preceding statement will look like:

certificate encryption test

When you no longer need a certificate, it can be removed from the database using the DROP CERTIFICATE statement:

DROP CERTIFICATE CERTIFICATE_NAME

In this statement, CERTIFICATE_NAME specifies the name of the certificate to be removed.
To execute the DROP CERTIFICATE statement, you will need the CONTROL permission on the certificate.The following syntax drops your previously created certificate.

DROP CERTIFICATE Certificate01

The following script outlines the certificate encryption process from end to end:

-- Demonstration of certiifcate encryption
-- Create Database
CREATE Database CertEncryptDemo
GO
USE CertEncryptDemo
--
-- Switch to database context
--
-- Create table for data to be encrypted
CREATE Table Customers(
FirstName varchar(30),
LastName varchar(30),
CreditCardNum varbinary(300))
--
-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '5YtF4$aQ#W4d^W'
--
--** You should backup the Database Master Key immediately after creation! **
--
--Create certificate and use the databse master key to encrypt the private key
CREATE CERTIFICATE Certificate02
WITH SUBJECT = 'Test certificate for encryption',
START_DATE = '1/1/2007',
EXPIRY_DATE = '1/1/2012';
--
-- Populate table with data included encrypted credit card numbers
INSERT INTO Customers Values('Blake', 'Cabbage',
EncryptByCert(Cert_ID('Certificate02'), '342724356361631'))
INSERT INTO Customers Values('Colin', 'Edwareds',
EncryptByCert(Cert_ID('Certificate02'), '4516525615214110'))
INSERT INTO Customers Values('Anoson', 'Monroe',
EncryptByCert(Cert_ID('Certificate02'), '5582858885802510'))
Data Encryption • Chapter 8 241
--
--View the contents of the table
Select * from Customers
--
--View table data including the decrypted plain text credit card numbers
--
SELECT Firstname,LastName, CAST(DecryptByCert(Cert_ID('Certificate02'),
CreditCardNum) AS varchar) as 'CreditCardNum' from customers
--
--Clean-up demo
DROP CERTIFICATE Certificate02;
DROP MASTER KEY;
USE TEMPDB
DROP DATABASE CertEncryptDemo;
--END