Decryption in SQL Server 2012

Question

I've encrypted texts stored in sql server 2012 which I want to decrypt using sql. AES 256 bit algorithm was used to encrypt the data and I've the key used to encrypt. This encryption was done in a third party application and not in SQL Server. Is there as way I can decrypt it?

Answer 1

You can use AES 256 in SQL Server, but that does not guarantee that you will be able to decrypt your data. If the "key" you have is a string used to create the key, then you might be able to use the built-in DECRYPTBYKEY function. I emphasized "might" since there is an optional second value that can be used to encrypt data (in SQL Server it is called the "authenticator"), and if it was used when encrypting the data, then it must also be used when decrypting.

The following example uses a plain text phrase as KEY_SOURCE and does not use an authenticator:

USE [tempdb];

-- DROP SYMMETRIC KEY [test_key];
CREATE SYMMETRIC KEY [test_key]
WITH ALGORITHM = AES_256,
     KEY_SOURCE = '{your "key"}'
ENCRYPTION BY PASSWORD = 'weak';

OPEN SYMMETRIC KEY [test_key]
   DECRYPTION BY PASSWORD = 'weak';

DECLARE @Encrypted VARBINARY(8000);
SET @Encrypted = ENCRYPTBYKEY(KEY_GUID(N'test_key'), 'test me!');  

SELECT @Encrypted AS [EncryptedValue],
       DECRYPTBYKEY(@Encrypted) [DecryptedBytes],
       CONVERT(VARCHAR(500), DECRYPTBYKEY(@Encrypted)) AS [DecryptedText];

CLOSE SYMMETRIC KEY [test_key];

Returns:

EncryptedValue                   DecryptedBytes        DecryptedText
0x00A6E1C5C3035A45BC33EEFA...    0x74657374206D6521    test me!

IF, on the other hand, you have the actual binary key itself, then that will not work with the built-in T-SQL functions. In this case you might be able to use SQLCLR in order to use the AesManaged Class in .NET which accepts a 128 bit (16 byte), 192 bit (24 byte), or 256 bit (32 byte) key. Of course, this requires a second value, the "initialization vector (IV)", and I don't know if you have that, and I haven't been able to get it to work when not supplying a value for it. And while it might not matter, there is a "Padding" property that has a few different possible values and it is always possible that Guidewire used a different setting than the default .NET setting, so it could require a bit of trial and error.