Depryting returns a NULL value | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Depryting returns a NULL value

<br />I find it weird when decrypting a column from a copied table in another database. Here’s the scenario:<br /><br />Instance1 has Database1 and Database2.<br />Database1 has Table1 with two columns encyrpted — Card Number and SS Number<br />Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.<br />Now, I tried coping this Table1 to Database2 using the SELECT command. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br /><br />I tried also the <br /><br />Here’s exactly what I did to create the encyprtion and decryption keys on the Database2:<br />– Create the master key encryption<br />CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myMasterPassword'<br /><br />– Create a symetric key<br />CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES<br /> ENCRYPTION BY Password=’myPassword’;<br />Go<br /><br />– Create Card Certificate<br />CREATE CERTIFICATE myCert WITH SUBJECT = ‘My Certificate on this Server’;<br />GO<br /><br />– Change symmetric key<br />OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = ‘myPassword’;<br /><br />– I then verified if the key is opened<br />SELECT * FROM sys.openkeys<br /><br /><br />Anything wrong with this? Thanks experts!
<br />I tried also the following:<br /><br />Backup the database1, restore it to another database instance on another SQL server 2005. Create certificate and symmetric keys, but it didnt work as well <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /> <br /><br />Any suggestions gurus?
Check the following.. http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
]]>