SQL Server 2008 Encryption Keys - Certificates

Certificates are another tool provided by SQL Server for asymmetric encryption. A certificate is basically an asymmetric key public key/private key pair containing additional data describing the certificate. The additional data includes a start date, expiration date, and certificate subject. Unlike SQL Server’s asymmetric keys, certificates can be backed up to and restored from files. If you need SQL Server to generate your public key/private key pairs for asymmetric encryption, the ability to create backups makes certificates a better option than asymmetric keys.

Certificates are signed by a certifying authority, which is often a trusted third party, although SQL Server can generate self-signed certificates as well. SQL Server supports certificates that follow the International Telecommunication Union Telecommunication Standardization Sector (ITU-T) X.509 standard (available at http://www.itu.int/ITUT/index.phtml).

SQL Server provides the following T-SQL extensions for managing certificates:

• CREATE CERTIFICATE: Allows you to generate self-signed SQL Server certificates, load certificates from Distinguished Encoding Rules (DER)-encoded files, or create them from certificate-signed dynamic link library (DLL) files. If the ENCRYPTION BY PASSWORD clause is omitted, SQL Server will use the DMK to secure the certificate by default.

• BACKUP CERTIFICATE: Allows you to export a certificate to a file. The exported private key is encrypted with a password you supply in the ENCRYPTION BY PASSWORD clause. There is no RESTORE CERTIFICATE statement; to restore a backed-up certificate, use the CREATE CERTIFICATE statement.

• ALTER CERTIFICATE: Allows you to add or remove a private key from a certificate, change a certificate’s private key, or make a certificate available for Service Broker dialogs.

• DROP CERTIFICATE: Drops an existing certificate. A certificate that is currently being used to protect symmetric keys cannot be dropped.

SQL Server supports certificate private key lengths from 384 to 3,456 bits, in multiples of 64 bits, for private keys imported from DER-encoded files or certificate-signed DLLs. Certificate private keys generated by SQL Server are 1,024 bits long.

The following example demonstrates how to generate and back up a self-signed SQL Server certificate and its private key (which is backed up to a separate file).

USE AdventureWorks;
GO

CREATE CERTIFICATE SampleCert
ENCRYPTION BY PASSWORD = N'p$@1k-#tZ'
WITH SUBJECT = N'Sample Certificate',
EXPIRY_DATE = N'10/31/2026';

BACKUP CERTIFICATE SampleCert
TO FILE = N'c:\MK\BackupSampleCert.cer'
WITH PRIVATE KEY (
FILE = N'c:\MK\BackupSampleCert.pvk' ,
ENCRYPTION BY PASSWORD = N'p@$$w0rd',
DECRYPTION BY PASSWORD = N'p$@1k-#tZ'
);

DROP CERTIFICATE SampleCert;
GO

To restore the backed-up certificate and its private key, you could run a CREATE CERTIFICATE statement like the following:

CREATE CERTIFICATE SampleCert
FROM FILE = N'c:\MK\BackupSampleCert.cer'
WITH PRIVATE KEY (
FILE = N'c:\MK\BackupSampleCert.pvk',
DECRYPTION BY PASSWORD = N'p@$$w0rd',
ENCRYPTION BY PASSWORD = N'p$@1k-#tZ'
);
GO

Microsoft recommends that certificates, like asymmetric keys, be used to encrypt your symmetric keys, and symmetric keys be used to encrypt your data. T-SQL does, however, provide the functions EncryptByCert and DecryptByCert to encrypt data using certificates. Encryption by certificate has the same limitations on length as asymmetric encryption. The maximum length of the plain text you can pass to EncryptByCert can be calculated using this formula: clear_text_max_bytes = ( private_key_length_bits / 8 ) – 11. The length of the encrypted text returned can be calculated using this formula: cipher_text_bytes = ( private_key_length_bits / 8 ).

The EncryptByCert and DecryptByCert functions both require a certificate ID, which is the int ID number for a given certificate. The Cert_ID function can be used to retrieve the ID number for a certificate by name. To use Cert_ID, pass it the name of the certificate as an nvarchar or a varchar. The EncryptByCert function accepts the plain text you wish to encrypt using a certificate. The DecryptByCert function accepts the previously encrypted text you wish to decrypt. The DecryptByCert function includes a third (optional) parameter, the certificate password, which is the same password specified when you created the certificate. If the certificate is secured by the DMK, this parameter should be left out of the call to DecryptByCert.

The following example shows how to use EncryptByCert and DecryptByCert, assuming that the SampleCert certificate created earlier in this section currently exists in the AdventureWorks database.

USE AdventureWorks;
GO

-- Initialize the plain text
DECLARE @plain_text NVARCHAR(58);

SET @plain_text = N'This is a test!';
PRINT @plain_text;

-- Encrypt the plain text using the certificate
DECLARE @cipher_text VARBINARY(127);
SET @cipher_text = EncryptByCert(Cert_ID(N'SampleCert'), @plain_text);
PRINT @cipher_text;

-- Decrypt the cipher text using the certificate
SET @plain_text = CAST(DecryptByCert(Cert_ID(N'SampleCert'),
@cipher_text, N'p$@1k-#tZ') AS NVARCHAR(58));
PRINT @plain_text;
GO

Source of Information : Apress Accelerated SQL Server 2008