SQL Server Data Encryption: How to encrypt a column such that DBAs cannot decrypt sensitive data.

Question

Hi,

We need to store sensitive credit card information in our SQL server database. We would like to encrypt the credit card number column.

Is there any way to encrypt a column such that the dba users cannot decrypt the numbers and only designated applications (users ) can extract the clear text?

Thank you,

Answer 1

AFAIK,you'll need to encrypt/decrypt in the application layer is you need to prevent DBAs from decrypting data.  This "encrypt early, decrypt late" pattern is also more secure since data are encrypted both in motion and at rest.

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Dan, Is there any new feature in SQL 08R2 that help me to do this in SQL server? I know SQL server encrypts using a key. Is there any way to deny DBAs access to a particular key ?

Thank you,

Answer 3

DBAs will have access to the key, although you could protect the key with a password that is unknown to them.  The gotcha is that the application will need to specify the password when openning the symmetric key, which could be sniffed when transferred over the wire.

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Thanks Dan,

One last question: Is it possible to search encrypted columns using where clause?

Answer 5

One method to find an exact match is to store a hash of the clear text value.  Yoo won't be able to search with LIKE due to the salt.  In the WHERE clause, check both the hash (an indexed column) and the decrpted value.  For example:

WHERE
	CreditCardNumberHash = HASHBYTES('MD5', @CreditCardNumber)
	AND @CreditCardNumber = DecryptByKey(EncryptedCreditCardNumber);

 

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

How come he WHERE clause has two condition? either of conditions should handle the exact search. Is that correct?

I mean either:

WHERE                 CreditCardNumberHash = HASHBYTES('MD5', @CreditCardNumber

Or

WHERE @CreditCardNumber = DecryptByKey(EncryptedCreditCardNumber);

Should do the trick right?

Answer 7

There are two reasons I suggest checking both the hash and clear text value.  Checking only the decrypted value will require that all credit card numbers in the table be scanned and decrypted because the same credit card number will have different encrypted values.  This has obvious performance implications.

Checking only the hash value against an indexed column greatly improves search performance.  There is however the possibility, albeit very unlikely, that different clear values could yield the same hash value.  Checking both the decrypted value and hash protects against such collisions and inadvertent data disclosure.

I didn't mention it earlier but another technique is to deliberately truncate the hash value (and increase the likelihood of collisions) in order to thwart guessing of clear text values against the hash.  See the Cryptography in SQL Server method 3 topic in http://msdn.microsoft.com/en-us/library/cc837966(v=sql.100).aspx for details.

 

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/