DataGinger.com

As of today (5/14/2013) this MSDN article says “SQL Server Agent service startup account must be a member of the SQL Server sysadmin fixed server role”

However, in one of our environments (SQL 2012) I have seen, just a public fixed server role is good enough to start the SQL Agent services and it works very well.

So is the statement mentioned in the MSDN article incorrect? looks like it is..moreover later I figured out the account (either a domain account or built-in system account) used for running any of the SQL Server services may not necessarily have a SQL login at all…sounds interesting?

Before going further on this topic let’s first understand What is a Per-Service SID?

SQL 2008 introduced this new option of SID (Security Identifier), a mechanism that assigns privileges to the SQL service itself, rather than to the account under which the service runs.

So starting SQL Server 2008+ on Windows Server 2008+ here is how Service Configuration and Access Control is managed:

• SQL Server enables per-service SID for each of its services to provide service isolation and defense in depth

• This per-service SID is derived from the service name (e.g. Database Engine/SQL Server Agent/SSIS/SSRS..etc) and is unique to that service

• For example, a service SID name for the Database Engine service might be NT Service\MSSQL$<InstanceName>

Service SIDs managed to improve our security because:

• 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

And here’s my understanding after some research

• The account (either a domain account or built-in system account) used for running SQL Server services may not necessarily have a SQL login if the Per Service SID has a login instead

• SQL Server DB Engine and SQL Server Agent runs as Windows services named NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT respectively within the Windows environment.

• And the Service Account which we specify to run the services will be used when a process tries to reach outside of the current Windows environment.

• Hence, the Service Startup Account will be used for off-box operations, unless it’s a built-in account like Network Service or a Virtual Account and if the server is Domain-joined, in which case the Machine Account will be used.

• On SQL 2012 separate Service Startup account can be eliminated and the SQL Services can run as the “Virtual Accounts”, which have the same name as the Per Service SID, but which also have a system-managed password.

Always remember SQL Server Secuirty is all about Principle of Least Privilege. For more specific information refer MSDN BOL: Configure Windows Service Accounts and Permissions