I have a little sql knowledge but am only dangerous. My problem is that we have an accounting sql2005 database that has a few tables with a few encrypted columns. (SS, bank account numbers, etc.)I need to move this to a new 2008r2 sql server. But cannot find (can't believe this) the encryption password. I have done a test move and yes the data is encrypted on the new server.
What can I do? Still have the production sql2005 server up and running.
Do a search for DecryptbyPassphrase in your objects. You might try SQL Search (from from Red Gate) to help. This might clue you into the passphrase.
If it's in a .NET application of some sort, you'd need to search the code there.
Have you also talked to the clients? When they access that data, what do they do? Perhaps they enter something?
I might look through the tables, perhaps it's stored there. Not a great design, but I've seen people do this. Or config files, as mentioned in the comments.
The last thing to do is look through your DMvs (sys.symmetic_keys or sys.asymmetric_keys) to see if you have keys in the database.
If you've really lost the key, or the password to it, you are stuck. The issue with encryption is there is not easy way to get this back. That's the idea of encryption. You could start a crack on the key, but not sure how easy that would be.
DecryptbyPassPhrase - http://msdn.microsoft.com/en-us/library/ms188910.aspx
answered May 18 '12 at 07:46 PM
Steve Jones Editor ♦♦
Humm, Well never mind. The answer was fairly simple. See below:
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password variable'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password variable' --Test password
answered May 18 '12 at 08:00 PM