Storing SSN/CC data

Question

I read through quite a few similar questions here on SO but none were in quite the same situation as I am.

Previously, user enters in a ton of info including SSN, Spouse SSN, and CC data. When the user completed the process, the info was pushed on PDFs, zipped up (which then got encrypted), and then FTPed back into our server. We saved everything in the DB except SSNs and CC, which were erased when the session died.

Now, we need to save that info the database as well for some cases where after User A is done, User B needs to come in and sign off on the forms. After User B is done, the files are created and SSNs/CC data deleted. This means that data has to live in our DB from a few minutes to possibly a month. There is a set expiration date where I wipe out the data from the DB and make them start over. Note: I am not using the CC data to actually make a charge so I can't hand it off to a third party like Authorize.net or Paypal.

With that explained, I need to know the best way to encrypt this stuff and protect it. I'm torn between doing AES in my code using the user's GUID as the key or just SQL Server 2005 column encrypting and limiting the decrypt function to the web user.

I like AES because it keeps the few people who have DB access from using the web user's password to grab all the CC data. They would have access to the source code and could replicate the decrypt method but at least that's a little harder than just running some queries.

Unfortunately, I don't have time push through a way of not storing the CC data but I have some ideas for the next release. I have to make a choice and implement the encryption this week.

Answer 1

Have you already reviewed the Payment Card Industry requirements?

Using the user's GUID as the AES key would not seem to be compliant.

Answer 2

I do not understand how using AES encryption in your application code with the users GUID as the symmetric key will protect the data from being decrypted by people with database access. In most systems wouldn't the user id GUID also be stored in the database in clear text? If so then anyone with access to the database will be able to decrypt any data by passing it through the equivalent decryption function.

Depending on how your application server connects to the database server the built in column encryption functionality in SQL Server should be a good solution. If you use integrated authentication you can avoid having a clear text password for the application user. By using the access controls available to secure your encryption keys you can set them up so that no other user (and optionally not even the DBA) can arbitrarily decrypt the data from the database.

In June 2008 I gave a presentation on using the encryption functionality in SQL Server 2005 and 2008 that is available here

Here is sample code from that presentation that gives an overview of how to accomplish this so that only the appropriate users can view the decrypted data:

-- create the master key for the database, this will be used to encrypt every other key in the DB and is in turn encrypted by the service master key

CREATE MASTER KEY ENCRYPTION BY password = 'MasterKey1$'

-- create certificates for the user to be used to protect its own symmetric keys -- symmetric keys are created to encrypt data because they are faster and do not have inherent data size limitations based on key size

CREATE CERTIFICATE data_cert AUTHORIZATION data WITH SUBJECT = 'Data Cert'

-- note that you can also use the ENCRYPTION BY PASSWORD option here to prevent a DBA that does not know the password from opening the certificate and thus being unable to decrypt the data

-- create symmetric keys for each user to protect their data -- note if running SQL Server on XP the AES algorithms are not available, you must use 3DES

CREATE SYMMETRIC KEY data_key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE data_cert

-- note you can also use ENCRYPTION BY PASSWORD here as well to prevent a DBA who does not know the password from opening this key

-- grant permissions to the symmetric keys so that only the correct user can access them

GRANT VIEW DEFINITION ON SYMMETRIC KEY::data_key TO [DOMAIN\ApplicationServiceAccount]

This gives you a symmetric key that can only be opened by the appropriate user to use to encrypt/decrypt the data in the tables. You also gain the benefits of the cryptography best practices functionality in the database engine such as each cell being encrypted with a unique initialization vector as well as the key management functionality in SQL Server that allows you to easily change your keys regularly.