A couple of weeks ago at DevDays Netherlands, I struggled a bit with a demo that was relatively straightforward in SQL Server 2008 running under Windows Server 2003 R2, but has an interesting twist in SQL Server running under Windows Server 2008. The interesting twist is based around the fact that SQL Server 2008 and above use Service SIDs when running under Windows Server 2008. The demo struggle turned out to be "pilot error"; the Service SID is the principal that should be given permission. So I thought it would be a nice excuse to write about Service SIDs and SQL Server and use with different utilities. The point of Service SIDs, for starters, is that Windows Services that run under the same Service Account (for example, running both SQL Server and SQL Agent as Network Service), don't have access to each other's resources, because a unique SID is generated for each service.
I've always been a proponent of running SQL Server services using principal of least privilege. Starting in SQL Server 2005, this meant creating an local or domain account (I call mine "SQLService") with no special privileges. It's only a member of Windows Group Users or Domain Users. During installation, SQL Server install process creates a series of local groups grants the requires permissions to the group and puts your account (SQLService) in the appropriate group. Some of the group names change over releases too, so I thought I'd document these for folks who like to script things, so you can remember to change the name in your script. I'll do that in an additional blog entry, later.
Additional privileges are required for some special SQL Server activities. Three that come to mind are:
Instant File Initialization
Using the Windows Security Log as an Audit target
Using the ETW target for extended events
For the first two, you simply add the SQL Server service Windows Group (e.g. SQLServerMSSQLUser$ZMV08$MSSQLSERVER where ZMV08 is my machine name) to the Policy using Local Security Policy tool (Perform Volume Maintanance Tasks for Instant File and Generate Security Audits to use Windows Security Log. Using the ETW target requires the service account to be a member of the Performance Log Users group. Since you can't nest local groups, you must add the service account *user* to the Performance Log Users group for this one. Before Windows Server 2008, I simple added my user, SQLService. When running under Windows Server 2008, I must add the SQL Server Service SID to the group, not SQLService. If you use GUI tools, you must type the Service SID in by hand. For the default SQL Server instance, this is NT SERVICE\MSSQLSERVER. For a named instance, say SQLDEV01 instance, this would be NT SERVICE\MSSQL$SQLDEV01. Adding the account you named on install (SQLService) will NOT work, you must use the Service SID.
A few more things about SQL Server's use of Service SIDs.
-Not all services associated with SQL Server use Service SIDs. The VSS Writer services doesn't, for example.
-Using SQL Server Configuration Manager and Control Panel/Services applet shows the account you chose at install. Not the service SID.
-Changing the Service Account in these utilities will not change the fact that the Service SID is being used. The Service SID does not change.
-Service SID names and Windows acct IDs do not change between releases except for the SSIS service. In SQL Server 2008 and 2008 R2, the service SID name is NT SERVICE\MsDtsServer100, in Denali CTP1 its NT SERVICE\MsDtsServer110.
-Service SIDs are not used with SQL Server 2005, but are used with all versions starting with SQL Server 2008. Windows Server 2008 OS or above is required, because that's when this OS feature was introduced.
To find out if a service uses Service SIDs, use the command line utlity sc. sc qsidtype [servicename] will show service SID usage. This is a much better strategy than looking at membership in SQL Server's NT Service Groups. As a example, the MSSQLFDLauncher service (Full-Text Filter Daemon Launcher) has no users in the NT Service Group SQLServerFDHostUser$[MachineName]$MSSQLSERVER where [MachineName] is the actual machine name. But using the sc utility shows that it uses Service SIDs. It's also interesting to note that although Service SIDs can run with a restricted or unrestricted token, all SQL Server-related services use an unrestricted token.
For more info about the Service SID OS feature in general run this blog entry is a good start.
@bobbeauch