Encryption Keys - SQL Server 2008

The SQL Server encryption model includes built-in encryption key management patterned after the ANSI X9.17 standard. This standard defines several layers of encryption keys, which are used to encrypt other keys, which in turn are used to encrypt actual data. The layers of encryption keys defined by the ANSI X9.17 standard and the roughly analogous layers defined by SQL Server are listed in Table.

SQL Server and ANSI X9.17 Encryption Key Layers Comparison

SQL Server and ANSI X9.17 Encryption Key Layers Comparison

The service master key (SMK) is the top-level key, the granddaddy of all SQL Server keys. There is a single SMK defined for each instance of SQL Server 2008. The SMK is secured by the Windows Data Protection API (DPAPI), and it is used to encrypt the next layer of keys, the database master keys (DMKs). The SMK is automatically created by SQL Server the first time it is needed. DMKs are used to encrypt symmetric keys, asymmetric keys, and certificates. Each database can have a single DMK defined for it.

The next layer of keys includes symmetric keys, asymmetric keys, and certificates. Symmetric keys are the primary means of encrypting data in the database. While asymmetric keys and certificates can be used to encrypt data (with some work on your part, which we will discuss in the “Asymmetric Keys” section later in this chapter), Microsoft recommends that you encrypt data exclusively with symmetric keys.

In addition to all the keys and certificates previously supported by SQL Server 2005, SQL Server 2008 introduces the concept of server certificates and database encryption keys in support of the new transparent data encryption functionality. The server certificate is simply a certificate created in the master database. The database encryption key is a special symmetric key used to encrypt an entire database at once. We’ll discuss the server certificate and database encryption key in detail in the “Transparent Data Encryption” section later in this chapter.

SQL Server encryption key hierarchy

SQL Server encryption key hierarchy

Service Master Key

SQL Server 2008 includes the following T-SQL statements to alter, back up, and drop SMKs:

  • ALTER SERVICE MASTER KEY: Allows you to change or regenerate the SMK. This statement can be used to change the SMK and to automatically decrypt and re-encrypt the entire encryption key hierarchy.
  • BACKUP SERVICE MASTER KEY: Backs up your SMK to a file. The SMK is encrypted before backup and stored in encrypted format. You must supply a password to be used to encrypt the SMK backup.
  • RESTORE SERVICE MASTER KEY: Restores your SMK from a file. The SMK RESTORE statement requires you to supply the same password used when you backed up the SMK. Like ALTER SERVICE MASTER KEY, the RESTORE SERVICE MASTER KEY statement regenerates the entire encryption key hierarchy.

After installing a new SQL Server 2008 instance, you should immediately back up the SMK and store it in a safe location. The BACKUP SERVICE MASTER KEY statement takes the following form:

BACKUP SERVICE MASTER KEY TO FILE = 'c:MKackup_master_key.dat' ENCRYPTION BY PASSWORD = 'p@$$w0rD';

In this example, the SMK is backed up to the file c:MKackup_master_key.dat, and it is encrypted with the password p@$$w0rD. The encryption password is required if you need to restore the SMK. If you need to alter, restore from backup, or regenerate your SMK, SQL Server will attempt to decrypt and re-encrypt all keys in the encryption key hierarchy. If any of these decryptions fail, the whole process will fail. If that happens, you can use the FORCE option on the ALTER SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY statements. However, be aware that if you must use the FORCEoption, you can count on data loss.

Database Master Keys

As mentioned previously, the SQL Server encryption key hierarchy includes a single DMK for each database. The DMK directly encrypts asymmetric keys and certificates that can be used to encrypt symmetric keys. Symmetric keys are used, in turn, to encrypt other symmetric keys and data.

Unlike the SMK, which is generated automatically the first time it is needed, a DMK must be created explicitly with the CREATE MASTER KEY statement. SQL Server includes the following T-SQL statements to manage DMKs:

  • CREATE MASTER KEY: Creates a DMK within a database. A password must be supplied to encrypt the DMK in the database when it is created.
  • ALTER MASTER KEY: Allows you to regenerate your DMK or to change how the DMK is secured by adding or removing encryption by password or SMK. If you regenerate the DMK, all the keys it protects will be decrypted and re-encrypted.
  • DROP MASTER KEY: Drops the DMK from the current database. If any private keys in the current database are protected by the DMK, the DROP statement will fail.
  • BACKUP MASTER KEY: Backs up the DMK to a file. You must specify a password, which will be used to encrypt the DMK in the file.
  • RESTORE MASTER KEY: Restores the DMK from a file. You must supply the same password you used when backing up the DMK for a RESTORE operation to succeed. You must also supply a second password to encrypt the DMK in the database after it is restored. During the restore process, SQL Server attempts to decrypt and re-encrypt all keys protected by the DMK.
  • OPEN MASTER KEY: Opens the DMK so that it can be used for encryption and decryption. The DMK must be open in order for any encryption or decryption operation to succeed, although SQL Server can implicitly open your DMK when it’s protected by the SMK, which we will discuss shortly.
  • CLOSE MASTER KEY: Closes a DMK that was explicitly opened using OPEN MASTER KEY after you are finished using it for encryption and decryption.

The ALTER MASTER KEY and RESTORE MASTER KEY statements attempt to regenerate the hierarchy of encryption keys that the DMK protects. That is to say, these statements try to automatically decrypt and re-encrypt all encryption keys below the DMK in the hierarchy. If any of these decryptions fail, the entire ALTER or RESTORE statement will fail. The FORCE option can be used to force an ALTER or RESTORE statement to complete regardless of errors. But be warned: the FORCE option should be used only as a last resort, since it always results in data loss.

All DMK management statements require CONTROL permission on the database, and they must be executed in the context of the current database.

The following statement creates a DMK in the AdventureWorks database:

USE AdventureWorks;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Avx3$5*!';

You should back up all of your DMKs and store them in safe locations as soon as you create them. You can back up the DMK with a statement like the following:

BACKUP MASTER KEY TO FILE = N'c:MKAwMasterKeyBackup.bak'
ENCRYPTION BY PASSWORD = N'#%e3)Fr';
GO

If you ever need to restore the DMK, use the RESTORE MASTER KEY statement:

RESTORE MASTER KEY FROM FILE = 'c:MKAwMasterKeyBackup.bak'
DECRYPTION BY PASSWORD = N'#%e3)Fr'
ENCRYPTION BY PASSWORD = N'Avx3$5*!';
GO

When restoring a DMK, you need to supply the same password in the DECRYPTION BY PASSWORD clause that you used when you performed the BACKUP operation.

SQL Server 2008 provides two methods of securing DMKs. Using the first method requires you to explicitly supply a password when you create, alter, or restore your DMK. This password will be used to encrypt the DMK and store it in the database. If you encrypt your DMK with a password, you must supply the same password every time you need to access the keys the DMK protects. This also means you need to use the OPEN MASTER KEY and CLOSE MASTER KEY statements to explicitly open and close the DMK.

By default, SQL Server also provides a second method of securing your DMKs. When you create a DMK, it is automatically encrypted using the SMK and Triple DES algorithm, with copies stored in both the current database and the master database. This allows SQL Server to automatically open and close your DMK when it is needed, without the need for you to supply a password.

On the plus side, this automatic SMK-based security makes development easier, since you don’t need to use explicit OPEN MASTER KEY and CLOSE MASTER KEY statements to open and close the DMKvery time you encrypt your data. You also don’t need to worry about managing, storing, and/or transmitting a password to SQL Server every time you want to perform an encryption or a decryption operation. The downside to this method (and you knew there would be one) is that every sysadmin can decrypt the DMK. In many businesses, this could be the deciding factor against using this feature.

You can use the ALTER MASTER KEY statement to turn off automatic encryption of your DMK by SMK

USE AdventureWorks;
GO
ALTER MASTER KEY
DROP ENCRYPTION BY SERVICE MASTER KEY;

Dropping the DMK is as simple as executing the DROP statement:

DROP MASTER KEY;

The OPEN MASTER KEY and CLOSE MASTER KEY statements are used to open and close the DMK so that it can be used to encrypt and decrypt the other keys and certificates that it protects. These keys can then be used to encrypt and decrypt symmetric keys and data. As we noted, SQL Server can implicitly open and close your DMK if it is not encrypted by the SMK. We will describe how to use the DMK with and without automatic SMK encryption in the “Symmetric Keys” section later in this chapter.

Asymmetric Keys

SQL Server encryption provides support for asymmetric keys, which are actually composed of a pair of encryption keys: a public key and a private key. The private key can have a key length of 512, 1,024, or 2,048 bits. SQL Server provides the following statements to manage asymmetric keys:

  • CREATE ASYMMETRIC KEY: Allows you to generate a new asymmetric key public key/private key pair, import the key pair from a file, or import a public key from a .NET assembly. This statement requires CREATE ASYMMETRIC KEY permissions on the database.
  • ALTER ASYMMETRIC KEY: Allows you to modify the properties of an existing asymmetric key. With this statement, you can remove the private key from the public key/private key pair or change the password used to encrypt a private key in the public key/private key pair. This statement requires CONTROL permission on the asymmetric key if you are removing the private key from it.
  • DROP ASYMMETRIC KEY: Drops an asymmetric key from the database. This statement requires CONTROL permission on the asymmetric key.

The algorithm/key length identifiers provided by SQL Server for use in the WITH ALGORITHM clause of the CREATE and ALTER ASYMMETRIC KEY statements are listed in Table.

Asymmetric Key Algorithm/Key Length Identifiers

Asymmetric Key Algorithm/Key Length Identifiers

When you create an asymmetric key, its private key is protected by the DMK by default. If the DMK does not exist, you must supply a password to encrypt the private key at creation time. However, if the DMK does exist, the ENCRYPTION BY PASSWORD clause of the CREATE statement is optional.

When altering an asymmetric key pair with the ALTER ASYMMETRIC KEY statement, the following rules apply:

  • If you are changing the password used to encrypt the private key or if the private key is currently protected by the DMK and you want to change it to be encrypted by password, the ENCRYPTION BY PASSWORD clause is mandatory.
  • If the private key is currently protected by password and you want to change it to encryption by DMK or you are changing the password used to encrypt the private key, the DECRYPTION BY PASSWORD clause is required.

SQL Server provides the built-in EncryptByAsymKey and DecryptByAsymKey T-SQL functions to encrypt and decrypt data via asymmetric keys. EncryptByAsymKey requires you to supply the asymmetric key pair ID number, obtained with the AsymKey_ID function. AsymKey_ID takes the name of the asymmetric key as a parameter and returns the integer ID of the key as a result. EncryptByAsymKey also accepts its plain text to encrypt in the form of a char, nchar, varchar, nvarchar, binary, or varbinary constant, expression, variable, or column name (in a DML statement). EncryptByAsymKey returns a varbinary result, regardless of the type of the plain text passed in.

The DecryptByAsymKey function decrypts data that was previously encrypted using Encrypt ByAsymKey. Decrypt ByAsymKey accepts the asymmetric key pair ID number, just like the Encrypt ByAsymKey function. It also accepts the varbinary encrypted text and an optional asymmetric key password, which is required if the asymmetric key is encrypted by password. The asymmetric key password can be omitted if the asymmetric key is secured by the DMK, but must be of nvarchar type if it is used.

Here is an example of encryption and decryption by asymmetric key:

CREATE ASYMMETRIC KEY SampleAsymKey
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = N'B&^19!{f!5h';
DECLARE @plaintext NVARCHAR(58);
DECLARE @ciphertext VARBINARY(256);
-- Initialize the plain text
SET @plaintext = N'This is a sample plain text string';
PRINT @plaintext;
-- Encrypt the plain text
SET @ciphertext = EncryptByAsymKey (AsymKey_ID (N'SampleAsymKey'), @plaintext);
PRINT @ciphertext;
-- Decrypt the cipher text
SET @plaintext = DecryptByAsymKey (AsymKey_ID (N'SampleAsymKey'), @ciphertext,
N'B&^19!{f!5h');
PRINT CAST(@plaintext AS NVARCHAR(MAX));
DROP ASYMMETRIC KEY SampleAsymKey;
GO

Although SQL Server 2008 provides the EncryptByAsymKey and DecryptByAsymKey encryption functions, Microsoft recommends that you use asymmetric keys to encrypt symmetric keys only, and use symmetric keys to encrypt your data. One reason for this is speed. Symmetric encryption is considerably faster than asymmetric encryption. Another reason for encrypting data with symmetric encryption is the limitation on the sizes of data that asymmetric encryption can handle. Table shows the limitations of the asymmetric algorithms based on the private key lengths implemented in SQL Server 2008.

Asymmetric Algorithms, Key Lengths, and Limitations

Asymmetric Algorithms, Key Lengths, and Limitations

For an asymmetric key with a private key 1,024 bits long, for instance, the RSA_1024 algorithm will encrypt a varchar value with only a maximum length of 117 characters, or an nvarchar value with a maximum length of 58 characters. This limitation makes asymmetric encryption a poor choice for data of any considerable length. If, however, you get stuck encrypting lengthy data asymmetrically (perhaps because of business requirements, for example), you can use a work-around like the user defined functions (UDFs).

The BigAsymEncrypt function in this listing divides up the nvarchar(max) plain text passed into it and encrypts it in chunks. The size of the plain text chunks is equal to the number of bits in the asymmetric encryption key’s private key divided by 16 (if the plain text were varchar instead of nvarchar, it would be divided by 8 instead), minus 11 bytes. The 11 extra bytes are used by the Microsoft Enhanced Cryptographic Provider for PKCS #1 padding. The UDF performs a loop, incrementing the loop counter by the calculated chunk size after each iteration. The BigAsymDecrypt function divides up the encrypted

cipher text, decrypting it in chunks and appending the decrypted plain text chunks to the nvarchar result. The chunk size of the varbinary encrypted text is calculated as the length of the asymmetric encryption key’s private key divided by 8. Here is an example of these UDFs in action:

USE AdventureWorks;
GO -- This code tests the chunked asymmetric encryption functions
DECLARE @testplain NVARCHAR(MAX);
DECLARE @testcipher VARBINARY(MAX); -- Define the plain text for testing
SET @testplain = N'"A human being is a part of a whole, called by us' +
N'''universe'', a part limited in time and space. He ' +
N'experiences himself, his thoughts and feelings as something ' +
N'separated from the rest... a kind of optical delusion of his ' +
N'consciousness. This delusion is a kind of prison for us, ' +
N'restricting us to our personal desires and to affection for a ' +
N'few persons nearest to us. Our task must be to free ourselves ' +
N'from this prison by widening our circle of compassion to ' +
N'embrace all living creatures and the whole of nature in ' +
N'its beauty." - Albert Einstein'; -- Create a test asymmetric key
CREATE ASYMMETRIC KEY SampleAsymKey
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = N'B&^19!{f!5h'; -- Test the BigAsymEncrypt and BigAsymDecrypt functions
PRINT @testplain
SET @testcipher = dbo.BigAsymEncrypt (N'SampleAsymKey', @testplain);
PRINT @testcipher
PRINT dbo.BigAsymDecrypt (N'SampleAsymKey', @testcipher, N'B&^19!{f!5h'); -- Drop the test asymmetric key
DROP ASYMMETRIC KEY SampleAsymKey;
GO

This example uses the asymmetric encryption functions to encrypt and decrypt a large nvarchar(max) string. Although you can use methods like this to work around the asymmetric encryption size limitations, symmetric encryption is considerably faster and should generally be used to encrypt your data.

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 ).

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.

The 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:MKBackupSampleCert.cer'
WITH PRIVATE KEY (
FILE = N'c:MKBackupSampleCert.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

CREATE CERTIFICATE SampleCert
FROM FILE = N'c:MKBackupSampleCert.cer'
WITH PRIVATE KEY (
FILE = N'c:MKBackupSampleCert.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 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

Symmetric Keys

Symmetric keys are at the bottom of the SQL Server encryption key hierarchy. A symmetric key is used to encrypt other symmetric keys or data. Because symmetric key encryption is so much faster than asymmetric encryption and does not suffer the same data-length limitations as SQL Server’s asymmetric encryption implementations, Microsoft recommends encrypting your data exclusively with symmetric keys.

While asymmetric encryption requires two keys (a public key/private key pair), symmetric encryption requires only a single key to both encrypt and decrypt your data. Symmetric encryption is performed using block cipher algorithms, which encrypt your data in blocks of a constant size, and stream cipher algorithms, which encrypt your data in a continuous stream. Block cipher algorithms have a set encryption key size and encryption block size.

SQL 2008 Supported Algorithms

SQL 2008 Supported Algorithms

You can calculate the size of the cipher text based on the length of the plain text using one of the formulas:

  • For 8-byte block ciphers like the Data Encryption Standard (DES) family, use length of
    ciphertext = 8 * ( ( length of plaintext + 8 ) / 8 ) + 36.
  • For 16-byte block ciphers like the Advanced Encryption Standard (AES), use length of
    ciphertext = 16 * ( ( length of plaintext + 16 ) / 16 ) + 44.

For either formula, add 20 bytes to the total length if you use an authenticator.

SQL Server provides the following statements to manage symmetric keys:

  • CREATE SYMMETRIC KEY: Creates a symmetric key to be used for encryption. Symmetric keys can be encrypted by certificates, asymmetric keys, passwords, or even other symmetric keys.
  • ALTER SYMMETRIC KEY: Allows you to change the method of securing your symmetric keys.
  • DROP SYMMETRIC KEY: Drops a symmetric key from the database. Symmetric keys cannot be dropped while they are open.
  • OPEN SYMMETRIC KEY: Opens and decrypts a symmetric key for use.
  • CLOSE SYMMETRIC KEY: Closes a symmetric key that was previously opened.
  • CLOSE ALL SYMMETRIC KEYS: Closes all symmetric keys currently open in the current session.

SQL Server does not provide backup or restore statements for symmetric keys. Because symmetric keys are stored in the current database, they are backed up during the normal database backup process. You can also re-create a symmetric key from scratch with the CREATE SYMMETRIC KEY statement. In order to re-create a symmetric key from scratch, you must supply a KEY_SOURCE and IDENTITY_VALUE. The KEY_SOURCE is a value SQL Server hashes and performs bitwise manipulations on to generate a symmetric encryption key. If not specified, SQL Server randomly generates a KEY_SOURCE.

The IDENTITY_VALUE is a value SQL Server uses to generate a key GUID. Copies of the key GUID are stored with the data the key is used to encrypt. In order to re-create a symmetric key, you must supply the same KEY_SOURCE and IDENTITY_VALUE originally used to create the key. SQL Server guarantees that supplying duplicate IDENTITY_VALUE and KEY_SOURCE values will generate an identical key.

The following example creates a symmetric key and then drops it:

CREATE SYMMETRIC KEY SymTest
WITH ALGORITHM = Triple_DES
ENCRYPTION BY PASSWORD = '$#ad%61*(;dsPSlk';
DROP SYMMETRIC KEY SymTest;

Of course, creating a symmetric key is not very useful if you can’t use it to encrypt things. And as we mentioned, symmetric keys in SQL Server can be used to protect other symmetric keys or data. To protect a symmetric key with another symmetric key, use the ENCRYPTION BY SYMMETRIC KEY clause of the CREATE SYMMETRIC KEY statement. To encrypt and decrypt data, use the EncryptByKey and DecryptByKey functions. The following example creates a symmetric key, which is used to encrypt another symmetric key, which is used in turn by EncryptByKey and DecryptByKey to encrypt and decrypt some data.

USE AdventureWorks;
GO
-- Create a symmetric key to encrypt a symmetric key
CREATE SYMMETRIC KEY SymKey
WITH ALGORITHM = Triple_DES
ENCRYPTION BY PASSWORD = '$#ad%61*(;dsPSlk';
-- Open the key-encrypting key
OPEN SYMMETRIC KEY SymKey
DECRYPTION BY PASSWORD = '$#ad%61*(;dsPSlk';
-- Create a symmetric key to encrypt data
CREATE SYMMETRIC KEY SymData
WITH ALGORITHM = Triple_DES
ENCRYPTION BY SYMMETRIC KEY SymKey;
-- Open the data-encrypting key
OPEN SYMMETRIC KEY SymData
DECRYPTION BY SYMMETRIC KEY SymKey;
-- Initialize the plain text
DECLARE @plain_text NVARCHAR(512);
SET @plain_text = N'"Those who would give up Essential Liberty to purchase a ' +
N'little Temporary Safety, deserve neither Liberty nor Safety." - Ben Franklin'
PRINT @plain_text;
-- Encrypt the data
DECLARE @cipher_text VARBINARY(1024);
SET @cipher_text = EncryptByKey(Key_GUID(N'SymData'), @plain_text);
PRINT @cipher_text;
-- Decrypt the data
SET @plain_text = CAST(DecryptByKey(@cipher_text) AS NVARCHAR(512));
PRINT @plain_text;
-- Close the data-encrypting key
CLOSE SYMMETRIC KEY SymData;
-- Close the key-encrypting key
CLOSE SYMMETRIC KEY SymKey;
-- Drop the symmetric keys
DROP SYMMETRIC KEY SymData;
DROP SYMMETRIC KEY SymKey;

The EncryptByKey function requires the key GUID of the symmetric key to encrypt your data. The symmetric key GUID can be retrieved by passing the name of the key to the Key_GUID function. The plain_text passed into the function is char, varchar, nchar, nvarchar, binary, or varbinary data. The return value of EncryptByKey is varbinary(8000). Block mode ciphers on SQL Server, like Triple DES and AES, automatically use an encryption mode known as Cipher Block Chaining (CBC) mode and random initialization vectors (IVs) to further obfuscate your encrypted data.

In addition, the EncryptByKey function also accepts an optional authenticator value to help defeat whole-value substitutions of your data. The authenticator value passed in is a sysname, which is synonymous with nvarchar(128). When an authenticator value is provided, it is encrypted together with the plain text to even further obfuscate your data. The authenticator value can be used to “tie” your encrypted data to a specific row. If you do use an authenticator, the add_authenticator parameter to EncryptByKey must be set to 1.

The DecryptByKey function accepts your encrypted data as a varbinary(8000), and returns the decrypted plain text as a varbinary(8000). If your original data was varchar or nvarchar, then you will need to CAST or CONVERT the result back to its original datatype. If you used an authenticator value when you encrypted the plain text, you must supply the same authenticator value to decrypt your cipher text. Note that you don’t need to supply the Key_GUID when you call DecryptByKey. This is because SQL Server stores the key GUID with the encrypted data during the encryption process.

In addition to padding your plain text before encrypting it, SQL Server stores additional metadata with your cipher text, including the following:

When you use the EncryptByKey and DecryptByKey functions, and the symmetric key you are using to encrypt or decrypt data is protected by another key, you must explicitly open the symmetric key with the OPEN SYMMETRIC KEY statement. SQL Server provides the following additional functions that automatically open and decrypt your symmetric key before decrypting your data:

  • DecryptByKeyAutoAsymKey: Decrypts your data with a symmetric key that is protected by an asymmetric key. This function automatically opens and decrypts your symmetric key with its associated asymmetric key.
  • DecryptByKeyAutoCert: Decrypts your data using a symmetric key that is protected by a certificate. This function automatically opens and decrypts your symmetric key with its associated certificate.

Keys are available to users who have been granted access in all current SQL Server sessions at any given time (apart from temporary symmetric keys, which were mentioned earlier in this section). Each session can open and close keys independently of the other sessions. For instance, if users Joe and Lisa had open SQL Server sessions at the same time, both could use the same symmetric key simultaneously. If Joe closed the symmetric key in his session, this would have no effect on Lisa’s session or her open symmetric key.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics