x

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.
more ▼

asked May 09, 2012 at 05:18 PM in Default

dakers gravatar image

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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 06:11 PM KenJ
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered May 18, 2012 at 07:46 PM

Steve Jones Editor gravatar image

Steve Jones Editor ♦♦
361 3

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered May 18, 2012 at 08:00 PM

dakers gravatar image

dakers
10 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943
x38
x15

asked: May 09, 2012 at 05:18 PM

Seen: 1539 times

Last Updated: May 18, 2012 at 08:00 PM