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

USE [DatabaseNameGoesHere]
GO
-- add in a database master key with a strong password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!_G03s_H3r3';
GO
-- create a certificate
CREATE CERTIFICATE Encryption_Test
WITH SUBJECT = 'Test Certificate',
EXPIRY_DATE = '20991231';
GO
-- create a symmetric key using the above cert
CREATE SYMMETRIC KEY DBEncryption
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Encryption_Test;
GO

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.

-- open the key
OPEN SYMMETRIC KEY DBEncryption
DECRYPTION BY CERTIFICATE Encryption_Test;
GO
-- add a varbinary column to the table first
ALTER TABLE dbo.Table1
ADD Encrypted VARBINARY(200)
GO
--update the altered table with the encrypted value using the symmetric key
UPDATE dbo.NoNo
SET Encrypted = ENCRYPTBYKEY(KEY_GUID('DBEncryption'),[password]) --clear_text_password is the passwd column in Table1
GO

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.

-
-- close the key
CLOSE SYMMETRIC KEY DBEncryption
GO
-- rename the table1 to table1_Updated
-- create a view called Table1 so that the view matches the old table name; this allows this to be seamless to the application
EXEC sp_rename 'dbo.NoNo', 'dbo.NoNo_Maybe'
GO
CREATE VIEW dbo.NoNo -- same name as the former table
AS
SELECT CONVERT(NVARCHAR(100),DECRYPTBYKEYAUTOCERT(CERT_ID('Encryption_Test'),NULL,encrypted)) AS 'NowYouSeeMe'
FROM dbo.NoNo_Maybe
GO
-- Voila!
SELECT * FROM dbo.NoNo
GO

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.

This isn’t the data you are looking for

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.

© 2016, John Morehouse. All rights reserved.