Microsoft® SQL Server® 2012 Bible

856

Part VI: Securing Your SQL Server

Result:

MembershipCardID CustomerID MembersCardNumber MembershipExpires

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

2 1 99999991111 0413

Sure enough, the data decrypted to the same value previously inserted. If the passphrase

were incorrect, then the result would have been null.

There is one other option to the passphrase encryption method. An authenticator may be

added to the encryption to further enhance it. Typically, some internal hard-coded value

unknown by the user is used as the authenticator to make it more diffi cult to decrypt the

data if it’s removed from the database.

An example of the importance of using the authenticator option is that if you look at

a list of employees where you can read the names of the employees but the salaries are

encrypted, you can then update your salary to have the same encrypted value of your CEO

and give yourself a nice pay raise. If the company uses the authenticator option, it ensures

that it can decrypt and encrypt the data before updating it. If your data is encrypted and

you try to update you salary with the CEO’s salary value but you can’t decrypt or encrypt

the data, then you end up with a salary of null!

The following code sample adds the authenticator to the passphrase encryption. The code,

1 , enables the authenticator, and the last parameter is the authenticator phrase:

INSERT GYM_Membership (CustomerID, MembershipCardNumber, MembershipExpires)

VALUES(3,EncryptbyPassPhrase('Passphrase','99999992222',

1, 'hardCoded Authenticator'), '0413');

SELECT MembershipCardID, CustomerID,

CONVERT(VARCHAR(20),DecryptByPassPhrase('Passphrase', MembershipCardNumber,

1, 'hardCoded Authenticator')),

FROM GYM_Membership

WHERE CustomerID = 3;

Result:

MembershipCardID CustomerID MembersCardNumber MembershipExpires

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

3 3 99999992222 0413

Encrypting with a Symmetric Key

Using a symmetric key provides an actual object for the encryption, rather than just a

human-readable passphrase. Symmetric keys can be created within SQL Server using the

create DDL command:

CREATE SYMMETRIC KEY MembershipCardKey

WITH ALGORITHM = TRIPLE_DES

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

c34.indd 856c34.indd 856 7/31/2012 10:01:46 AM7/31/2012 10:01:46 AM

http://www.it-ebooks.info