855
Chapter 34: Data Encryption
34
To actually encrypt data, SQL Server provides fi ve methods:■ Transact-SQL functions■ (^) Symmetric key
■ Asymmetric key
■ (^) Certifi cate
■ Transparent Data Encryption
Encrypting with Transact-SQL
The fi rst method to encrypt data is to use T-SQL, similar to a password but without the
strong password requirements. The encrypted data is binary so the example code uses a
varbinary data type for the membershipcardnumber column. You should test your situation
to determine the required binary length.
The actual encryption is accomplished using the EncryptbyPassPhrase function. The
fi rst parameter is the passphrase, followed by the data to be encrypted. This example dem-
onstrates encrypting data using the insert DML command:
CREATE TABLE GYM_Membership (
MembershipCardID INT IDENTITY PRIMARY KEY NOT NULL,
CustomerID INT NOT NULL,
MembershipCardNumber VARBINARY(128),
MembershipExpires CHAR(4)
);
INSERT GYM_Membership(CustomerID, MembershipCardNumber, MembershipExpires)
VALUES(1,EncryptByPassPhrase('Passphrase', '99999991111'), '0413');
A normal select query views the encrypted value actually stored in the database:
SELECT *
FROM GYM_Membership
WHERE CustomerID = 1;
Result (binary value abridged):
MembershipCardID CustomerID MembershipCardNumber MembershipExpires
2 1 0x010000005FDB9A 0413
To decrypt the membership card data into readable text, use the DecryptByPassPhrase
function and convert the binary result back to a readable format:
SELECT MembershipCardID, CustomerID,
CONVERT(VARCHAR(20), DecryptByPassPhrase('Passphrase', MembershipCardNumber)),
MembershipExpires
FROM GYM_Membership
WHERE CustomerID = 1;
c34.indd 855c34.indd 855 7/31/2012 10:01:46 AM7/31/2012 10:01:46 AM
http://www.it-ebooks.info