Microsoft® SQL Server® 2012 Bible

858

Part VI: Securing Your SQL Server

Using the Symmetric Key

To use the symmetric key, the fi rst step is to open the key. This decrypts the key and

makes it available for use by SQL Server:

OPEN SYMMETRIC KEY MembershipCardKey

DECRYPTION BY PASSWORD = 'P@s$wOrD';

Using the same gym_membership table created previously, the next code snippet encrypts

the data using the MembershipCardKey key. The EncryptByKey function accepts the

GUID identifi er of the key, which can be found using the key_guid() function, and the

actual data to be encrypted:

INSERT gym_membership(CustomerID, MembershipCardNumber, MembershipExpires)

VALUES(7,EncryptByKey(Key_GUID('MembershipCardKey'),'99999993333'), '0413');

To decrypt the data the key must be open. The decryptbykey function identifi es the cor-

rect key from the data and performs the decryption:

SELECT MembershipCardID, CustomerID,

CONVERT(varchar(20), DecryptByKey(MembershipCardNumber)) as MembershipCardNumber,

MembershipExpires

FROM gym_membership

WHERE CustomerID = 7;

Result:

MembershipCardID CustomerID MembershipCardNumber MembershipExpires

---------------- ---------- ------------------- -----------------

3 7 99999993333 0413

It’s a good practice to close the key after the transaction:

CLOSE SYMMETRIC KEY MembershipCardKey

For most applications, you want to encrypt the data as it goes into the database and

decrypt it as it is selected. If you want to move the data to another server and decrypt

it there, then both servers must have identical keys. To generate the same key on two

servers, the key must be created with the same algorithm, identity_value, and

key_phrase.

Using Asymmetric Keys

Using asymmetric keys involves encrypting and decrypting with matching private and pub-

lic keys. Generating an asymmetric key is similar to generating a symmetric key:

CREATE ASYMMETRIC KEY GymMembershipKey

WITH ALGORITHM = RSA_512

ENCRYPTION BY PASSWORD = 'P@s$w0rD';

c34.indd 858c34.indd 858 7/31/2012 10:01:47 AM7/31/2012 10:01:47 AM

http://www.it-ebooks.info