none
Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they for? RRS feed

  • Question

  • Hi Guys,

     

    I’ve done a fair amount of research for this question but just cannot seem to find the answer to my question in simple, non-DBA, terms.

     

    Server 2008 R2

    SQL 2008 R2

     

    There are 2 users in the system database logins (NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT) … what are they for? It appears that they are accounts to run the corresponding Windows services but yet they cannot be selected from the list of available built-in accounts, local accounts or domain accounts.

     

    Also, I am using a couple of domain user accounts to run the services, do I need to add them to the database? I changed the service accounts from NETWORK SERVICE to the domain user accounts using the SQL Configuration Manger which is supposed to take care of managing the user group membership and registry changes but the domain accounts are not in the database …. The services appear to be running fine.

     

    Thanks

    Friday, July 15, 2011 1:12 PM

Answers

  • In basic terms:

    As you say, in the SQL Server Database Engine there are two logins; NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT. The Database Engine runs in Windows as a Windows service named MSSQLSERVER. The NT SERVICE\MSSQLSERVER login is used by the service to connect to the Database Engine. Basically, this is how it connects to itself. The SQL Server Agent runs as a Windows service named NT SERVICE\SQLSERVERAGENT. The NT SERVICE\SQLSERVERAGENT login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the msdb database to find out what it should do; and then do it. Both of these logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way.

    No, they can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.

    As for the account that you used to run the services, this is complicated and has changed from SQL Server 2005 to SQL Server 2008 and now again in SQL Server Code Named 'Denali'. The short answer is that the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group SQLServerMSSQLUser$computername$MSSQLSERVER.

    The good news is that SQL Server Configuration Manager figures out all the stuff you need when you change the accounts. If you are a glutton for punishment, you can get an idea for how complicated this is by looking at the Denali documentation where I have tried to provide more specific information. (Note this is not the same as SQL Server 2008.) You can see it at: Configure Windows Service Accounts and Permissions http://msdn.microsoft.com/en-us/library/ms143504(SQL.110).aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Mr. Wharty Sunday, July 17, 2011 2:36 AM
    • Marked as answer by Stephanie Lv Tuesday, July 26, 2011 8:52 AM
    Friday, July 15, 2011 3:33 PM

All replies

  • In basic terms:

    As you say, in the SQL Server Database Engine there are two logins; NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT. The Database Engine runs in Windows as a Windows service named MSSQLSERVER. The NT SERVICE\MSSQLSERVER login is used by the service to connect to the Database Engine. Basically, this is how it connects to itself. The SQL Server Agent runs as a Windows service named NT SERVICE\SQLSERVERAGENT. The NT SERVICE\SQLSERVERAGENT login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the msdb database to find out what it should do; and then do it. Both of these logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way.

    No, they can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.

    As for the account that you used to run the services, this is complicated and has changed from SQL Server 2005 to SQL Server 2008 and now again in SQL Server Code Named 'Denali'. The short answer is that the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group SQLServerMSSQLUser$computername$MSSQLSERVER.

    The good news is that SQL Server Configuration Manager figures out all the stuff you need when you change the accounts. If you are a glutton for punishment, you can get an idea for how complicated this is by looking at the Denali documentation where I have tried to provide more specific information. (Note this is not the same as SQL Server 2008.) You can see it at: Configure Windows Service Accounts and Permissions http://msdn.microsoft.com/en-us/library/ms143504(SQL.110).aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Mr. Wharty Sunday, July 17, 2011 2:36 AM
    • Marked as answer by Stephanie Lv Tuesday, July 26, 2011 8:52 AM
    Friday, July 15, 2011 3:33 PM
  • This is a question for me for a long time too and not a very clear answer I can find on internet.

    In our cases when we install SQL server at the step to choose an account running SQL server and sql agent services, we use a domain account for example mydomain\mysqlsvcUser for both SQL server and sql agent.

    Now if for example we have a SSIS job scheduled to run in SQL agent and  running using SQL agent service acccount. my question is what account it is using, using a. mydomain\mysqlsvcUser or using b. NT SERVICE\SQLSERVERAGENT.

    I guess it is using a. A strange thing is that I found in one of my packages is that it runs under the SQl agent service account, but I don't see the account  mydomain\mysqlsvcUser is added specifically as a user to the database which is used in the package. All the time the package runs successfully, in the package it runs tasks like to run stored procedures to drop table, create user, create constraints, etc.

    But the service account is not a security user in the database, how this works?

    Please note I don't want to talk anything about proxy account, but just SQl service agent account.

    Thanks

    Sqlfriends


    SQLFriend

    Thursday, March 8, 2012 12:07 AM
  • Hi, I do have the same sort of problem. I need to give the sql 2012 account proper read/write permissions to my local D:\ drive , how do i do this? Its just not showing up in the security settings for that drive.

    Thanks and Regards Mohamed

    Wednesday, May 23, 2012 4:10 AM
  • I had to give read/write permission for everyone to fix my problem, but i was hoping to get answers for giving permission to that sql server 2012 virtual account in some way.

    Thanks and Regards Mohamed

    Wednesday, May 23, 2012 11:36 AM
  • Described in the very complicated Books Online topic Configure Windows Service Account and Permissions http://technet.microsoft.com/en-us/library/ms143504(v=sql.110).aspx in the section Windows Privileges and Rights. The per service SID should be assigned the access permission on the file location, in your case somewhere on the D drive. To grant that permission, right-click the file system folder, and then click Properties. On the Security tab, click Edit and then Add. Now you are in the Select Users, Computer, Service Account, or Groups dialog box. Click Locations, and then at the very top of the location list, select your computer name, and then click OK. Now in the Enter the object names to select box, provide the name of the per service SID listed on that Books Online topic. For the Database Engine per service SID, use  NT SERVICE\MSSQLSERVER for a default instance, or  NT SERVICE\MSSQL$InstanceName for a named instance. Click Check Names, it will tell you name not found, then click OK, and it will pop up a Multiple Names Found dialog box. (At least it does on my computer.) Now select MSSQLSERVER and click OK a couple of times to back up. Eventually you get back to the spot where you can allow full control to the per service SID. Repeat for the SQL Server Agent account if you need to.

    Let me know if this works for you. If so, I'll add the steps to Books Online. E-mail me at Rick.Byham@Microsoft.com if you have advice, comments, or rants about this.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by shanferouze Thursday, May 24, 2012 9:16 AM
    Wednesday, May 23, 2012 3:17 PM
  • That works great Rick, thanks for your answer.

    Thanks and Regards Mohamed

    Thursday, May 24, 2012 9:17 AM
  • Rick, I say thank you too, clever explanation what worked out.

    Can you explain how can i define that permission for SqlServerAgent running on host "HostA", on the folder D:\Folder1\ placed on the HostB ?

    Lets suppose HostA and HostB are standalone servers (no Active Directory)

    1.) Should i use "standart" user for running Agent ?

    2.) Is this scenario with NT SERVICE\SqlServerAgent (without "standart/conventional" user) possible at all ?

    3.) What if there is also (default) instance on the HostB ?



    Monday, July 23, 2012 4:09 PM
  • Hallo Kosta,

    create a share on HostB and grant permissions on it.
    Due to the fact that you have no AD you won't be able to grant access to a service account from an "external" machine.

    You have to grant it to the local "everyone"

    The permissions for the Serviceaccount need to be handled as for a "normal" user.
    So if you are logged on to HostA and wants to have access to a non domain computer "HostB" you have to create a share, too :)


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Wednesday, July 25, 2012 10:13 AM
  • Thanks, looks like i got it
    Sunday, December 16, 2012 1:36 PM
  • Thanks this linked help me find way for my solution. User NT Service\MSSQL$Instance - assigning this to any folder - right click on folder - properties - security - edit - add - Location - <here select the topmost object in the hierarchy> - Type in Object Name box NT Service\MSSQL$InstanceName and click Check Names

    This worked out for me.

    Ketan.

    Friday, March 21, 2014 11:34 AM
  • HOLY SMOKES!  I had NO idea the CHECK NAMES button worked that way.  It actually added an account (group) that I couldn't browse for!  Just so you know, I'm doing something different than this thread is describing, that's why it added the account in my situation.  Thanks for spelling out these steps, Rick Byham.  I always thought that the Check Names button was just an unnecessary (antiquated) button.  Turns out it does more than browsing!  I feel like such a novice.

    • Edited by thisisfutile1 Friday, October 24, 2014 3:38 PM Clarification
    Friday, October 24, 2014 3:35 PM
  • Hi, I accidently deleted the NT SERVICE accounts for SQL. Is there a way to add them back? I can't seem to get SQL applications installed without them? Thx.
    Tuesday, November 10, 2015 5:26 PM
  • How SQL Server uses the service accounts varies by version. What version are you using?

    SQL Server setup usually creates the service accounts that it uses so I'm not sure what you mean. Is setup giving you an error? What error?

    (By the way, you really should start a new thread for a new issue. This thread is marked as answered, so many people will not read it, so you are limiting the number of people who can help you.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by corbra Wednesday, July 20, 2016 10:41 AM
    Thursday, November 12, 2015 4:43 PM
  • Hi Rick,

    I've tried the above solution  but it still didn't work.

    Am I missing something?

    I'm running a vm setup with windows 2012 r2 with sql server 2012 r2 express tryin to install dpm 2012 r2 but can't make the sql agent to start up.

    I've added the above accounts to the security permissions of the entire sql installation folder and gave them full control and than tried to add the same accounts to the administrator group, the SQL agent service still won't start.

    Any advice would be appreciated, it also happened before with an SQL 2008 express installation.

    Monday, December 14, 2015 11:11 AM
  • Thanks for detailed answer.

    But when I try to add, "Name Not Found" appears. Is it about my permissions?

    Tuesday, December 4, 2018 8:58 AM