0

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?

  • What do you want to Decrypt (Password or Stored Procedure etc)? If it is password Decryption: stackoverflow.com/questions/173329/… – CR241 Jun 15 '17 at 18:41
  • It's impossible to tell if you can decrypt something encrypted by some black-box application. – Max Vernon Jun 15 '17 at 18:56
  • I'm trying to decrypt a bank account number. The encryption is done by Guidewire application before it is stored in SQL Server. – Vikas Singh Jun 16 '17 at 9:02
2

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.

  • Do you have any reference to AesManaged in sql – Harshal Bulsara Jan 2 '18 at 9:44
  • @HarshalBulsara Sorry, but no, I do not have a working example of a SQLCLR object that implements the AesManaged Class. – Solomon Rutzky Jan 2 '18 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.