none
GPO and Service SID? RRS feed

  • Question

  •  

    Hi, I'm a DBA installing SQL Server 2012.  SQL Server setup is creating service SIDs (e.g., NT SERVICE\MSSQLSERVER, NT SERVICE\MsDtsServer110, etc.) and granting them rights (e.g., SeServiceLogonRight, SeAssignPrimaryTokenPrivilege, etc.). 

    Our GPO is removing rights from the service SIDs created by SQL setup.  We have been unable to add a service SID to GPO.  I think there is an error that the account does not exist.  We can add just the name (e.g., MSSQLSERVER, MsDtsServer110, etc.), but this does not seem to work as rights on the service SID are still removed. 

    We did add NT SERVICE\ALL SERVICES (no error) and grant it SeServiceLogonRight.  I think this covers all service SIDs.  This appears to be working; however, I’m reluctant to grant some of the other rights to all services using service SIDs. 

    Are only “well known” service SID values valid in GPO?  Is there any way to add a service SID such as "NT SERVICE\MsDtsServer110" into GPO?  Is there a best practice for handling service SIDs and group policy? 

    Thanks.


    Randy in Marin


    Wednesday, July 24, 2013 9:57 PM

All replies

  • Hi,

    We are doing some research and will reply soon.


    TechNet Subscriber Support |If you have any feedback on Technet forum, please contact tnmff@microsoft.com.

    Thursday, July 25, 2013 8:01 AM
    Moderator
  • what error do you get when you adding the service account? please provide screenshots. Thanks

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, July 25, 2013 10:46 AM
  • Hi Aaron, I will ask our admin tech for some screen shots. 

    Randy in Marin

    Thursday, July 25, 2013 3:39 PM
  • Here is the error. 


    Randy in Marin

    Thursday, July 25, 2013 3:55 PM
  • Please let me know the type of the account NT SERVICE\MsDtsServer110. domain account or local account on the SQL server.

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Friday, July 26, 2013 3:35 AM
  • It's a service SID and local by nature. 

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

    http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx 

    Below is from the "Virtual Accounts" item in the above link.

    Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup on Windows Server 2008 R2 or Windows 7, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. When specifying a virtual account to start SQL Server, leave the password blank. If the virtual account fails to register the Service Principal Name (SPN), register the SPN manually. For more information on registering a SPN manually, see Register a Service Principal Name for Kerberos Connections.

    Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.


    Randy in Marin


    Friday, July 26, 2013 8:46 PM
  • please try computernme\MsDtsServer110.

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, July 30, 2013 10:17 AM
  • Hi, unforunately that does not work.  There is an error that the account can't be validated.  It can't be validated against active directory or the local machine via GPO.  This is group policy, not local sercurity at the machine level.   

    Here are a few of the ways our admin tried to add the service SID into GPO.  Is there a different way we should be using?  

    Thanks.


    Randy in Marin

    Tuesday, July 30, 2013 5:17 PM
  • Perhaps I should verify the object type and location to use for a service SID? 

    Randy in Marin

    Tuesday, July 30, 2013 5:21 PM
  • if it is a local account on specific computer, I suggest you use local policy

    if the account exist on multiple computers with same account name, please try to type account name only. just like "MsDtsServer110", without domain name, computer name and "nt service"


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Saturday, August 3, 2013 1:01 PM
  • We tried that.  It was ignored.  I will ask that it be tried again to verify. 

    I looked for the account using powershell, but can't find it.  It does not appear as a local user or group. 

    get-wmiobject -class "Win32_UserAccount" -namespace "root\CIMV2" -filter 'LocalAccount = True'
    get-wmiobject -class "Win32_UserAccount" -namespace "root\CIMV2" -filter 'Domain = "ISTBMK8CDQYD1"'
    gwmi -class win32_account -Filter 'LocalAccount = True' | select Name, Caption, SIDType | ft -AutoSize 
    gwmi -class win32_account -Filter 'Domain = "ISTBMK8CDQYD1"' | select Name, Caption, SIDType | ft -AutoSize 

    I was able to find the SID and name in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\

    S-1-5-80-##########-##########-##########-##########-##########

    I checked a server for the same profile and found it had the same SID.  (I thought it would have been different.  Perhaps the SQL install is setting the SID?)  The SID stands out from the other SIDs because of the S-1-5-80 prefix. 

    • SID: S-1-5-80
      Name: NT Service
      Description: An NT Service account prefix

    So, the service sid is not a local account or a local group as far as I can tell.  Am I really the first to have this problem?  Is there no GPO guidance for a service SID?  

    Thanks.


    Randy in Marin

    Monday, August 5, 2013 3:00 AM
  • Yes, I think we should know the type of the account first, local account or domain account.

    If it is a local account, you should able to see it on the SQL server in compmgmt.msc > local users and groups > users

    If it is a domain account, we should able to find it in ADUC.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, August 7, 2013 3:31 PM
  • It is not listed anywhere I have been able to find it; however, it can be a member of a group.  When listed in a group, it has the same icon as a domain user or a builtin account like NT AUTHORITY\SYSTEM.  When I add SYSTEM, I need to use the local machine for the location.  So, this appears to be something along the lines of NT AUTHORITY.  Can NT AUTHORITY be added or located in AD?  If so, then perhaps that method would work for NT SERVICE.  Is there someplace NT AUTHORITY accounts are listed?  Perhaps I will find NT SERVICE accounts there? 


    Randy in Marin

    Wednesday, August 7, 2013 5:22 PM
  • NT AUTHORITY\SYSTEM

    means it is a local account, not domain account.

    if it is a local account, we should add it in GPO by name without domain prefix or computer name.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Saturday, August 10, 2013 6:15 AM
  • Try this,

    Assign log on as a service user rights to a local system account via GPO using WMI Filters

    http://me.go-unified.com/ssign-log-on-as-a-service-user-rights-to-a-local-system-account-via-gpo-using-wmi-filters/


    Devaraj G | Technical solution architect

    Saturday, August 10, 2013 9:52 AM
  • Thanks for the link Devaraj.  I probably can figure out a WMI query that returns true for the machines that need these rights.  I'm a little - actually, very - fuzzy about GPO.  Will the WMI query filter allow GPO be applied as normal with the exception of the adding just the few rights needed by a few local services? 

    Randy in Marin

    Monday, August 12, 2013 3:59 PM
  • Aaron, I've have not yet had the admin retry adding the account without "NT SERVICE\".  It could be added without error, but account did not show up in local policy settings.  But I should verify this. 


    Randy in Marin

    Monday, August 12, 2013 4:03 PM
  • Yes. it will apply based on the filter applied.

    Devaraj G | Technical solution architect


    • Edited by Devaraj G Monday, August 12, 2013 6:08 PM content change
    Monday, August 12, 2013 6:08 PM
  • Hi,

    Was a resolution for this ever found?

    I have added, for example MSSQLSERVER into the log on as a service option under user rights assignment. when a sql server is dropped into the ou this policy is applied to any sql service which runs as MSSQLSEVER gets a 7041 error 

    This service account does not have the required user right "Log on as a service."

    If I then run a secpol.msc on the machine I cannot see the MSSQLSERVER entry under log on as a service as i can do in the gpo settings.

    Can anyone help?

    Thanks

    Tuesday, January 28, 2014 3:56 PM
  • I've been waiting for a solution.  I was trying to follow security best practices when using the least privileged account.  However, it appears bad security practices are required in GPO to let it work.  We currently block policy on our OU for SQL Servers.  This is not an option for developer machines.  You could try to use "NT SERVICE\ALL SERVICES" for the rights required; however, I would think this defeats any security advantage of using the service sid.  I don't know.  Nobody seems to know.  It is the default on some installs, so it does seem like some guidance should be available. 

    I did submit a bug, but I just now realized I submitted it as a powershell bug.  So, it will be ignored as it's not a powershell bug. 

    https://connect.microsoft.com/PowerShell/feedback/details/796258/unable-to-set-policy-for-a-service-sid

    Anybody know where I can report this bug so that it is addressed?  I can't seem to find the right place in

    https://connect.microsoft.com/directory/non-feedback/


    Randy in Marin

    Tuesday, January 28, 2014 7:15 PM
  • I just spent some time struggling with this too, and settled on "NT SERVICE\ALL SERVICES" as good enough for the moment.  Maybe editing gpt.ini directly would have worked... it certainly seems like a missing feature in GPO management when we are able to address other non-SID-resolvable objects such as BUILTIN\Groups.

    As far as I know, Connect is the only place to quasi-report a bug (no guarantee it will be looked at) other than opening a support ticket.


    Steve Kradel, Zetetic LLC

    Tuesday, March 18, 2014 2:58 PM
  • I'm having our developers change the failing services accounts to local system for now.  I do wish this would get some attention and an official support policy of some sort.  The 0 vote status so far might be taken as a sign that this topic, while annoying, is not important enough to address. 

    Randy in Marin

    Wednesday, March 19, 2014 4:44 PM
  • I just tossed an upvote at the issue, although the fact that it's in the Powershell category (rather than AD / GPO / Windows Security) is somewhat of a deterrent from it getting any traction.  IMHO it is a GPMC bug because 2012 R2 GPMC should be smart enough not to insist on validating the un-validateable--at least there should be a control to let it pass.

    Why did you choose to run the process as SYSTEM rather than authorize ALL SERVICES to logon as a service?  SYSTEM seems to be a much greater risk than the latter approach...


    Steve Kradel, Zetetic LLC

    Wednesday, March 19, 2014 4:52 PM
  • Steve, thanks for the upvote.  I missed the alert for your post.  I did get around to looking up the defaults for non-virtual service accounts and we are using the local service account. 

    I don't know how to report a GPMC bug.  I looked for the right place to report the bug at https://connect.microsoft.com/directory/accepting-bugs/servers/.  Perhaps the moderator can help with this?   

    Anyway, I have reluctantly concluded that virtual accounts are not ready yet.  I really want to be wrong about this because I like virtual accounts.  It's a great feature.  I know that the a lot of effort was put into providing the option of using virtual accounts and that they are now the default for SQL Server.  However, I think they are a security risk.  Yes, it is a best practice to isolate the services.  However, granting all virtual accounts the same rights or blocking policy to allow the rights to be retained is a bigger risk.  It's been almost year now without any clear guidance. 


    Randy in Marin

    Thursday, June 26, 2014 7:50 PM
  • I've been looking at this again.  It appears that the virtual account SIDs are not unique across machines.  For example, if 2 SQL Servers both have a dev instance, the SID of NT SERVER\MSSQL$DEV on both is the same.  (I checked this on two machines not in the same domain using "SC SHOWSID MSSQL$DEV".)  Perhaps a well know SID pattern is in the making? 

    Can we use the SID in group policy rather than "NT SERVER\MSSQL$DEV" to customize policy for the services? 


    Randy in Marin

    Wednesday, September 24, 2014 6:36 PM
  • > I've been looking at this again.  It appears that the virtual account
    > SIDs are not unique across machines.
     That's correct. The service SID is simply derived from the service name.
     
    > Can we use the SID in group policy rather than "NT SERVER\MSSQL$DEV" to
    > customize policy for the services?
     
    In firewall rules, you can. Other GP settings I don't know.
     

    Martin

    Mal ein GUTES Buch über GPOs lesen?

    NO THEY ARE NOT EVIL, if you know what you are doing: Good or bad GPOs?
    And if IT bothers me - coke bottle design refreshment :))
    Thursday, September 25, 2014 9:13 AM
  • I know that this post is from 2013, but the problem persists… I’ve found it yesterday. And I realized that this is a generic problem, related to the startup of certain services who need virtual o bultin accounts.

    I had these errors starting sql services, moving virtual machines, changing backup users… all related with the lack of certain rights in each local machine (logon as a service, etc…).

    You cannot add this users (for example NT Service\MSSQL$SQLEXPRESS in the error with sql) from the domain controller when you try to modify the domain policy and assign for example “logon as a service” right… gives the error “the account could no be validated”. This is because the domain controller don’t know about these users accounts because it didn’t have sql, hyper.v, etc… roles, which corresponds with each error.

    And you cannot change the local policy in sql server because is managed from the domain.

    All you have to do is install the mmc pluggin to manage domain policies in the sql server and then you can add this account.

    Friday, November 28, 2014 1:47 PM
  • I know that this post is from 2013, but the problem persists… I’ve found it yesterday. And I realized that this is a generic problem, related to the startup of certain services who need virtual o bultin accounts.

    I had these errors starting sql services, moving virtual machines, changing backup users… all related with the lack of certain rights in each local machine (logon as a service, etc…).

    You cannot add this users (for example NT Service\MSSQL$SQLEXPRESS in the error with sql) from the domain controller when you try to modify the domain policy and assign for example “logon as a service” right… gives the error “the account could no be validated”. This is because the domain controller don’t know about these users accounts because it didn’t have sql, hyper.v, etc… roles, which corresponds with each error.

    And you cannot change the local policy in sql server because is managed from the domain.

    All you have to do is install the mmc pluggin to manage domain policies in the sql server and then you can add this account.

    Friday, November 28, 2014 1:48 PM
  • Hi, I ran across this problem today after having installed MS SQL Server 2012 Express. Jordi Fresquet have more or less described the answer, but it isn't marked as such yet.

    The solution to the problem is to install the Group Policy Management Feature on the server with SQL Server installed. If you start GPMC from the server, and edit the GPO in question, you are able to resolve the virtual account NT SERVICE\MSSQL$SQLEXPRESS (or whatever it is in your case) when adding it to the User Right Log on as a service.

    This user will appear as a long SID when the GPO settings are viewed on a domain controller, but since the SID is generated based on the name of the virtual account it is not unique (source: http://support.microsoft.com/kb/2620201). Thus, for every server affected by the GPO the virtual account you specified will get the Log on as a service permission.

    This is more or less the same process you have to go through when configuring Windows Services that are unique to a specific server. They will not be accessible when editing the GPO on a domain controller, but they are available when editing the GPO through GPMC on the server where the services exist.

    Tuesday, February 3, 2015 3:40 PM
  • Thanks Jordi.  Sorry to get back so late on this.  I must have missed the alerts.  I'll open a ticket to have this done.  If it works, I'll come back and mark it as an answer. 

    Randy in Marin

    Tuesday, August 11, 2015 5:28 PM
  • Øystein, thanks for the feedback.  I was looking at this again because we a SQL Server 2005 agent crash with a .net runtime error.  (Our 2005 will be soon gone.)  I was wondering if it was because of a service SID vs a service account permission issue. 

    Randy in Marin

    Tuesday, August 11, 2015 5:32 PM
  • We also run into this issue due to the way that we secure our systems. We have a base GPO that applies several settings, including locking down most of the Security Settings/Local Policies/User Rights Assignments.  When we have the SQL folks install different features on servers, we run into this constantly.  Adding "NT Authority" accounts, we don't have any issue with but "NT Service" prefixed accounts are a no-go for us as well.  Hoping someone has a better workaround than adding "NT SERVICE\ALL SERVICES".
    Thursday, October 1, 2015 4:40 PM
  • Install the Group Policy Management Console on the server with the local (NT SERVICE\*) accounts.  When editing the GPO, the MC will be able to validate the local accounts and will add them to the GPO without a problem.

    -Jeff

    Tuesday, October 4, 2016 9:30 PM
  • HI JAdams,

    Thanks for the answer, but if this is the solution, then for a environment where there are  100 + SQL Servers, we need to Install the Group Policy Management Console on all the 100 + servers and we need to edit GPO and add the specific "per-service SID" from the respective servers. Hoping that there might be a better workaround.

    Wednesday, May 16, 2018 6:38 AM
  • > Thanks for the answer, but if this is the solution, then for a environment where there are  100 + SQL Servers, we need to Install the Group Policy Management Console on all the 100 + servers and we need to edit GPO and add the specific "per-service SID" from the respective servers. Hoping that there might be a better workaround.

    You might re-think how to assign privileges... I did awhile ago: http://evilgpo.blogspot.de/2015/04/wer-bin-ich-und-was-darf-ich.html

    Basically, you grant the priv in question to a generic name (just type it in). Then on each affected server, you apply a GPO that uses GPP local users and groups to
    a) create a group with that name
    b) add the Account to that group (here, too, you can simply type the account name in, it doesn't matter if it exists or not)

    Wednesday, May 16, 2018 11:29 AM
  • I figured out a workaround for this, but it's kind of wonky:

    1) create a group policy with an entry in the the logon as a service setting

    2) backup up the group policy

    3) find the files for the backup and locate the GptTmpl.inf file under <GPO GUID>}\DomainSysvol\GPO\Machine\microsoft\windows nt\SecEdit

    4) Edit the file in a text editor

    5) locate the line starting SeServiceLogonRight

    6) modify the line, which is a comma-delimited list of security IDs (make sure to include an asterisk in front of each SID)

    7) import the settings into the GPO you wish to apply

    Monday, April 29, 2019 7:09 PM