question

dakers avatar image
dakers 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.
sql-server-2005encryptionpassword
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is it encrypted by passphrase, key, or cert? can the application decrypt the data when it uses the new server?
0 Likes 0 ·
Passphrase, I think, altho, in the process of the application upgrade last year, I saved a file to the server desktop named named MRIMasterKey which I don't remember why. No file extension on it.
0 Likes 0 ·
if it's passphrase, the application that writes the data will have a copy that it uses to encrypt the data. you should be able to get it from there.
0 Likes 0 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
Steve Jones Editor avatar image
Steve Jones Editor answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dakers avatar image
dakers answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.