Understanding how SQL Server handles Service Isolation

By: K. Brian Kelley   |   Updated: 2013-11-26   |   Comments (13)   |   Related: > Security

Problem

When I look at who is connected to my SQL Server, for instance by sys.dm_exec_sessions, I see a login that hasn't been granted access when I check the Logins folder. I know the login is the SQL Server Agent service account. I see "NT Service\SQL Agent" but I don't see any connections with that login. What's going on here?

Solution

What you're experiencing is due to Service Isolation. Starting with Windows Vista, all Windows operating systems support this security feature. Prior to SQL Server 2008, Microsoft SQL Server only supported the standard Domain\Username or ComputerName\Username method for granting access. That's because operating systems through Windows Server 2003 didn't have this service isolation feature.

So what is Service Isolation?

Every Windows user account, security group, and computer account has a unique identifier, its security identifier or SID. This is supposed to be unique and not change so long as the account stays in the same Windows domain (if a user account is moved to a different Windows domain, the SID will change). You can rename the account, for instance, if someone mistypes the group name, and Windows handles it just fine. That's because the SID doesn't change.

The problem with granting access to a user account is that any process that is running under that user account has access. If you look at the list of services and their logins, you'll see a lot of logins for Network Service and System. That means any process that runs under Network Service can access any resource Network Service is granted access to. And perhaps that's not what's intended. Perhaps I mean one particular service running under Network Service and not any of the other services running under Network Service. How do I get that? That's where Service Isolation comes in.

With Service Isolation, not only do user accounts, security groups, and computer accounts have SIDs, but so do services. So I can grant permission to one service and not the other. Network Service isn't explicitly granted access, the first service is. If you try to touch the resource with the second service, you can't. You can only get to it from the first service.

How Service Isolation Works in SQL Server

In SQL Server, you see access due to Service Isolation via a login that begins with "NT Service" like in the following case:

Here you can see an entry belonging to SQL Server Agent. Don't let the $SQL2008R2 fool you. It's named this way because this happens to be a named instance SQL2008R2. It is through this login that SQL Server Agent is able to connect. However, when you query sys.dm_exec_sessions, you won't see a connection that matches this. You can see what connection is being made due to SQL Server Agent using the following query:

SELECT program_name, login_name
FROM sys.dm_exec_sessions
WHERE program_name LIKE 'SQLAgent%';

Here's a sample output from a SQL Server installation, the same one that has NT SERVICE\SQLAgent$SQL2008R2:

I've obscured the computer name, but you can see from the username that it certainly isn't SQLAgent$SQL2008R2. It's the regular user account on the computer in question that has been assigned to SQL Server Agent. We can confirm this by looking in SQL Server Configuration Manager:

We see the match, svc.SQL2008R2Agent is the service account for the SQL Server Agent service. SQL Server is granting access by the service SID, but it's telling us the actual user account coming in. It will do this for any login coming in via a service and granted access via an NT SERVICE\* login. It's not obvious at first glance, but that's what's going on.

Putting The Login Together with the Service

So how do you put login together with the service? After all, not all services are going to be as easy to confirm like SQL Server Agent was. Your best bet is to go back to sys.dm_exec_sessions and run the query above. Hopefully, the program_name will be descriptive enough for you to match that up to a login. If it's not, have a look at the services and what logins each one runs under. You should be able to match up the login from sys.dm_exec_sessions with a login from the Logins folder (or sys.server_principals, if you're querying that catalog view). For instance, here's the match for the given example:

Be aware that multiple services could be using the same login. In that case you'll have to check which services are running and try to determine what the login is doing to figure out the connection. This is one very good reason to use different user accounts for different services. By separating out the accounts, it makes the process of figuring out what connections are tied to what services.

Next Steps

• Learn how to create secure service accounts for the SQL Server services.
• Understand why you shouldn't use System for any of the SQL Server services.
• Know what tables and views to use for auditing SQL Server logins.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips