Understanding how SQL Server handles Service Isolation

By:   |   Comments (13)   |   Related: > Security


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?


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:

How Service Isolation Works in SQL Server

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:

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:

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:

Putting The Login Together with the Service

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley 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

Comments For This Article

Wednesday, November 27, 2013 - 11:23:57 PM - Atif Back To Top (27632)

Brian, Congrats for acheiving high count of quality contributions. Your tips are really helpfull for every DBA.

Thanks & Regards

Wednesday, November 27, 2013 - 8:28:17 AM - satya Back To Top (27624)


I have created WINDOWS2K12-64bit machines. i have installed two MSSQL2012-SP1-64bit instances.
Installed build using Non-default user.Unable to connect particular instance database in SQL Server Management studio 

So could you please tell me how to connect database in win2k12-64bit machines with installed build non-default user?




Tuesday, November 26, 2013 - 11:58:18 PM - Azim Back To Top (27622)


Congratulations Brian on competing your century of community capacity building.

Warm Regards



Tuesday, November 26, 2013 - 7:08:07 PM - Arshad Back To Top (27621)

Congratulations Brian and thank for your contribution!

You tips have been really helpful, especially in terms of security.

Thanks once again.

Tuesday, November 26, 2013 - 6:10:51 PM - Graham Okely Back To Top (27620)

Congratulations, Brian on 100 tips. 


Graham Okely

Tuesday, November 26, 2013 - 5:48:33 PM - Daniel Farina Back To Top (27619)

Congratulations Brian for your 100 tips! Keep on going!

Best Regards!

Tuesday, November 26, 2013 - 3:14:26 PM - Kun Lee Back To Top (27618)

Nice article and congrats!!!

Tuesday, November 26, 2013 - 11:54:42 AM - Armando Prato Back To Top (27615)

100 tips... I don't think I even know 100 things about anything.   Congratulations!

Tuesday, November 26, 2013 - 11:44:08 AM - Tim Cullen Back To Top (27614)


Congratulations, Brian!  We shall be inspired by you.  And Happy Thnaksgiving to those who celebrate it and a have great week to those who don't!


Tuesday, November 26, 2013 - 11:20:19 AM - Greg Robidoux Back To Top (27612)

Brian congratulations on your 100th tip on MSSQLTips.com.  Thanks for all of your contributions over the years to both MSSQLTips and all of the other sites as well as all of the presentations you have delivered. 

Tuesday, November 26, 2013 - 11:06:22 AM - Brady Back To Top (27611)

Congrats Brian!

Tuesday, November 26, 2013 - 10:45:53 AM - Scott Murray Back To Top (27610)

GREAT JOB BRIAN.  Congrats!!!


Tuesday, November 26, 2013 - 10:20:13 AM - Jeremy Kadlec Back To Top (27608)


Congrats on your 100th tip!


Dear Community,

If any of Brian's tips have ever helped you.  Let him know here and thank him for his dedication to the community!


Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader

get free sql tips
agree to terms