Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2009-07-13
Medium Priority
Last Modified: 2012-05-07
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.

Question by:saruman101
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
LVL 22

Expert Comment

ID: 24841306
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.
LVL 22

Expert Comment

ID: 24841319
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?

Author Comment

ID: 24841485
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 22

Expert Comment

ID: 24841711
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.

Author Comment

ID: 24842005
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.
LVL 22

Expert Comment

ID: 24842821
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)))

LVL 22

Accepted Solution

8080_Diver earned 1000 total points
ID: 24843103
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. ;-)

Author Comment

ID: 24869578
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.
LVL 22

Expert Comment

ID: 24869665
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. ;-)

Author Closing Comment

ID: 31602884
Thanks for the help. I can see you truly love your work.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question