We are just a bunch of geeks who love working in IT and are making the most of the transition that the industry is currently undergoing. This is the place where we learn and collaborate on our DevOps journey.

Friday, 6 January 2017

SQL Service SIDs

This is a new one to me...

I set up an SMB Share on the Backup directory for a SQL Server 2016 instance (using DSC of course) and assigned the SQL Server AD account full access, only to find that I was getting an "Access Denied" exception when I tried backing up using the share but not when backing up using the local path. This made no sense because the AD account had full access to the folder and the share (or so I thought).

However, Microsoft has been doing stuff with security accounts and promoting Managed Service Accounts as a best practice so I figured "what the heck" and tried replacing the AD account with the SQL Server Service (i.e. NT Service\MSSQLSERVER) on the share...and what do you know, it worked.

Turns out that the SID for the AD account and the the SID for the SQL Server Service are not the same so even though the account does have access to both the folder and the share it only has access to the folder via the SQL Server Service. To sort this out you need to use the same thing for both, either the SQL Server Service or the AD Account but you can't mix the two.

IT huh, almost 20 years on and still learning the basics. Later dudes!