Thanks for the info.
I created the same Master key in test db that I used for the Prod db in March. I bounced the test db after recreating the master key, still returns NULL for the existing encrypted data. This is on my client's server.
I just tried to reproduce the scenario on our local server, we only have one SQL server 2005, so I restored our db to test db and it decrypted fine. But when I drop and recreate the same master key, the decrypt returns NULL for the existing encrypted values even after bouncing test db. I know incase production db crashes and we restore it to a different server and recreate the master key it should work. Am I missing something here?
Also, in our SQL 2005 server, we have different client's test databases and is it possible for these databases to have different master key for every database? I'm thinking the master key will be only one for all the databases in a SQL server.?
Please help. Thanks a lot again