Access key in DB1 from DB2

I have a SQL 2005 server with two databases. DB1 is the main database and DB2 holds summarized data from DB1 for reporting. The report person has written a Stored Proc that is in DB2 and does this..

OPEN SYMMETRIC KEY DB1Key DECRYPTION BY CERTIFICATE DB1KeyCert

SELECT @.Value= cast(DecryptByKey(field) as VARCHAR)) FROM DB1.Schema.Table

CLOSE SYMMETRIC KEY DB1Key

Of course this does not work since the sp is in DB2 and the key is in DB1. I have tried fully qualifing the key (DB1.DB1Key) and such, but no luck.

Can this be done? Can I access and decrypt data in DB1 from DB2? And if so, how?

Thanks!!!

Jim

I would recommend reading Laurentiu’s article on this topic:

http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx

If you have further questions, please feel free to ask either in this forum or directly on Laurentiu’s blog, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

That is helpful, but the symmetric key was not created with a source so I can not recreate it. To decrypt the data, drop and recreate the keys and then reencrypt the data would cause me a lot of headaches (the main database is used 24x7 and is covered by SLA's). Is there any way to just reference the key from DB1 in DB2?

Jim

|||

You have to be in DB1 to use the DB1 key. You cannot use an encryption key from another database.

Thanks

Laurentiu