Group Policy, Local Permissions and the SSRS Account

 I had a nerve wracking (wrecking) experience with SSRS the other day, caused by a chain of events. The backstory:

A client has been on a drive to improve security across the environment. This is a good thing and something I've been fully supportive of, so at my suggestion we've been implementing group managed service accounts for the SQL related services to improve security around service accounts and local logins.

A further step in the journey has been to control the local security permissions, things like Lock Pages in Memory, with a centralised group policy. Again, all good stuff.

And now the issues:

The group policy was setting, among other things, the accounts that have the Log on as a Service permission. This wasn't update-able from the local server, being locked down by the gpo, and due to only having one SSRS instance, we didn't have a separate service account for SSRS. This had all been in place for a couple of weeks, but the server hadn't been restarted, so was quite happy to carry on with the existing virtual account. As soon as we applied Windows patches and restarted the server, SSRS refused to start. The actual error from the SSRS Configuration Manager doesn't give much information, but looking in the System event log showed:

Adding to the dilemma was that we didn't have a backup of the encryption keys (due to a couple of reasons), but thankfully did have the ability to redeploy reports if necessary.

So first up, try to change the service account in SSRS Configuration Manager. I figured I could use one of the existing "permitted" service accounts so set about changing the account. This failed miserably. When changing the account via the config manager, it first tries to backup the encryption key, then restores it once the account has changed, but in this case, because the service couldn't start the manager couldn't back up the key, so the change failed.

Now, it's not recommended to change the service account via the Windows services manager, actually you should never change the account outside of the config manager, but given we were stuck I figured it was worth a try, so I took a copy of the current service account, changed it to one of our group managed ones and voila, the service started. I fired up a browser and navigated to the site, and was prompted with a login. So far so good. Login succeeded and page started to load but then showed the dreaded, but somewhat expected message The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncryptedData). No problem, I'll just change it back, however going back into the service manager the ability to change the login was greyed out and no amount of starting and stopping the service re-enabled the ability to change the service account.

Thankfully there's a command line way of configuring Windows services, the sc executable. Time to give this a try

sc.exe config SqlServerReportingServices obj="NT Service\SQLServerReportingServices"

This worked, so now I was back to the start, with a service that wouldn't start. 

At this point the only option I could see was to remove the group policy setting so that I could add the service account back into the Log on as a Service permissions. I don't have AD permissions, so a quick call to the friendly domain admin to explain the situation and they moved the server to a temporary OU which stopped the policy getting applied. This actually took a while (around 30 minutes) to filter down, with numerous attempts at gpupdate /force to try and get it to apply.

Once this took effect I was able to grant the virtual account the necessary log on permissions. As the server wasn't getting any values for this setting I also needed to add the group managed service accounts as well. It was then a simple matter of starting SSRS, checking everything was working through the portal, then updating the account in the config manager to use a new account. The server was moved back into the correct OU, and after the policies flowed through everything kept working. The last thing was to back up the encryption keys!

So learnings:

• Some changes affecting the services won't be apparent until the service is restarted, and this could be at an awkward time such as during patching
• Always change the SSRS account using the SSRS config manager. Anything else will cause problems
• Backup the SSRS encryption key! This was a system set up by a different team and no one, myself included, had thought to check if the key was backed up