Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding how SQL Server handles Service Isolation


By:   |   Last Updated: 2013-11-26   |   Comments (13)   |   Related Tips: More > 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:

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


Last Updated: 2013-11-26


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

Hi,

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?

Thanks,
Satya.

 

 


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

 

Congratulations Brian on competing your century of community capacity building.

Warm Regards

Azim

 


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

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

Congratulations, Brian on 100 tips. 

Regards

Graham Okely


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

Congratulations Brian for your 100 tips! Keep on going!

Best Regards!


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

Nice article and congrats!!!


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

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

 

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!

Tim


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

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

Congrats Brian!


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

GREAT JOB BRIAN.  Congrats!!!

 


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

Brian,

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


Learn more about SQL Server tools