SQL Server 2008 Encryption / Decryption How and why multiple encryption within a column does not work?

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

UPDATE Sample1  
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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here's a test for you to perform.
  1. Create a new table that has an Identity column and a column for "SSN" values that is a VarChar(8000), and another for the NeverEncryptedSSNS" values.  Now enter some data into the table (entering the same value in both SSN columns) and run a query to extract the data so that you can save it, for instance, to an Excel spreadsheet.
  2. Now, run your UPDATE query to encrypt just the SSN column and then run a SELECT query to once again extract the data but do not decrypt the SSN column data.  Again, save it to an Excel spreadsheet.
  3. Now repeat step 2 above about 3 times (only encrypting the SSN column and still not decrypting before saving tot he Excel Spreadsheet pages).
Now, look at the encrypted SSN columns.  I am going to guess that they increase in size each time you encrypt them.  Now, if they do and if you have assigned, for instance, either a CHAR(09) or a VarChar(09) or even a VarChar(20) to the SSN column that you then encrypt, the truncation of the string may result in a value that cannot be decrypted and, in fact, cannot even be recovered in any manner.
What is the data type of the SSNUM column? EncryptByKey returns binary data so SSNUM should generally be BINARY or VARBINARY too. Also the encripted value will be larger than the value being encrypted so make sure your column has sufficient space. VARBINARY(MAX) is best to use for encrypted columns.

So if the column is already a VARBINARY(MAX), what do you mean by "blank" and how are you displaying the data?
saruman101Author Commented:
Hey guys thank for the reply.
As for the varbinary max that is already implemented in my column.
And as for the display of the information here it is

CONVERT(varchar, DecryptByKey(SSNUM))   AS 'Decrypted Number'

Remember guys this already works. I can encrypt and decrypt.
I'm doing this to understand the whole mechanics of this much better.
Again the problem is this. If I decide to update the column multiple times..let say by mistake or simply an error such as
UPDATE Sample1  
SET SSNUM= EncryptByKey(Key_GUID('SampleACN_Key_01'), SSNUM)

Without a where clause to specify the column.
If that statement above gets run multiple times..when you try to decrypt the data then you get a blank.
I was thinking could it be because since it gets encrypted multiple times the string becomes too long...not too long as it can't fit within the column since it already has a Max value within, but too long for the decryption function to even work properly.

Try it and make a small table with any column and provide it with a varbinary column. Use the code above with your table and see what I mean by the output results.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

While I sort of understand your desire to conduct this experiement, I also sort of don't.  The query that you presented to update the column to an encrypted value should never, ever be allowed to be used except under very controlled circumstances (i.e. by a DBA who knows what he/she is doing).  Once the data is encrypted the initial time (as in, when the data is bulk loaded when the database is first being populated), the query should not be needed ever again.
If you are concerned about someone accidentally updating the world instead of a single row, then you should be relying on stored procedures to perform the row updates (which I would advise anyway).
As an aside, you should also be aware that any column that is encrypted becomes a column that is not a good candidate for an index, any sort of key, or for a search in a WHERE clause.  If the encryption is any good at all, the results become, essentially, random numbers and any use of them as an index or key will tend to result in table or index scans.
saruman101Author Commented:
8080 Diver you are correct. The syntax it self would never be used by anyone who has no clue or right of usage to begin with only a DBA would have the responsibilities for this.
Im clearly experimenting as to why this actually occurs. As I said before in case some one decide to run this multiple times by mistake...or on purpose this output would occur which would leave the data obsolete and useless.

If you can try to run the data and see what I mean about the output. I'm just trying to understand why the Decryptfunction does not work after multiple encryption occurs within the same column.
If the statement
 UPDATE Sample1  
SET SSNUM= EncryptByKey(Key_GUID('SampleACN_Key_01'), SSNUM)
runs multiple times when you try to decrypt you get blank output for your return.

Again it is an interesting experiment since this could happen to anyone...or at least some people who don't have proper procedures to implement in place from this happening multiple times.
If you encrypt twice then the result of this expression:

CONVERT(varchar, DecryptByKey(SSNUM))

will probably return unprintable characters. This is correct because the decrypted value is really binary and you can't expect it to cast to meaningful characters. A correct decryption for a doubly-encrypted value would be:

CONVERT(varchar, DecryptByKey(DecryptByKey(SSNUM)))

In the APL language, you can obtain the determinant of a matrix by using what is called a "Circle dot product" (based on the "over punch" characters that used to be required to represent the symbol).  In fact, the "Circle Dot" was a modifier that could be applied consistently to virtually any math operator . . . however, only a few actually made sense and were defined.  
Onw of my coworkers who was getting a PhD in Math wrote a program in APL to dosome major calculations using matrices.  For whatever reason, there was a typographical error that introduced some serious errors in his calculations . . . instead of a Circle Dot Product, the Circle Dot operator was applied to a square root calculation against the matrix.  Now, in the first place, thre is some question as to exactly what it would mean to take the square root of a matrix but, more importantly, the results of using a Square Root calculation in place of a multiplication calculation in the manipulations of that would be equivalent to getting the determinant of a matrix are totally undefined.  Interestingly enough, though, they are somehow mathematically consistent . . . in other words, given the same matrix, you get the same answer.
So, since my coworker was looking for an obscure, toatlly useless mathematical topic for his PhD thesis, he used this typo to his advantage.  He wrote his doctoral thesis on "The Circle Dot Square root Calculation in APL".
Now, I know, you are wondering why in the world I am telling you this and what the heck it has to do with the subject at hand, aren't you? ;-)  Well, the mutltiple encryption of a column in the manner you have described is the SQL equivalent to the APL Circle Dot Square Root.  It is probably consistent but it is also of virtually no use.  
If you were considering using a double/triple/whatever encryption as a means of achieving greater security, then you should be aware of the overhead involved in the process as well as the exhibited issueof apparently not being able to successfully retrieve data from a multiply encrypted column.
However, for the sake of argument, have you tried multiple decryptions instead of trying to display the results after on decryption?  (Although, I am betting that, somewhere in the process of multiple encryptions, there is going to be a truncation issue that will invalidate the data in the column. ;-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
saruman101Author Commented:
Hey 8080 driver
I was going to say your explanation is rock solid. Based on this experiment I was able to understand more as to why the multiple encryption / decryption within one column can cause the blank output as well as the truncation within the column.
Again this to me was a very interesting subject as well as testing environment since I like to tinker around with things. Personally this is my first time dealing with encryption at this level of data so I decide to play around with an experiment and tried to understand the results as presented to me.

Thanks again and keep up the good work.
I have played, off and on, with encryption since about 1969 or 1970 for one reason or another.  It is, indeed, a very interesting topic and there are some wonderfully intriguing aspects to it.  I've explored back-pack and verious other systems and once even created a reasonably secure encryption technique for some data transmissions (it involved multiple substitutions for single characters based upon normal character counts and used a pre compression of the data before making the substitutions ;-).  Some years back, I almost bought a book that was the entirety of the (then) PGP code.  It "only" cost $70 and was only about 4" or 5" thick. ;-)  
So, you see, I, too, have a bent toward this sort of investigation. ;-)
saruman101Author Commented:
Thanks for the help. I can see you truly love your work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.