locked
Why NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT are flagged as Windows groups on both sys.syslogins and sys.server_principals RRS feed

  • Question

  • Why NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT are flagged as Windows groups on both sys.syslogins and sys.server_principals?
     
    The question is relevant, at least, for xp_logininfo, which will return an error if executed with one of these two logins, and with the “members” option:
     
     
    exec xp_logininfo 'NT SERVICE\MSSQLSERVER', 'members'
     
    Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42
    Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQLSERVER’, error code 0x8ac.
    Wednesday, February 5, 2014 8:57 PM

Answers

  • Think of these accounts as the identity of a local Windows Service, regardless of what account is used to start the service.  So your SQL Agent Service Account might be Bob, but you don't have to make Bob a sysadmin.  When the service is started with Bob's credentials it has an additional identity 'NT SERVICE\SQLSERVERAGENT' that has sysadmin privilges.

    And they are correctly listed as WINDOWS LOGINs in sys.server_principals, at least on my system.

    name                        type_desc
    --------------------------- ---------------
    NT SERVICE\SQLWriter        WINDOWS_LOGIN
    NT SERVICE\Winmgmt          WINDOWS_LOGIN
    NT Service\MSSQLSERVER      WINDOWS_LOGIN
    NT SERVICE\SQLSERVERAGENT   WINDOWS_LOGIN
    
    
    (5 row(s) affected)
    
    
    --------------------------------------------------------------------------------
    Microsoft SQL Server 2012 (SP1) - 11.0.3373.0 (X64) 
    	Jun 29 2013 21:15:12 
    	Copyright (c) Microsoft Corporation
    	Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, February 5, 2014 9:37 PM
  • Actually assigning the permissions to the Per-Service SID started with SQL 2008 on Vista and above.

    "For stand-alone instances of SQL Server on Windows Vista and on Windows Server 2008 operating systems, service SIDs are added to the service group, and the service SID for SQL Server Engine and SQL Server Agent is added as a login to the sysadmin server role."

    http://support.microsoft.com/kb/2620201

    For FCI's there was still an option to use groups.

    What's new in SQL 2012 is that SQL Server uses a virtual account as the Service Account by default.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Sofiya Li Monday, February 17, 2014 4:02 AM
    Wednesday, February 5, 2014 11:06 PM

All replies

  • That's because 'NT SERVICE\MSSQLSERVER' isn't a group (though it might appear to be one), so you can't add users to it.

    It what's called a Managed Service Account which basically makes it easier segregate services from each other, while not needing to manually manage the passwords on multiple service accounts. See http://technet.microsoft.com/en-us/library/dd560633(v=ws.10).aspx which gives a brief description of them. I believe they initially appeared in SQL 2008, but I think made themselves more prominent in SQL 2012 (certainly I noticed them more in Server 2012).

    Wednesday, February 5, 2014 9:30 PM
  • Think of these accounts as the identity of a local Windows Service, regardless of what account is used to start the service.  So your SQL Agent Service Account might be Bob, but you don't have to make Bob a sysadmin.  When the service is started with Bob's credentials it has an additional identity 'NT SERVICE\SQLSERVERAGENT' that has sysadmin privilges.

    And they are correctly listed as WINDOWS LOGINs in sys.server_principals, at least on my system.

    name                        type_desc
    --------------------------- ---------------
    NT SERVICE\SQLWriter        WINDOWS_LOGIN
    NT SERVICE\Winmgmt          WINDOWS_LOGIN
    NT Service\MSSQLSERVER      WINDOWS_LOGIN
    NT SERVICE\SQLSERVERAGENT   WINDOWS_LOGIN
    
    
    (5 row(s) affected)
    
    
    --------------------------------------------------------------------------------
    Microsoft SQL Server 2012 (SP1) - 11.0.3373.0 (X64) 
    	Jun 29 2013 21:15:12 
    	Copyright (c) Microsoft Corporation
    	Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, February 5, 2014 9:37 PM
  • What David and Keith said, is the case for SQL Server 2012

    In SQL Server 2005-2008R2, SQL Server used indeed Windows Groups for assigning permissions on operating system level.

    Frankly I do not remember, why exactly that error comes up when running xp_logininfo

    But I can confirm your observation.

    Edit: I found an old blog about this: http://danieladeniji.wordpress.com/2010/09/18/ms-sql-server-issue-xp_logininfo-fails-when-ms-sql-server-is-running-as-localsystemnetwork-services

    So check what User the Service is using for Log On


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com


    Wednesday, February 5, 2014 10:55 PM
  • Actually assigning the permissions to the Per-Service SID started with SQL 2008 on Vista and above.

    "For stand-alone instances of SQL Server on Windows Vista and on Windows Server 2008 operating systems, service SIDs are added to the service group, and the service SID for SQL Server Engine and SQL Server Agent is added as a login to the sysadmin server role."

    http://support.microsoft.com/kb/2620201

    For FCI's there was still an option to use groups.

    What's new in SQL 2012 is that SQL Server uses a virtual account as the Service Account by default.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Sofiya Li Monday, February 17, 2014 4:02 AM
    Wednesday, February 5, 2014 11:06 PM
  • Actually assigning the permissions to the Per-Service SID started with SQL 2008 on Vista and above.

    "For stand-alone instances of SQL Server on Windows Vista and on Windows Server 2008 operating systems, service SIDs are added to the service group, and the service SID for SQL Server Engine and SQL Server Agent is added as a login to the sysadmin server role."

    ...

    You are right, thanks.

    I already had the feeling, that I missed something and something die chance even before 2012.
    But the thing is: even under 2008 with Service SIDs, we also do have the Windows Groups.

    I perfectly remember my confusion about this and at PASS Summit 2009 I think it was, when I inquired about it, Il-Sung Lee had a nice diagram that showed and explained that change - unfortunately I never saw that diagram again and forgot the exact interrelation details :/


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Wednesday, February 5, 2014 11:14 PM
  • Thank you all of you for your answers, but my question is rather why those logins are flagged as Windows group (type = ’G’ on sys.server_principals and isntgroup = 1 on sys.syslogins, respectively), if, and your answers point to that, they are NOT Windows groups.

    I think is better to clarify with an example: in order to use xp_logininfo with the “members” option only for Windows group, one will filter logins by “type = ’G’” or by “isntgroup = 1” from either sys.server_principals or sys.syslogins, respectively.
     
    Any of those conditions will NOT filter out the NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT logins, and xp_logininfo will raise the above error for those, which is normal as they are not groups, and they cannot have members.

    Oh, and from one of my systems:

    SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition');

    10.50.1600.1    RTM    Enterprise Edition (64-bit)

    SELECT name,type,type_desc FROM master.sys.server_principals WHERE name LIKE 'NT SERVICE\%SQL%';

    NT SERVICE\SQLSERVERAGENT    G    WINDOWS_GROUP
    NT SERVICE\MSSQLSERVER    G    WINDOWS_GROUP

    SELECT name,isntgroup FROM master.sys.syslogins WHERE name LIKE 'NT SERVICE\%SQL%';

    NT SERVICE\SQLSERVERAGENT    1
    NT SERVICE\MSSQLSERVER    1
    • Edited by RobertNC Thursday, February 6, 2014 1:49 AM
    Thursday, February 6, 2014 1:21 AM
  • ...

    In SQL Server 2005-2008R2, SQL Server used indeed Windows Groups...

    But I can confirm your observation.

    Edit: I found an old blog about this: http://danieladeniji.wordpress.com/2010/09/18/ms-sql-server-issue-xp_logininfo-fails-when-ms-sql-server-is-running-as-localsystemnetwork-services

    ...

    Sorry for the mess, but this is still true, if you are under SQL 2008/R2

    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Thursday, February 6, 2014 10:00 AM