Home / server / Questions / 139331
In Process
Paul
Asked: 2010-05-07 11:32:06 +0800 CST

Decrypt column in SQL 2008

  • 772

I need to decrypt a column in a table that has previously been encrypted at application level.

The algorithm is DES at 192 bits and block size = 64.

I have the password but DecryptByPassPhrase doesn't seem to work.

sql-server sql-server-2008 encryption

1 Answers

  1. You need the original key. Most likely the encryption password was used to encrypt the key, not the data. DECRYPTBYPASSPHRASE can be used to decrypt something encrypted by ENCRYPTBYPASSPHRASE. To decrypt something encrypted by ENCRYPTBYKEY, you use DECRYPTBYKEY. The password is used to open the key:

    OPEN SYMMETRIC KEY <keyname> DECRYPTION BY PASSWORD = '...';

    If the encryption key was encrypted by a certificate and the certificate was encrypted by a password (As is often the case) then you obviously need open the certificate using the password. Whoever designed the original encryption scheme should be able to guide you through the proper decryption key hierarchy.

    • 1