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
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...
--
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" wrote:
> .
>