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.

asked May 09 '12 at 05:18 PM in Default

dakers
10 ● 1 ● 1 ● 1

Is it encrypted by passphrase, key, or cert? can the application decrypt the data when it uses the new server?

May 09 '12 at 05:39 PM KenJ

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.

May 09 '12 at 05:47 PM dakers

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.

May 09 '12 at 06:00 PM KenJ

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.

May 09 '12 at 06:06 PM dakers

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.

May 09 '12 at 06:11 PM KenJ

(comments are locked)

2 answers:

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