asked
Lost encryption password
Hi All, 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.
How would I accomplish that? The support staff at MRISoftware have told me to keep guessing for days now. Or perhaps call Microsoft possibly. I have called MS before and they helped with issues, but from looking at posts here I thought that someone might have knowledge of this issue.
Guessing could be a bit problematic, as you've noticed :) Check any configuration files the application uses for it. I've seen clients with applications that used stored procedures and had embedded the password inside the procedure (nothing like storing the key with the treasure). If you don't find the password in a configuration file, it could be in a stored procedure or compiled into the application, if it was custom built for you.
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
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
6 People are following this question.
