¿Privileges on the Service SID or on the SQL Service Account?
On SQL Server 2008 the concept of Service SID was introduced to be used when installing on Windows 2008 or later.
The concept of Service SID (also known as Virtual Account) allows assigning the required permission needed for the correct functioning of the SQL Instance without using Local Groups or Domain Groups (in a cluster scenario). Instead, the permission will be assigned to the Service SID.
Just to be clear, the concept of Service SID is no from SQL Server, but form Windows Server 2008 or later, and SQL Server 2008 or later can take advantage of this feature.
The interesting part when configuring permissions or rights like “Lock Pages in Memory” or “Perform Volume Maintenance Tasks”, historically they were assigned to the SQL Service account. But now that the Service SID come into play, whom should I assign the rights? Similarly, if I want to do a backup on a network share, whom should I assign the permission on the share, so SQL Server can write the backup.
The same questions apply to any other privilege that before should be assigned to the Service account.
The answer is, that you can assign the privilege or permission to any and SQL Server will work fine. However, because the Service SID have the scope of only the computer where you installed SQL, to assign permission outside the computer you have to use the computer account, the name format is “DomainName\ComputerName$”.
In conclusion the privileges for SQL Service is the union of the privileges from the Service SID plus the privileges from the Service account.
Now that you know you can assign the privileges to both accounts, the service account or the Service SID, Which one should we use?, because the service account can be changed, the best practice is to assign the privilege to the service SID whenever possible.
To be clear on the concept of Service SID, for those who don’t have experience with it, check the following images. The account “NT Service\MSSQLServer” is the Service SID. The name of the service SID will include the instance name as part of the name, for example “NT Service\MSSQL$Denali”. In the images is shown how to add a Service SID to the privilege Lock Pages in Memory and the permissions for a folder.
The use of the service SID are extended on SQL Server 2012 and also exists the concept of Managed Service Account starting on Windows 2008, but those are concepts for other post.
References
https://support.microsoft.com/kb/2620201/en-us
https://msdn.microsoft.com/en-us/library/ms143504.aspx#MSA
Comments
Anonymous
January 01, 2003
Hi John, thanks for your question, before answering it I would like to point a clarification, the SQL Installation does not automatically assign the “Lock Pages in Memory” Right to the SQL Service Account, it need to be added manually if needed. As you said, Just click [Add User or Group] button and Type the name of the Service SID, if you are in a domain be sure to first select your computer in the locations tab, because there is where this account exists. You can get the service name from the properties of the service in the services applet.Anonymous
September 04, 2013
Your screenshot of "Lock Pages in Memory" doesn't show how to assign that right....it just shows that NT SERVICEMSSQL$DENALI and MSSQLSERVER have the rights....they were assigned by SQL installation. If you click on the [Add User or Group] button, how do you drill into the NT SERVICE accounts themselves. Try it....if you use the local computer as context and select all the types possible...the NT SERVICE accounts are not available to choose....(and, of course, if you choose the Entire Network as context and all types....they are not there either since Virtual Accounts are local to the computer). So the question is: how do you explicitly assign the"Lock Pages in Memory" to the an NT SERVICE account?