none
SQL Agent Access Control List (ACL) permission (PowerShell)

    Question

  • BLUF:  How to set ACLs for SQL Agent and the MSDTS000 - concept of least permission -  is there a POWERSHELL for this somewhere in the Microsoft arsenal?

    DETAIL: Generally speaking, the Configuration Manager takes care of a good deal of permission aside from what is set in the local security policy, that said, there seems to be a lot missing in the SQL Server setup for setting up the ACLs properly.

    SQL allows the user to setup a service account and then SQL configures all the necessary ACLs during setup.  It does this for MSSQL$<instancename>, for SQLAgent$<instancename>, and for MSDTSServer000.  Additionally, on newer versions there is similar accounts setup for xxx$RSERVER.

    The problem arises when a domain service account rather than the MSSQL$ local virtual service runs SQL Server Agent or the MSDTS service is used for the service.  Given the SSIS legacy, a domain account is generally used to run the Integration Services / DTSX packages that operate between the OLTP and the datawarehouse server because it requires domain level permissions.  Using an over-privileged domain account for this purpose can lead to unknown vulnerabilities.

    ACL Examples: For example, SQL Agent needs access to the SQLAGENT.OUT and the ERRORLOG files.  Replacing it with a domain service account (assuming that we give it least permission) means that the SQLAGENT.OUT file must have an ACL for the domain service account that is running SQL Agent or it will fail to start.  The easy way to create this is to give read ACL to the ..\MSSQL\Log folder.  AN easier way, would of course be, if Microsoft set the installer up to designate a specific service account to run the Agent and the Integration Services and so on rather than set an account that has SA so it can be used as a service account after the fact.

    The Integration Services requires rights on the ..\000\DTS\Binn folder.  Same applied here.   And on more occasions that one, it seems there is always some other ACL that is missing here and there for these accounts.

    Given the nature of SQL and the need for the highest level of security, having the ability to assign a service account to a service during setup would make the process much simpler.  

    Is there a PowerShell in the Microsoft arsenal that handles this?

    (dropped in a connect suggestion if you would like to see this improved in future setup software for Sql Server  https://connect.microsoft.com/SQLServer/feedback/details/3118235)


    R, J






    • Edited by Crakdkorn Saturday, January 7, 2017 3:40 PM
    Saturday, January 7, 2017 3:22 PM

Answers

All replies

  • From the command line you can specify them according to this link 

    https://msdn.microsoft.com/en-us/library/ms144259.aspx?f=255&MSPPError=-2147217396#Accounts

    When you specify the account the setup program will grant them the rights they need.

    Does this answer your question?

    • Marked as answer by Crakdkorn Sunday, January 8, 2017 12:54 PM
    Saturday, January 7, 2017 4:34 PM
  • We discovered this trying to install with DSC PowerShell xSqlServer. It isn't as full featured as a silent install.  I'm trying to get documentation into our Atlassian on what's there and what's not.  I'm still hoping to hobble together a PowerShell command that will return a report of all the ACLs that are relevant to SQL Server as well as a list of principals those Access Control Lists provide.

    It is currently a requirement in the latest STIGs that this information be available. (SQL 2016).  It means either use third party software that is frequently unreliable in a government environment or write your own using PowerShell.  I believe the latter is my optimal choice here.


    R, J


    • Edited by Crakdkorn Monday, April 9, 2018 5:54 PM
    Monday, January 9, 2017 2:31 PM
  • I was hoping it wouldn't come to this. Our Security Technical Implementation Guide is requesting that we replace the accounts that have previously had these accounts with new dedicated accounts. What it means is that we may need to reinstall everything. I think the easier solution is to have a PowerShell that knows the Access Control List settings for these accounts and automates those permissions. I've read that the Configuration Manager will do this for you but I have little faith it gets it right - or even whether it is a myth.

    Example:

    MyDataEngineServiceAccount once controlled the three services - data engine, agent, and integration.  With the new guidelines, the permissions must be with MyAgentServiceAccount and MyIntegrationServicesServiceAccount.   Since MyDataEngineServicweAccount already has permissions everywhere, it is difficult to tell exactly which ones will be required.

    Worse, during the setup, this account was given permission (MyDataEngineServiceAccount) so we do not find the usual permissions that may have been assigned to SQLAgent$<myinstancename> and MSDTSServer130.   I haven't seen a listing of the ACLs required for these accounts.   Does Microsoft have one available?   Reinstallation is not the preferred option.

    Just as an aside, it appears that the DTS account needs only the same permissions as the user does; hopefully for at least this one there are no exceptions.   The Agent needs access to the SQLAgent file - needs to own that file.   There may be some exceptions.  I'm hoping there is a list.


    R, J


    • Edited by Crakdkorn Thursday, July 5, 2018 6:13 PM
    Thursday, July 5, 2018 6:03 PM
  • Just a bit clarification:

    the Configuration Manager will set the necessary permissions for the SQL Server Services (+Agent etc) Accounts based on what is necessary for SQL Server itself to run (those are documented in BOL for reference)

    it will NOT keep track of whatever custom permissions that may have been granted like access to certain folders

    Those should be documented in a Changelog as a Best Practice

    I do not have a script for those at hand, but I am sure a google search will bring up something. Besides ACLs also Permissions granted by Policies should be taken into consideration.

    The SQL Server Agent Account sometimes needs adjustment if you use a customized folder structure for it's logs (as I would even recommend in general)


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Friday, July 6, 2018 3:09 AM