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
   

Title Case Function, With Exceptions

 

 I had an issue logged from a client the other day about address entries in their system getting automatically converted to title case when they didn't want this to happen, e.g. de Lorean Drive  would get auto updated to De Lorean Drive.

I was hoping this was something being done at the application layer, but no such luck and it turned out there was stored procedure doing the title case conversion. This proc was taking a relatively simple approach and just finding a PATINDEX match for a space followed by a letter, or the first letter in the string, and capitalising that letter.

Now, I'd much prefer that this sort of thing was handled in the front end, and title casing can be a very complex beast, but in this case the rules were pretty simple, and it seemed an interesting problem to solve so I set about writing some code that would capitalise each word unless that word was present in an exclusion list. I recalled I had a code snippet lying around that I could use as a starting point, so I dug that out and set to work. Not too much time later this was the basic result:

declare @t table(char_sequence varchar(10))
insert into @t(char_sequence) values('de'), ('te')

declare @input_string varchar(255) = 'de Lorean dr' -- just for testing

declare @pos int = 1
declare @next_pos int = 0
declare @out varchar(256) = '', @word varchar(255)

while @pos <= len(@input_string)
begin
    set @next_pos = charindex(' ', @input_string + ' ', @pos + 1)
    select @word = substring(@input_string, @pos, @next_pos - @pos)

    select @out +=
        case when not exists(select * from @t where char_sequence = @word)
             then stuff(@word, 1, 1, upper(left(@word, 1))) else @word
        end
        + ' '
    set @pos = @next_pos + 1

end

select rtrim(@out)

I'll break it down. First up was to create a temp table to hold the exclusion words. This could be a permanent table, temp table or table variable. I not a big fan of table variables but I used one in my dev code out of laziness. In the final production code this was replaced with a temp table, and I used a couple of exclusions to make sure the code could handle more than 1. I also have a test string here for use during development, this is removed for the final code and replaced with a parameter.

declare @t table(char_sequence varchar(10))
insert into @t(char_sequence) values('de'), ('te')

declare @input_string varchar(255) = 'de Lorean dr' -- just for testing

Next up some variables to keep track of the position as we make our way along the string. @pos will be the starting position of a word and @next_pos will be the end of the word/start of the next one. @out holds our formatted output and @word is the word currently being checked for exclusions. Note that setting the varchars to an empty string is important for string addition. Using a concat function might have got around this but, hey, whatever works.

declare @pos int = 1
declare @next_pos int = 0
declare @out varchar(256) = ''
declare @word varchar(255) = ''


Rather than looking for space-letter patterns as the original code did, my approach was to effectively split the string into words, then check to see if the word was in the exclusion table. If so it's added to the output unchanged, if not then the first letter gets capitalised before adding to the output string.

I'm using a while loop to loop through the string until no more words are found. Adding a space to the end of the input means the while loop will stop at the end rather than entering an endless loop looking for that last word. So I set @pos to the CHARINDEX of the start of the word, set @next_pos to the CHARINDEX of the next instance of a space, then get a SUBSTRING that starts at @pos and has a length of @next_pos - @pos. For example if the start of the word was at character 3 and the end of the word was at character 10 then SUBSTRING would look for a string starting at 3 and 7 characters long.

while @pos <= len(@input_string)
begin
    set @next_pos = charindex(' ', @input_string + ' ', @pos + 1)
    select @word = substring(@input_string, @pos, @next_pos - @pos)


Once we have the string, check to see if it exists in the exceptions table. It it doesn't then STUFF the capitalised first letter back into the word. If it is in the table then leave it alone, then add the word and a space to the output string.

    select @out +=
        case when not exists(select * from @t where char_sequence = @word)
             then stuff(@word, 1, 1, upper(left(@word, 1))) else @word
        end
        + ' '
    set @pos = @next_pos + 1
end

Finally we can return the final string, trimming off the extra space on the end

select rtrim(@out)

That's it. It performs well, and does the, admittedly simple, job.  Does it have limitations? Sure, but it's still relatively simple, is scalable in terms of exclusions and performs well in conjunction with the application. Enhancements could be to allow for an input string of separation characters, allow passing exclusions in (and then updating a permanent exclusion table so it's self maintaining), but that's a job for future me.