August 11, 2008 at 7:57 am
Hi,
My company want me look inot encryption function on SQl2005 to encrypt SSn and password. Currently, both are varchar (128) column. I'm instructed to use certificate with SQL2K5 encryption. I had few questions and hope somecan share their experience on this.
1. what is the migarttion steps to encrypt the SSN and password column ?
2. Will there be new column introduced to move the current data to new encrypted data or the current column need change the data type ?
3. when application descrypt data, will it return 'celar text' data in varchar format or it still in varbinary and need another cast to do the comapre ?
4. anyone can give me some experience if they use this function
Thank you
August 11, 2008 at 10:07 am
We actually chose NOT to use SQL Server to encrypt/decrypt this data, but to put the encryption/decryption in the business layer because using SQL Server encryption means that the data is in plain text going across the wire. Thus, packet sniffing can see the data.
Now to address some of your questions as best I can:
1. Not sure what you mean exactly.
2. You will need new column(s) that should be varbinary. Once you have created the new column(s), loaded them with the encrypted data, and verified through decryption that everything is worker drop the unencrypted column(s).
3. Your decryption will return the data in clear text varchar/nvarchar.
4. I gave you my "experience" above. The only downside of our decision is that you will need to have, in our case, a .NET application, running our encryption dll in order to see the plain text, but when talking security is that really a downside? I mean I cannot write at least not easily a T-SQL statement to decrypt the data which means it's protected from the DBA's as well. I can do it in the CLR of course.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply