Nothing To See Here

by Apr 4, 2016

In a galaxy far, far, far away a developer of a third party application somewhere in the Hoth system had to implement cell level encryption for sensitive data.  Their weapon of choice was to do this utilizing C# code within SQL Common Language Runtime (CLR).  Though the galaxy is full of other alternatives, this choice was not a bad one.  CLR can be a robust tool to handle such things if needed.

Enter stage left.  The password stored in a table.  In clear text.  

While it is very good that the data was encrypted using a solid encryption methodology, the keys to the kingdom where left in plain sight.  If you follow any known white hacker, you’ll probably know right off that leaving any bread crumbs around makes it easier to crack encrypted values.

The only saving grace is that fact that the table that holds the password is actually in a separate database; one that is controlled by the Rebellion.  Armed with this knowledge, we can trick the application by using a view instead of a table in conjunction with a built-in SQL function, DecryptByAutoCert.  Books Online tells us that this function,

Decrypts by using a symmetric key that is automatically decrypted with a certificate.”

 Essentially, this means that if you have a symmetric key encrypted using a certificate, this function will automatically decrypt the values without any additional input.

So the process would look something like this:

  1. Add a VARBINARY column to the existing table.
  2. Create a database master key (if one doesn’t exist)
  3. Create a certificate using the database master key
  4. Create a new symmetric key using the certificate.  This will be used to encrypt the actual password.
  5. Rename the existing table to something else, like dbo.NothingToSeeHere
  6. Create a new view using the function referencing the newly renamed table
  7. Test. Test. Test. Test. (Did I mention test?)

First, let’s look at the table structure as it stands.  This is just an example, no real passwords were hurt in the making of this post.

We will create the encryption parts that will be used to further protect the information.


We will create the encryption parts that will be used to further protect the information.

Screen Shot 2016-04-04 at 2.24.35 PM

From below we see that the certificate and corresponding symmetric key were created.


Now that those are present, we can add in a new VARBINARY(128) column.  This new column will be used to store the encrypted password.

Screen Shot 2016-04-04 at 2.25.29 PM

Using the ENCRYPTBYKEY function, it will do the heavy lifting of encrypting the data.


Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application.  The application doesn’t know if it’s calling a table or a view so that’s why this works.

Screen Shot 2016-04-04 at 2.26.49 PM

You can see below that the view returns the decrypted value.


The password is now fully encrypted utilizing SQL Server encryption.  Still, in order to use the view properly, the database users would need to be granted:

  • VIEW DEFINITION on the symmetric key
  • CONTROL on the certificate

This further secures the information allowing you to add in more granular control on who can decrypt the data.

As a side note,  in the event that this database is captured from the R2-D2 unit, the encrypted value won’t be able to be decrypted by the Empire until the database master key is regenerated with the new corresponding service account master key of the new instance.

Of course, you would have to test this very thoroughly in your environment to ensure that nothing breaks.