|
Encrypt and Decrypt Data in Yukon
SQL Server 2005's engine-based, data-encryption functions protect confidential information from unauthorized access and disclosure, and offer native key management as a bonus.
by Roger Jennings
June 23, 2005
Technology Toolbox: VB.NET; SQL Server 2005 Developer or Express edition, April 2005 CTP or later; VS 2005 Express, Developer or higher edition, beta 2 or later
Reports of more than 2 million credit card numbers stolen from merchants and data brokers in early 2005 brought about the expected knee-jerk reaction from federal lawmakers— new legislation that proposes to protect individuals against identity theft, such as the "Comprehensive Identity Theft Protection Act" (S. 768; see Additional Resources).
However, the first line of defense against unauthorized large-scale disclosure of confidential personal information is encryption at the database column or cell level. In this article, I'll explain how to take advantage of SQL Server 2005's new native encryption features to reduce the risk of exposure of confidential information to unauthorized individuals or organizations and to potential civil or criminal penalties. SQL Server 2005 Express Edition (SQLX) supports the same set of data encryption features. Sample code and a downloadable VS 2005/VB Express project deliver production-grade data encryption examples (see Figure 1). Live encryption examples compensate—at least in part—for the inadequate and sometimes incorrect encryption-related topics in the April CTP's Books Online.
SQL Server 2005's new encryption functions enable the database engine to execute high-strength cryptographic algorithms for one-way hash indexes and two-way encryption with plaintext passwords, X.509 certificates, or symmetric or asymmetric keys. You can encrypt symmetric keys with passwords, certificates, or other keys; asymmetric keys support optional password encryption. Encryption and decryption operate at the cell level, so a single table column can contain varbinary ciphertext data that's encrypted by multiple methods and different users. SQL Server 2005 establishes the key management infrastructure for users and client applications by encrypting keys with a service master key for each instance and database master keys based on the service master key, which is encrypted by Windows Data Protection API (DPAPI). Alternatively, individual administrators who are responsible for safeguarding confidential data can manage the key infrastructure by assuming responsibility for creating and safeguarding passwords that encrypt keys.
The simplest way to avoid liability for unintentional disclosure of personal information is not to retain a potentially readable copy. If you don't need to know the original (plaintext) value, the T-SQL HashBytes function lets you store the hashed (cyphertext) value of credit card, social security, or driver's license numbers as varbinary(16) or varbinary(20) ciphertext. It's not practical using cryptography to derive the plaintext (char, varchar, nchar, or nvarchar) value from the byte array stored as varbinary data, so the original data is fully protected. This basic syntax for the HashBytes function supports five encryption algorithms:
DECLARE @Ciphertext varbinary(20);
DECLARE @Plaintext [n]varchar;
SET @Plaintext = 'SomePlainText;'
@Ciphertext =
HashBytes('{MD2|MD4|MD5|SHA|SHA1}',
@Plaintext);
MD2, MD4, and MD5 message-digest algorithms produce 16-byte arrays and are easier to crack than the 20-byte array of the Secure Hash Algorithm (SHA and SHA1). Significant flaws have been found in MD4 and SHA algorithms, and MD2 is a bit slower than MD5; thus, SHA1 is recommended for new projects. Use this sample instruction to batch-update varbinary columns with 20-byte SHA1 hashes of 20-character varchar plaintext values at a rate of about 60,000 rows per second:
UPDATE Encrypted SET Encrypted.CardNumber =
HashBytes('SHA1', CreditCards.CardNumber +
CreditCards.Issuer)
FROM Encrypted INNER JOIN CreditCards ON
Encrypted.CustomerID =
CreditCards.ReferenceID;
Back to top
|