Microsoft SQL Server enables a per-service SID for each of its services on Windows Server 2008-based or Windows Vista-based operating systems in Microsoft SQL Server 2008 and in Microsoft SQL Server 2008 R2 to provide service isolation and defense in depth. The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for SQL Server service might be the following:
For stand-alone instances of SQL Server on Windows Vista and on Windows Server 2008 operating systems, service SIDs are added to the service group, and the service SID for SQL Server Engine and SQL Server Agent is added as a login to the sysadmin server role.
By default, for SQL Server failover cluster instances on Windows Vista and on Windows Server 2008 operating systems, SQL Server Setup uses the service SID and sets SQL Server and operating system resource ACLs to the service SID.
- SQL Server Agent cannot start or connect to a SQL Server service
- SQL Server Setup programs encounter the problem that is mentioned in the following Microsoft Knowledge Base article:
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\MSSQLSERVER', @rolename = N'sysadmin'
CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\SQLSERVERAGENT', @rolename = N'sysadmin'
For a named instance of SQL Server, you can correct this situation by adding the service SID. To do this, use the following Transact-SQL commands:
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\MSSQL$SQL2008R2', @rolename = N'sysadmin'
CREATE LOGIN [NT SERVICE\SQLAgent$SQL2008R2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\SQLAgent$SQL2008R2', @rolename = N'sysadmin'
The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.
|Rule software||Rule title||Rule description||Product versions against which the rule is evaluated|
|System Center Advisor||SQL Server service SID is not present in the correct state||System Center Advisor determines whether the two service SIDs that are provisioned during setup exist with the same configuration. If the service SID login is removed, disabled, or removed from the sysadmin server role, System Center Advisor generates a warning for this instance of SQL Server. Review the information that is provided in the “Information Collected” section of the advisor alert, and revert the service SID to its original configuration.||SQL Server 2008|
SQL Server 2008 R2
Article ID: 2620201 - Last Review: Oct 7, 2011 - Revision: 1