March 26, 2021 at 2:52 pm
I have had a process to move a production database to a test server going for a few years now. The developers wanted a second copy of the same database created so we setup a new database and then restore the same backup to the new database. After the restore sometimes it works fine and other times we have to run the following to get things working.
OPEN MASTER KEY DECRYPTION BY PASSWORD ='password';
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password' ;
Any ideas why it works sometimes and why it won't others? Like yesterday it worked fine. Today it is not working and because of an AD policy change we are getting the error that the password doesn't comply with the policy, Though the query worked 2 days ago and the password policy was changed back in February.
March 27, 2021 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 30, 2021 at 7:37 pm
My guess (could be way out to lunch) is that your scripts are slightly different for the restore. What I mean is if you drop the database and recreate it, then things will likely need you to open the master key by password and regenerate it. On the other hand, if you restore over top, I would expect that the master key could be used to decrypt the data without regenerating it.
The above is just a guess mind you; I have not tested it.
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.
March 30, 2021 at 8:11 pm
Actually copied and pasted the script from one Agent job to another and modified the Database being restored into and the restores are using the same backup file to restore from. It is one of those things that it works maybe 75% of the time but the 25% of the time it doesn't work is driving me crazy and with the change in password policy recently the devs are adamant that the password being used can't change and they want to know "why" it doesn't work 25% of the time.
March 30, 2021 at 10:06 pm
That is a new one for me. If it is the exact same script, I would expect it to have the exact same results each time.
Any chance anything else has changed? SP's or CU's for example. Or reusing an old session vs using a new session?
I am thinking there is SOME process change with that 25% and it MAY seem minor (different SSMS version, different physical machine or VM restoring from, restoring from FULL vs FULL+Differential+logs). I could be mistaken and it could be some weird network gremlins in your system that are doing it or a full moon makes it behave differently, but I would start by ruling out every little detail that I could.
Like if I restore at 3:00 PM, it uses a full, a differential backup, and 3 log backups. I generate the script from Machine A and restore it using SSMS version 18.8 on Machine A. This results in one of the 75% times. Next time I restore at 9:00 AM, it uses a full backup and 9 log backups. I generate the script from Machine B and restore it using SSMS version 17.2 on Machine B. This results in one of the 25% times.
Here I can slowly work down the differences by installing SSMS 18.8 on Machine B. If that changes it to one of the 75% times, I know it is a bug/feature/configuration option in SSMS 17.2 that is different in 18.8.
Now if nothing seems to be changing on the client side, I would start checking the SQL side. Is the restore from instance A to instance B every time? What I am getting at here is if you restore from A to A with a new database name and it works, but when restoring from A to B you get the password problem, it could be version differences or configuration differences between systems A and B.
Now, lastly, the permissions. Are you restoring as the same user every time? I am wondering if sometimes it is using windows authentication, sometimes it is sa or some other SQL account.
Basically, I am guessing that SOMETHING changes between the 75% and the 25%. It may be something seemingly minor (like SSMS version) or it could be something more major (like restoring a SQL Server 2008 backup onto a SQL Server 2019 instance gives you your 25% failure, but restoring from 2008 to 2008 gives the 75% success).
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply