none
Who runs SSIS packages and can access databases, cubes and ETL

    Question

  • We are having a debate about what account runs SSIS packages. We have our DW and Cubes on server A.  Our ETL on server B.  Source data is on Server S.  Our ETL that moves data from S to A.

    We could run jobs on A or B, that is also part of the debate, but to me which server runs the job doesn't matter.  Currently, we have it set up is the SQL Server Agent account on B is running the ETL.  Then we give read /write or dbo owner access to SQL Server Agent Account B to our database and cube on A and our source database on C.

    To me is seems weird to give a Service account from server B a login on A and S and to database on those servers.

    We also talked about creating a Windows domain account that would have access every where.

    We talked about a SQL account on one of the servers and creating the same account on all three servers.

    We talked about a proxy account...   but then what account would the proxy get its credentials from.

    Please point out the obvious bad choices and maybe a best practice if you knew about one.  Or maybe it doesn't matter.

    Any links to articles would also be appreciated, my google searches havn't turned up too much good info so far.

    Thanks,

    Mike

    jeudi 12 avril 2018 20:24

Toutes les réponses

  • Hi Mike,

    Personally, a proxy account is best choice to run SQL Agent job, most of reasons is security. A SQL Server Agent proxy account defines a security context in which a job step can run, each proxy corresponds to a security credential.

    See: SQL Server DBA Tip: Credentials and Proxies

    Then the proxy account should have sufficient permission to access source and destination component in SSIS package and also have execution permission.

    In your scenario, the proxy account (say a domain account) need to have access to source data on server S, and have read permission for DW on Sever A.

    Running a SSIS Package from SQL Server Agent Using a Proxy Account

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    vendredi 13 avril 2018 08:25
  • Thanks Pirlo, appreciate your response.  What I have never understood, is that the proxy gets its credentials from another account.  So why not just use that account.

    Then there is the account type ( that the proxies credentials come from)..   would it be a SQL server account, a SQL Service account or a Windows domain account.  I have my own ideas, but we are having a debate.

    I am going to read the articles you recommended, thank you.

    I hope to eventually to eventually post our conclusion and the reasoning behind it.  If anyone else has any ideas or can describe there set up, please do.

    Thanks again Pirlo

    vendredi 13 avril 2018 12:47
  • Hi Mike,

    -->> So why not just use that account.

    I guess you would like to configure the SQL Agent service account with other account (e.g. domain account) instead of proxy account which is same as the SQL Agent service account.

    Yes,  as a best practice, SQL Server Agent service account rights should be kept as low as possible to prevent exposing your system to security risks.

    In general, using a local user or domain user that is not a windows administrator is the best choice.

    See: Running SQL Server Agent with a least privilege service account

    In this scenario, of course you can configure the SQL Agent service account with the domain account, specially, if you have other packages requires different permission, I suggest the proxy account should be proper account so that you don't need to change the SQL Agent Service account.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    lundi 16 avril 2018 09:19