May 2, 2018 at 9:28 am
I am having a weird problem while testing a domain name change - I can't restore the encryption keys.
The long story:
We are changing our domain from A to B (not literally, but essentialyl that is what we are doing). To test the process, our IT department moved one of our test SQL servers to the new domain. This caused all of the service accounts to switch from A to B (in our case A\sqlacct_test changed to B\sqlacct_test). This unfortunately resulted in a random password being generated for it, but the service started up successfully.
My next step was to start up the SSRS configuration manager and restore the encryption keys we had backed up but it failed. And when it failed it locked up the configuration manager. It wasn't frozen, but none of the boxes were clickable and I was forced to hit the big red X (not task manager).
So, I thought I'd just use rskeymgmt.exe. The SSRS used to be SSRS 2008 but we upgraded it to 2016 and as a result, the 2008 rskeymgmt was still in place and was loading as the default. So I went to the 130\tools\binn folder to run .\rskeymgmt.exe -a -i <SQL Instance> -f <path to encryption key copied to the local disk in a folder that "everyone" has full control on to ensure there were no permission issues> -p <password for encryption key>" and I got a fun error: "The data is invalid. (Exception from HRESULT: 0x8007000D)". So my next thought, lets list the SSRS services with ".\rskeymgmt.exe -l -i <SQL instance>". Same error. Ok, maybe rskeymgmt is busted... lets try it with a non-SSRS instance and it tells me it can't find SSRS on that instance. So that looks good.
Getting a but stumped/worried about this migration, I decide to change the user account back to domain A\sqlacct_test and re-backup the encryption key just in case it was bad. It backs up successfully, so lets try restoring it JUST to be 100% sure it is valid. And it restores successfully. Both from the configuration manager and rskeymgmt.exe. So I know the key is good. Use configuration manager to change the service account to B\sqlacct_test and it fails to restore the key. rskeymgmt is giving me that "the data is invalid" message again.
Next thought, lets set B\sqlacct_test as sysadmin. Maybe it is database permissions. Nope, that makes no difference.
The SQL Instance is running as B\sqlacct_test, just SSRS seems to fail unless I run it as A\sqlacct_test and I am completely stumped as to what I am doing wrong. My understanding is that I should be able to restore the keys afterwards with no troubles but it just doesn't seem to be happy.
SSRS is version 13.0.4466.4 Standard Edition and the report server mode is Native. My current thoughts are that it is because A\sqlacct_test and B\sqlacct_test have the same SID that when it changes between them it is screwing something up. I did try changing it to a different A\ account (mine) and it worked as expected. I have not tried any other B\ account but my next testing steps are:
1 - change it from A\sqlacct_test to A\me
2 - change it from A\me to B\sqlacct_test
OR
1 - change it from A\sqlacct_test to B\me
2 - if step 1 works, change it from B\me to B\sqlacct_test
or am I missing something obvious? The service starts up no problem, it just can't decrypt any of the data which restoring the encryption keys should fix that issue.
My "last resort" method is going to be:
1 - change it from A\sqlacct_test to B\sqlacct_test
2 - delete the encryption keys (if it will let me... the "The data is invalid" error makes me think this may not work).
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.
May 2, 2018 at 2:51 pm
bmg002 - Wednesday, May 2, 2018 9:28 AMI am having a weird problem while testing a domain name change - I can't restore the encryption keys.The long story:
We are changing our domain from A to B (not literally, but essentialyl that is what we are doing). To test the process, our IT department moved one of our test SQL servers to the new domain. This caused all of the service accounts to switch from A to B (in our case A\sqlacct_test changed to B\sqlacct_test). This unfortunately resulted in a random password being generated for it, but the service started up successfully.
My next step was to start up the SSRS configuration manager and restore the encryption keys we had backed up but it failed. And when it failed it locked up the configuration manager. It wasn't frozen, but none of the boxes were clickable and I was forced to hit the big red X (not task manager).So, I thought I'd just use rskeymgmt.exe. The SSRS used to be SSRS 2008 but we upgraded it to 2016 and as a result, the 2008 rskeymgmt was still in place and was loading as the default. So I went to the 130\tools\binn folder to run .\rskeymgmt.exe -a -i <SQL Instance> -f <path to encryption key copied to the local disk in a folder that "everyone" has full control on to ensure there were no permission issues> -p <password for encryption key>" and I got a fun error: "The data is invalid. (Exception from HRESULT: 0x8007000D)". So my next thought, lets list the SSRS services with ".\rskeymgmt.exe -l -i <SQL instance>". Same error. Ok, maybe rskeymgmt is busted... lets try it with a non-SSRS instance and it tells me it can't find SSRS on that instance. So that looks good.
Getting a but stumped/worried about this migration, I decide to change the user account back to domain A\sqlacct_test and re-backup the encryption key just in case it was bad. It backs up successfully, so lets try restoring it JUST to be 100% sure it is valid. And it restores successfully. Both from the configuration manager and rskeymgmt.exe. So I know the key is good. Use configuration manager to change the service account to B\sqlacct_test and it fails to restore the key. rskeymgmt is giving me that "the data is invalid" message again.
Next thought, lets set B\sqlacct_test as sysadmin. Maybe it is database permissions. Nope, that makes no difference.
The SQL Instance is running as B\sqlacct_test, just SSRS seems to fail unless I run it as A\sqlacct_test and I am completely stumped as to what I am doing wrong. My understanding is that I should be able to restore the keys afterwards with no troubles but it just doesn't seem to be happy.SSRS is version 13.0.4466.4 Standard Edition and the report server mode is Native. My current thoughts are that it is because A\sqlacct_test and B\sqlacct_test have the same SID that when it changes between them it is screwing something up. I did try changing it to a different A\ account (mine) and it worked as expected. I have not tried any other B\ account but my next testing steps are:
1 - change it from A\sqlacct_test to A\me
2 - change it from A\me to B\sqlacct_test
OR
1 - change it from A\sqlacct_test to B\me
2 - if step 1 works, change it from B\me to B\sqlacct_testor am I missing something obvious? The service starts up no problem, it just can't decrypt any of the data which restoring the encryption keys should fix that issue.
My "last resort" method is going to be:
1 - change it from A\sqlacct_test to B\sqlacct_test
2 - delete the encryption keys (if it will let me... the "The data is invalid" error makes me think this may not work).
It used to be that you had to run some of the configuration before trying to restore the key after the migration.
At the point where you said you opened Configuration Manager to restore the key.....you would first go to the Database page and on there is the change database and change credential pieces and run through both. Even if you don't need to change anything, run through them, save and then try to restore the encryption key. This is what I did on a domain migration but it was 2008R2. It worked for others back then as well. About all I can guess is that it could be related to something changing in the configuration file. The service account change could make a difference - that's why after changing the service account, you're suppose to do a new backup of the key. But it sounds like they have the SIDs taken care of on the AD side so I kind of doubt that's an issue in your case. Maybe though....another thing you could try playing with before thinking about wiping the encrypted data.
Sue
May 2, 2018 at 4:07 pm
Strangely, I was doing more testing today and it seems that as long as I am not using the account B\sqlacct_test it works. Even using B\me it works fine.
I will try going through those other steps when I get back. I am off for the week so come Monday I'll take another stab at this. Worst case, we just need to make a new service account for SSRS on the new domain (or have it run as local service).
Thanks for the tips though.
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.
May 15, 2018 at 8:28 am
Update - so I tried everything in the SSRS configuration that I could find to change the user and every time it is the same thing. Works great with every account except the service account that got automatically migrated when our IT department moved the computer to the new domain.
So my work-around right now is to use a different account instead of B\sqlacct_test. Every other account I have tried (including B\me) works fine (when granted the appropriate permissions on the server), so I don't get 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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply