SQL Server 2008 Encryption / Decryption How and why multiple encryption within a column does not work?
Posted on 2009-07-13
Hello experts how are you all doing today?
I have a question which I would like to understand why what happens does happens.
Ok let me be clear on this one.
I have a sample table which I'm testing out its encryption / decryption functionality provided within SQL Server 2008.
So far I have been able to encrypt / decrypt the data. Create master key / certificates / symmetric keys as I want to.
At first we decided to have only one table to update within one column (example : SSN)
let say if the user decided to put this
SET SSNUM= EncryptByKey(Key_GUID('SampleACN_Key_01'), SSNUM)
To my understanding this updates the column to itself.
Which works lovely the first time around if we have a new table newly to import all data to it.
Now if the user decides to run this script over multiple times when you decrypt the data you usually get a blank column with no displayed data.
Obviously we are not going to do this since this is merely a test and I'm still learning the whole mechanics of encrypt/decrypt within SQL server.
I just want to have a better understanding as to why I get a blank when I try to decrypt.
NOTE : remember this only happens if I encrypt the columns multiple times.
Is it because the same string gets encrypted over and over again so when I try to decrypt I get nothing back...is there even a solution to this and if so how? and why?
Again guys this is merely to understand and for my own comprehension as to why this happens.
I'm heading a different route when it comes to the update such as having a temp table and updating the values from there.
Thanks again guys.
Any suggestion would be welcome for this.