Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. SQL Server uses a service SID to provide service isolation

SQL Server uses a service SID to provide service isolation

View products that this article applies to.

Summary

Microsoft SQL Server uses a security group to set resource access control lists (ACLs) instead of using the service account directly. Therefore, changing the service account can be done without having to repeat the resource ACL process. The security group can be a local security group, a domain security group, or a service security identifier (SID). During SQL Server installation, SQL Server Setup creates a service group for each SQL Server component. These groups simplify granting the permissions that are required to run SQL Server services and other executables and help secure SQL Server files. Depending on the service configuration, the service account for a service or for a SID is added as a member of the service group during an installation or an upgrade.

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:
NT Service\MSSQL$<InstanceName>
Service isolation enables access to specific objects without the need to run a high-privilege account or weaken the security protection of the object. By using an access control entry that contains a service SID, a SQL Server service can restrict access to its resources.

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.

↑ Back to the top

More Information

By removing the service SID logins or by removing them from the sysadmin server role, problems can result for various components of SQL Server that connect to SQL Server Database Engine. Some problems include the following:For a default instance of SQL Server, you can correct this situation by adding the service SID. To do this, use the following Transact-SQL commands:
CREATE LOGIN [NT SERVICE\MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\MSSQLSERVER', @rolename = N'sysadmin'
GO
CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\SQLSERVERAGENT', @rolename = N'sysadmin'
GO

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:
CREATE LOGIN [NT SERVICE\MSSQL$SQL2008R2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\MSSQL$SQL2008R2', @rolename = N'sysadmin'
GO
CREATE LOGIN [NT SERVICE\SQLAgent$SQL2008R2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\SQLAgent$SQL2008R2', @rolename = N'sysadmin'
GO

Note In this example, SQL2008R2 is the instance name of the SQL Server.

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 softwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server service SID is not present in the correct stateSystem 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

↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2620201
Revision : 1
Created on : 1/7/2017
Published on : 10/7/2011
Exists online : False
Views : 1416