June 12, 2020 at 5:17 pm
When creating SSIS catalog, it asked to enter a password for the master key protection.
And then I also do a backup of master key something like below:
backup master key to file = 'c:\temp\RCTestInstKey'
encryption by password = 'LS2Setup!'
Does the two password mean the same thing?
If not:
I know the second password is used when we restore database to another instance.
How about the first password used for?
Thanks,
June 12, 2020 at 5:51 pm
No.
The password you use when you create the master key is used to open it if you break the hierarchy of allowing the Service Master Key to automatically open it.
The one in the BACKUP command is used to protect the file. They do not have to be the same.
June 12, 2020 at 5:57 pm
Thanks!
So the first one :
The password you use when you create the master key is used to open it if you break the hierarchy of allowing the Service Master Key to automatically open it.
So what SQL statement is used to recover it when in above situation?
June 15, 2020 at 2:44 pm
To recovery the master key, if the password is known, you run (the button for adding SQL is missing for me right now, sorry for the poor formatting):
OPEN MASTER KEY DECRYPTION BY PASSWORD='password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
Replace 'password' with the password you know.
If the password isn't known, you can still SET a master key with:
ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD='password'
BUT you will lose all objects encrypted by the master key so that is more of a "last resort" type option. I had to do that when a 3rd party vendor tool in an old version created a master key encrypted by password and had created a master key with a password then dropped the encryption by service master key. Then in a future update of the tool, it required you to set a master key password. Unfortunately, while doing an update to that version, it needs to open the master key with a password which only they knew and were not willing to tell us. Did some digging and they encrypted nothing with that master key, so I did a force regenerate (after taking a full backup just in case I was mistaken) and the tool has been working fine ever since.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 15, 2020 at 3:30 pm
If you run RESTORE MASTER KEY, you need the password from the BACKUP MASTER KEY statement in the "decryption by password" options.
The password used in the "encryption by" is what Brian noted.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply