Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Using Service SIDs to grant permissions to services in SQL Server
In this articleSQL Server uses per-service Security Identifiers (SID) (also referred to as service security principal (SID)) to allow permissions to be granted directly to a specific service. This method is used by SQL Server to grant permissions to the engine and agent services (NT SERVICE\MSSQL$<InstanceName> and NT SERVICE\SQLAGENT$<InstanceName> respectively). Using this method, those services can access the database engine only when the services are running. This same method can be used when granting permissions to other services. Using a Service SID eliminates the overhead of managing and maintaining service accounts and provide tighter, more granular control over permissions granted to system resources. Examples of services where a Service SID can be used are:
Some services don't have a Service SID by default. The service SID must be created using SC.exe. This method has been adopted by Microsoft System Center Operations Manager administrators to grant permission to the HealthService within SQL server. Once the service SID has been created and confirmed, it must be granted permission within SQL Server. Granting permissions is accomplished by creating a Windows login using either SQL Server Management Studio (SSMS) or a query. Once the login is created, it can be granted permissions, added to roles, and mapped to databases just like any other login. Tip If the error SecurityEliminate service accountsTraditionally service accounts have been used to allow services to log into SQL Server. Service accounts add an additional layer of management complexity because of having to maintain and periodically update the service account password. Additionally, the service account credentials could be used by an individual attempting to mask their activities when performing actions in the instance. Granular permissions to system accountsSystem accounts have historically been granted permissions by creating a login for the LocalSystem (NT AUTHORITY\SYSTEM in en-us) or NetworkService (NT AUTHORITY\NETWORK SERVICE in en-us) accounts and granting those logins permissions. This method grants any process or service permissions into SQL, which is running as a system account. Using a Service SID allows permissions to be granted to a specific service.
The service only has access to the resources it was granted permissions to when it is running. For example, if the ExamplesA. Create a Service SIDThe following PowerShell command will create a service SID on the System Center Operations Manager health service.
Important
B. Query a Service SIDTo check a service SID or to ensure a service SID exists, execute the following command in PowerShell.
Important
C. Add a newly created Service SID as a LoginThe following example creates a login for the System Center Operations Manager health service using T-SQL.
D. Add an existing Service SID as a LoginThe following example creates a login for the Cluster Service using T-SQL. Granting permissions to the cluster service directly eliminates the need to grant excessive permissions to the SYSTEM account.
E. Grant permissions to a Service SIDGrant the permissions required to manage Availability Groups to the Cluster Service.
Note Removing the service SID logins or removing them from the sysadmin server role can cause problems for various components of SQL Server that connect to the 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 using the following Transact-SQL commands:
For a named instance of SQL Server, use the following Transact-SQL commands:
In this example, Next stepsFor more information about the service sid structure, read SERVICE_SID_INFO structure. Read about additional options that are available when creating a login. To use Role-Based Security with Service SIDs, read about creating roles in SQL Server. Read about different ways to grant permissions to Service SIDs in SQL Server. For more information on configuring service accounts for SQL server, read Configure Windows service accounts and permissions. FeedbackSubmit and view feedback for Additional resourcesAdditional resourcesIn this articleWhat process identifies and grants access to a user who is trying to access a system quizlet?Authentication: the process of identifying and granting access to some user, usually a person, who is trying to access a system. In Windows, this is most commonly handled by a password-protected account.
What is the process of identifying who has access to the network?Authentication is used by a server when the server needs to know exactly who is accessing their information or site. Authentication is used by a client when the client needs to know that the server is system it claims to be. In authentication, the user or computer has to prove its identity to the server or client.
What process identifies the rights and actions a user can take within a system?Authorization is the security process that determines a user or service's level of access. In technology, we use authorization to give users or services permission to access some data or perform a particular action.
What are the two types of permissions that you can configure to manage user access to files and folders on a Windows system?There are six standard permission types which apply to files and folders in Windows: Full Control. Modify. Read & Execute.. view the file names and subfolder names.. navigate to subfolders.. open files.. copy and view data in the folder's files.. |