[dba-SQLServer] SQL Server Encrypted field

jwcolby jwcolby at colbyconsulting.com
Wed Jun 6 12:08:42 CDT 2012


I need to store sensitive data in specific fields of specific tables.  I find things like:

http://msdn.microsoft.com/en-us/library/ms179331.aspx

Which discusses creating a certificate etc.  Hmm... what happens if the database is backed up?  What 
happens if I need to move the database?

And of course my favorite SQL guy (Pinal Dave):

http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

In the end however what I want do (in this case) is to allow specific information to be encrypted / 
decrypted on a user specific basis, i.e. based on something user specific.

Assume that users need to store their own Email Address, username and password in my database and 
then use that to send email "on their behalf" from my system.  The database is used for generating 
Community Volunteer passes, and when the pass is created it is printed to PDF, attached to an email 
and mailed to one or more email address at a specific prison.  I have created a new GMail account 
with a username and password but it would be nice to allow each user to enter their own email 
address / username / password to send from so that if there are issues and the prison replies to the 
email, it gets back to them directly.  Using my current system it would come back to my general 
address.  Of course I can do a "do not respond to this email" kind of thing but I have already been 
asked if they can get responses.

Obviously if I am going to store a user's email address, username and password it has to be 
encrypted, but furthermore it has to be retrievable only by that user.

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it



More information about the dba-SQLServer mailing list