A pain point with “Trusted Connection” in Sitecore v9.1

One of the projects I’m working on at the moment came with a requirement to change Sitecore v9.1 from running with the default SQL Security accounts to trusted connections using specific Active Directory accounts that the client provided. While there’s a bit of work to do to enable this, it shouldn’t be too tough. But trying to be a bit clever, I hit upon an issue which seemed worth documenting…

A bit of background:

You may well never have delved into the SQL Databases that Sitecore provisions, but some of the more modern ones include a stored procedure called GrantLeastPrivilege. They don’t seem to be officially documented, but when you examine them, they’re helper scripts that grant exactly the database rights that a user account needs to be able to access a specific Sitecore database:

So you’d hope that they’d be helpful if you need to move from one set of database accounts to another…

The issue:

But when I tried to use these scripts with the Active Directory accounts provided by the client, I got oddly inconsistent results. When I tried to call the procedure in some databases with my user

USE [MyInstance\_Xdb.Collection.Shard0]
GO
[xdb\_collection].[GrantLeastPrivilege] 'Domain\ProcessAccountName'

I got success. It would write out all the changes it made. But when I called the same procedure in other databases, with the same approach

USE [MyInstance\_ProcessingEngineStorage]
GO
[sitecore\_processing\_storage].[GrantLeastPrivilege] 'Domain\ProcessAccountName'

I got error messages instead – saying something like:

Msg 102, Level 15, State 1, Line 9Incorrect syntax near '\'.

When I tested it, across all the non-content databases, it broke on ProcessingEngineStorage, MarketingAutomation, Processing.Pools, Processing.Tasks, ProcessingEngineTasks and ReferenceData.

Looking at the underlying code, the scripts which work run statements in the form of

EXECUTE('grant execute on TYPE::[database].[thing] TO [' + @Name + ']')

and the ones that fail look like

EXECUTE('grant execute on TYPE::[database].[thing] TO ' + @Name)

so it’s pretty easy to tweak them to be successful. But it’s a bit of a pain that this doesn’t work out of the box. So I’ve reported this to Sitecore Support, who have accepted it’s a bug. If you’re hitting this issue too, make reference to bug 331327.

(And yes, I’m aware of the whole “string concatenation is dangerous when generating SQL statements thing – that’s a debate for another day)