June 3, 2017 at 8:43 am
I have a Windows Server 2016 Standand and SQL Server 2014 Standard installation. I am trying to grant permissions to local folders to SQL via the folder security permissions, but the only sql user/service listed is SQLServer2005SQLBrowserUser$<ServerName>. No other SQL users listed.
When I look at my existing SQL Server 2008 R2 server which resides on a Windows Server 2008 Standard R2 server, I can see SQLServerMSSQLUser$<ServerName>$MSSQLSERVER. There are also other SQLServerMSSQL accounts listed as well. This user or probably service was granted folder/file permissions on this box, and I am trying to replicate the same folder permissions on a new SQL Server 2014 server. Can you shed some light on this matter? SQL Server 2014 might work differently than SQL Server 2008.
June 3, 2017 at 2:33 pm
cmp119 - Saturday, June 3, 2017 8:43 AMI have a Windows Server 2016 Standand and SQL Server 2014 Standard installation. I am trying to grant permissions to local folders to SQL via the folder security permissions, but the only sql user/service listed is SQLServer2005SQLBrowserUser$<ServerName>. No other SQL users listed.When I look at my existing SQL Server 2008 R2 server which resides on a Windows Server 2008 Standard R2 server, I can see SQLServerMSSQLUser$<ServerName>$MSSQLSERVER. There are also other SQLServerMSSQL accounts listed as well. This user or probably service was granted folder/file permissions on this box, and I am trying to replicate the same folder permissions on a new SQL Server 2014 server. Can you shed some light on this matter? SQL Server 2014 might work differently than SQL Server 2008.
It's due to the security changes in using the per service SID and the different ways that the different accounts are used for different purposes. It's not as obvious as to how to do this as it was before but the service isolation is a good thing. For local folders, you would want to grant permissions to the per service SID. And those aren't going to be available in the list if you browse for accounts as they are technically services, not accounts. To add the accounts, type in the account name and then do the check names and it should resolve.
For a default instance, use: NT Service\MSSQLServer
For a named instance, use: NT Service\MSSQL$Instancename
The steps are also listed here:
Configure File System Permissions for Database Engine Access
If you want to read more about the per service SID, the following documentation explains it some - starting at the new account types section:
Configure Windows Service Accounts and Permissions
I searched and read a lot of other articles before it made much sense. Just do a search on per service SID and you will get a lot of related articles.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply