Developer Product Briefs

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
• 08/01/2005

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;

A practical use for HashBytes is to verify a customer's credit card number by testing its hashed value against a local table with a hashed credit card number and associated customer ID, name, address, and other columns:

SELECT * FROM Encrypted WHERE CardNumber = 
HashBytes('SHA1', '2206000318466846VISA');

In this example, the 16 digits serve as the customer-supplied credit card number, which isn't persisted in a local database, while VISA is a four-character issuer code that "salts the hash." If the site's acquirer (a merchant bank or service provider) supports a standard hash algorithm, it's possible for the site to issue a similar query to complete a transaction. Otherwise, the site can send the locally verified, plaintext credit card number through a Secure Sockets Layer (SSL) connection to initiate a transaction, obtain and store a transaction ID, and then purge the plaintext number from volatile memory. If the site's database security is compromised or backup tapes are lost or stolen, data thieves can't regenerate the credit card numbers from stored data.

Implement Two-Way Encryption
In most cases, authorized database users need selective access to the original plaintext data, which requires conventional, two-way encryption. The simplest and fastest two-way encryption/decryption method uses a passphrase (a shared, secret password) for symmetrical cryptographic operations. Data security is a function of the length and complexity of the passphrase, as well as how well the passphrase is protected. Use this sample instruction to bulk-encrypt plaintext data with a strong passphrase:

UPDATE Encrypted SET Encrypted.CardNumber = 
   EncryptByPassPhrase('Abracadabra!1234$', 
      CreditCards.CardNumber) 
FROM Encrypted INNER JOIN CreditCards 
   ON Encrypted.CustomerID = 
      CreditCards.ReferenceID

This instruction decrypts a single ciphertext value to cleartext by supplying the shared secret:

SELECT CONVERT(varchar, 
   DecryptByPassPhrase('Abracadabra!1234$', 
      CardNumber)) 
FROM Encrypted 
WHERE CustomerID = 100000001;

You must use the CONVERT or CAST function to deliver readable varchar or nvarchar data from the varbinary byte arrays that each of the four EncryptByMethod and DecryptByMethod function pairs return. Two-way encryption methods are PassPhrase, Cert (certificate), Key (symmetric key), and AsmKey (asymmetric key). The SQL Server native data provider doesn't throw an exception when decryption fails as the result of an incorrect passphrase value, wrong certificate ID, or mismatched DecryptByMethod; instead, it returns a NULL value to the cell (see Figure 2). Thus, logging encryption-related events for security audits requires adding T-SQL or managed code.

The obvious security issue with the PassPhrase method is sharing cleartext secrets over a LAN where anyone with a network sniffer or access to Network Monitor can intercept them. Providing SSL transport-level or IPSec network encryption, or using stored procedures created with the ENCRYPTION option to encrypt and decrypt data, mitigates interception issues. (Note that adding "Encrypt=True" to the end of a SQL Server 2005 connection string SSL encrypts the transport if a valid SSL Server Authentication Certificate is installed on the machine.) SQL Server 2005 Profiler substitutes dashes for the text of T-SQL batches that include EncryptByMethod() or DecryptByMethod() functions (see Figure 3). You receive a "SQL Profiler doesn't support SQL Express" if you attempt to open a connection to a SQLX instance.

Sensitive personal information, such as "protected health information" (PHI) that's defined by the Health Insurance Portability and Accountability Act of 1996 (HIPAA), requires restricting access to plaintext private data (see Additional Resources). The most commonly accepted method of protecting PHI is two-way encryption with symmetric or asymmetric keys. You start the encryption key chain by creating a database master key from the autogenerated SQL Server 2005 service master key with this instruction:

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'Complex#2005Password'

Service (##MS_ServiceMasterKey##) and database (##MS_DatabaseMasterKey##) master keys are symmetric keys that use the Triple-DES encryption algorithm with a 128-byte key length. HashBytes and PassPhrase encryption don't require creating a database master key. You can display the properties of master keys by executing SELECT * FROM sys.symmetric_keys from the master or active database. (CreditCards is the active database for this article's examples).

Certificates and asymmetric keys are the simplest methods for strongly encrypting sensitive data without exposing passwords or passphrases. This T-SQL instruction creates an X.509 certificate with an ID of dboCert, binds its public key to dbo, encrypts the private key with the database master key, and specifies a default one-year certificate validity period:

CREATE CERTIFICATE dboCert AUTHORIZATION 
   dbo WITH SUBJECT = 'Certificate for dbo';

If you want to extend the certificate's validity period, add an EXPIRY_DATE modifier to the WITH clause:

WITH SUBJECT = 'Certificate for dbo', 
EXPIRY_DATE = '5/31/2010';

After you create the certificate, you can use its public key to bulk-encrypt data with this batch instruction:

UPDATE Encrypted SET Encrypted.CardNumber = 
   EncryptByCert(Cert_ID('dboCert'), 
      CreditCards.CardNumber) 
FROM Encrypted INNER JOIN CreditCards 
   ON Encrypted.CustomerID = 
      CreditCards.ReferenceID

Next, use the private key to bulk-decrypt the encrypted data with a batch instruction:

UPDATE Encrypted SET CardNumber = 
   DecryptByCert(Cert_ID('dboCert'), CardNumber) 
FROM Encrypted

Asymmetric keys provide a more flexible approach to using the Public Key Infrastructure (PKI) for data encryption and decryption with the Rivest-Shamir-Adelman (RSA) algorithm. The T-SQL syntax is similar to that for certificates, but you can specify the private key length as 512, 1,024, or 2,048 bytes. This instruction creates a PKI key pair with a 2,048-byte private key that's encrypted by the database master key:

CREATE ASYMMETRIC KEY dboAsymKey 
AUTHORIZATION dbo 
WITH ALGORITHM = RSA_2048;

Use this statement to substitute encryption by a complex password for the database master key:

CREATE ASYMMETRIC KEY dboAsymKey AUTHORIZATION dbo 
WITH ALGORITHM = RSA_512 
ENCRYPTION BY PASSWORD = 
   'Abracadabra!1234$';

You don't need to supply the private key password to encrypt the plaintext data with the public key. This data encryption instruction works for either of the two preceding keys:

UPDATE Encrypted SET Encrypted.CardNumber = 
   EncryptByAsymKey(
   AsymKey_ID('dboAsymKey'),
   CreditCards.CardNumber) 
FROM Encrypted

The syntax for decrypting cyphertext with a private key that's encrypted with the database master key is also straightforward:

UPDATE Encrypted SET CardNumber = 
   DecryptByAsymKey(AsymKey_ID('dboAsymKey'), 
   CardNumber) 
FROM Encrypted

If you supply a private key password, you must include it as an nvarchar literal or variable in the decryption expression:

UPDATE Encrypted SET CardNumber = 
   DecryptByAsymKey(AsymKey_ID('dboAsymKey'),
   CardNumber, N'Abracadabra!1234567890$') 
FROM Encrypted;

Longer key lengths, which increase security, have only a minor effect on encryption performance, but increase decryption time dramatically (see Table 1). The shortest RSA key length—512 bytes—decrypts 16-digit credit card numbers at less than 100 rows/second.

Gain Performance with Symmetric Keys
Symmetric keys provide the best compromise between data security and encryption/decryption performance, which is one of the reasons that the Yukon developers chose the symmetric-key method for encrypting service and database master keys. Encryption with a Triple-DES or Advanced Encryption Standard (AES) symmetric key is about twice as fast—and decryption is an order of magnitude faster—than with a 512-byte RSA asymmetric key. The only drawback to user-assigned symmetric keys is the need to open the key for encryption and close it when it's no longer required:

OPEN SYMMETRIC KEY dboSymKey 
   DECRYPTION BY CERTIFICATE dboCert; 
UPDATE Encrypted SET Encrypted.CardNumber = 
   EncryptByKey(Key_GUID('dboSymKey'), 
   CreditCards.CardNumber) 
FROM Encrypted INNER JOIN CreditCards 
   ON Encrypted.CustomerID = 
   CreditCards.ReferenceID; 
CLOSE SYMMETRIC KEY dboSymKey;

You must execute similar instructions for symmetric-key decryption:

OPEN SYMMETRIC KEY dboSymKey 
   DECRYPTION BY CERTIFICATE dboCert; 
UPDATE Encrypted SET CardNumber = 
   DecryptByKey(CardNumber) 
FROM Encrypted; 
CLOSE SYMMETRIC KEY dboSymKey;

Note that the DecryptByKey function for symmetric keys requires a ciphertext argument only. The OPEN SYMMETRIC KEY parameter specifies the key ID, and the DECRYPTION BY CERTIFICATE parameter supplies the certificate ID.

You face a confidentiality issue with symmetric keys encrypted by the database master key because it allows members of the sysadmin group to impersonate certificate and key owners. Any user with IMPERSONATE ON LOGIN or IMPERSONATE ON USER privileges can execute an EXECUTE AS LOGIN = 'CertOrKeyOwner' or EXECUTE AS USER = 'CertOrKeyOwner' statement and decrypt ciphertext that's been encrypted with a key, certificate, or both owned by the user corresponding to the LOGIN name. For example, assume the AUTHORIZATION parameters assign this certificate and key to the MastAdmin login and user:

CREATE CERTIFICATE MastAdminCert 
   AUTHORIZATION MastAdmin 
   WITH SUBJECT = 'Certificate for MastAdmin';
CREATE SYMMETRIC KEY MastAdminSymKey 
   AUTHORIZATION MastAdmin 
   WITH ALGORITHM = TRIPLE_DES 
   ENCRYPTION BY CERTIFICATE 
      MastAdminCert;

Next, the MastAdmin user logs in and encrypts the plaintext CardNumber values with this batch:

OPEN SYMMETRIC KEY MastAdminSymKey 
   DECRYPTION BY CERTIFICATE 
      MastAdminCert; 
UPDATE Encrypted SET Encrypted.CardNumber = 
   EncryptByKey(Key_GUID('MastAdminSymKey'), 
      CreditCards.CardNumber) 
FROM Encrypted INNER JOIN CreditCards 
   ON Encrypted.CustomerID = 
   CreditCards.ReferenceID 
WHERE Encrypted.Issuer = 'MAST'; 
CLOSE SYMMETRIC KEY MastAdminSymKey;

Now assume the MastAdmin user closes the connection and logs off, believing the data to be safe from decryption by any other user.

A member of the sysadmin group (sa for this example) or a user with IMPERSONATE ON USER privileges then logs on and executes this instruction:

EXECUTE AS USER = 'MastAdmin';
OPEN SYMMETRIC KEY MastAdminSymKey 
   DECRYPTION BY CERTIFICATE 
      MastAdminCert; 
UPDATE Encrypted SET CardNumber = 
   DecryptByKey(CardNumber) 
FROM Encrypted 
WHERE Issuer = 'MAST'; 
CLOSE SYMMETRIC KEY MastAdminSymKey; 
REVERT;

Any sysadmin user can decrypt all cleartext encrypted by MastAdmin. You can use the Encryption.sln sample project to execute the preceding batches with cyphertext encrypted by all four added users: VisaAdmin, MastAdmin, AmexAdmin, and DiscAdmin. Mark the Symmetric Keys Only checkbox, click on the Encrypt All Card Numbers by User/Issuer button, and then click on the Decrypt All Card Numbers by sysadmin User button with the Decrypt All Cards as SA checkbox marked (see Figure 4).

Another pair of encryption-related functions that haven't received much attention are SignByCert(Certificate_ID, clear_text [password]), which returns a 128-byte varbinary digital signature, and VerifySignedByCert(Certificate_ID, clear_text, signature), which returns a boolean (bit) value of 1 if the current clear_text value generates the original digital signature. For example, these expressions return 1 and 0 because the first two @Cleartext values are identical, while the third differs:

DECLARE @ClearText as varchar(8000); 
DECLARE @Signature as varbinary(128); 
SET @ClearText = 'This is some clear text to encrypt';
SET @Signature = SignByCert(Cert_ID('dboCert'), 
   @ClearText);
SET @ClearText = 'This is some clear text to encrypt';
--Following returns 1
SELECT VerifySignedByCert(Cert_ID('dboCert'), 
   @ClearText, @Signature) AS VerifySig
SET @ClearText = 'This is some clear text to test';
--Following returns 0
SELECT VerifySignedByCert(Cert_ID('dboCert'), 
   @ClearText, @Signature) AS VerifySig

You might find these two digital signing functions useful for determining whether the original plaintext value has been modified during encryption/decryption or in any subsequent process without updating the stored @Signature value. The digital signature ensures the identity of the encryptor and the integrity of the decrypted content to authorized data users, which is required for full Sarbanes-Oxley Act conformance (see Additional Resources).

SQL Server 2005's native encryption functions are similar in capability to those offered by .NET Framework 2.0's System.Security.Cryptography namespace, but are much easier to implement than with managed code because the database engine handles native key management and storage duties. Brian Fonseca quoted Microsoft's Tom Rizzo in the May 25, 2004 issue of eWeek magazine: "We're trying to build the Ferrari of data encryption. Customers want to encrypt data. They know all the caveats that come with it—things take a bit longer to run. But they're willing to pay the taxes for just the extra security they get with it" (see Additional Resources). But don't expect a driver's manual; Books Online documentation and samples for the new encryption and decryption functions range from skimpy to missing in the April CTP drop. You can expect improved encryption docs in later SQL Server 2005 CTPs.

Whether information security or privacy officers and DBAs classify these new SQL Server 2005 encryption features as Ferrari-class remains to be seen, but my tests illustrate that symmetric encryption/decryption operations are easy to implement and speedy in execution. The ability to replace SQL Server 2000's third-party data encryption add-ins with native functions that run in the database engine's process, rather than third-party add-ins, is sure to free up memory and CPU cycles. The other major benefit of SQL Server 2005's native encryption approach is simplified key management within the database engine.

Don't let data thieves or unauthorized insiders filch your plaintext sensitive data. If you're storing third-party personal information, download the Encryption.sln sample project and give SQL Server 2005's new encryption functions a test drive. You'll find that it's easy to adopt the encryption/decryption techniques used by the sample project to your confidential data structures.

About the Author
Roger Jennings is an independent XML Web service and database developer and author. He's also a Visual Studio Magazine contributing editor and FTPOnline columnist, and manages the OakLeaf Systems XML Web Services demonstration site and Weblog. His Code of Federal Regulations Web services won Microsoft's 2002 .NET Best Horizontal Solution Award. Reach him at [email protected].