SQL Server best practice: grant permissions to per-service SID


Since Windows Server 2008/Windows Vista, from SQL Server 2008 onward, SQL Server installation process automatically generates per-service security identifier (SID). Whenever possible, it is recommended to grant rights to this service SID for security reasons, instead of your SQL Server’s startup account, which typically is domain user account.

For example, for performance reasons, I always want to SQL Server to have the following rights: Instant File Initialization and Lock Pages in Memory. The former enables instantaneous data (not log) file growth; whereas the later prevents Windows system from paging SQL Server data to virtual memory on disk.

Those rights can be granted via the Local Security Policy application, secpol.msc. Navigate to Security Settings -> Local Policies -> User Rights Assignment, you’ll find them there. Please note that Instant File Initialization is actually called “Perform volume maintenance tasks”.

Before service SID was introduced, I always granted those rights to SQL Server’s startup account. In my case it was typically a domain\user account. With the introduction of service SID, SQL Server’s resource access rights is the sum of both its startup account and service SID. Therefore it is recommended to grant rights to service SID, for obvious security reasons.

To prove that’s the case, let’s conduct the following experiment. For default instance of SQL Server, its service SID is NT Service\MSSQLSERVER. For named instance, its service SID is NT Service\MSSQL$InstanceName. Please note instant file initialization, once enabled, only works for SQL Server data files, not logs.

1. Assume your SQL Server instance is running under a domain\user account without “Perform volume maintenance tasks”;
2. Run the following code:
[code language=”sql”]
dbcc traceon(3004,3605,-1)
go

create database TestDb
go

exec sp_readerrorlog
go

drop database TestDb
go

dbcc traceoff(3004,3605,-1)
go
[/code]

Pay attention to the output of “exec sp_readerrorlog”. You should see something similar to this:

2016-05-19 23:39:35.830 spid51 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf from page 0 to 1024 (0x0 to 0x800000)
2016-05-19 23:39:35.890 spid51 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf (elapsed = 66 ms)

3. Now using secpol.msc, grant your service SID, in my case, NT Service\MSSQLSERVER, the right of “Perform volume maintenance tasks”;
4. Restart SQL Server instance;
5. Repeat step 2, you shouldn’t see entry similar to the one listed above in the error log, indicating that SQL Server has the combined rights of its startup account and its service SID.

By the way, this also applies to data and log folder permissions. Only grant data and log folder permissions to service SID, not its startup account. I have automated that process here.

Happy learning!

, ,

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.