Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Using AES Decryption

117 views
Skip to first unread message

John Cranston

unread,
Jan 6, 2010, 5:45:30 PM1/6/10
to
I am working with a data feed that is parsed and loaded into tables in a SQL
Server 2005 database. Certain fields will contain data encrypted using
Rijndael-256 (AES). I wanted to use the DecryptByKey t-sql command to
decrypt the data for processing.

I have successfully encrypted and decrypted using a generated key in the
database, but I do not know how to decrypt data that was encrypted
elsewhere. I was given the key for the encryption, but it is just a string.
I tried using this as password for a certifcate and encoded the sample
string provided by the other developer (that is creating the feed) and the
resulting encrypted data is not the same as was supplied.

Help.... please.

John


Michael Coles

unread,
Jan 6, 2010, 8:35:40 PM1/6/10
to
Hi John,

Let me give you my contradictory answers here:

Short answer: You can't. You can't decrypt data using built-in SQL Server
encryption functionality if it was encrypted using non-SQL Server
functionality elsewhere.

Longer answer: You can. If the data was encrypted using built-in SQL
Server encryption functionality elsewhere you can recreate symmetric keys or
import certificates from other server instances, which will allow you to
decrypt the data. There are a couple of little rules you need to follow to
make this happen though.

Even longer answer: You *might* be able to. If the data was encrypted
elsewhere using non-SQL Server encryption functionality; but it will be a
painful process (and it might not work even after all this):

1. You'll need to prepend the SALT value to the front (along with other
expected metadata like "version") before you submit it to SQL;
2. You'll need to ensure that the source encryption mechanism was using the
correct block cipher mode (CBC);
3. You'll need to generate the exact same encryption key that SQL Server
uses to encrypt your data, which might be possible if you use the same
algorithm SQL Server uses to generate encryption keys at the source (this
assumes that SQL Server uses a method like RFC 2898
http://msdn.microsoft.com/en-us/library/system.security.cryptography.rfc2898derivebytes.aspx
to derive the password, a big assumption);
4. You'll need to use DecryptByPassPhrase with the same key material used
at the source to generate a key;
5. ...Pray to a higher power.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"John Cranston" <jo...@axiomsw.com> wrote in message
news:%2313IRHy...@TK2MSFTNGP06.phx.gbl...

Michael Coles

unread,
Jan 6, 2010, 8:41:22 PM1/6/10
to
Oops, correction. EncryptByPassPhrase/DecryptByPassPhrase uses Triple-DES
not AES. So for my third answer you would have to use DecryptByKey using a
key created with KEY_SOURCE. But again, there's no guarantee that your
encryption key at the non-SQL source will match the encryption key generated
by SQL Server.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"John Cranston" <jo...@axiomsw.com> wrote in message
news:%2313IRHy...@TK2MSFTNGP06.phx.gbl...

John

unread,
Jan 7, 2010, 11:43:01 AM1/7/10
to
Thank you, Michael.

"Michael Coles" wrote:

> .
>

0 new messages