locked
SQL Server Agent - Failed Jobs for user NT SERVICE\SQLSERVERAGENT RRS feed

  • Question

  • I recently installed and configured SQL Server 2014 Developer Edition and have successfully imported data and created SSIS packages and jobs. I made sure to enable Named Pipes, NT SERVICE\SQLSERVERAGENT service is running. The failed jobs seem isolated to the jobs which contain steps to connect to other data sources like Teradata and Oracle. However, a job connecting to a Hive table works fine... I've manually run each step in the job successfully to confirm there are no issues with the connections; only when SQL Server Agent runs the job does it fail. Below is the error message.

    The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 
    {XXXXXXXXXXXXXXXXXXXXXXXXXXX}
     and APPID 
    {XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX}
     to the user NT SERVICE\SQLSERVERAGENT SID (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

    I went to Component Services > Computers > My Computer > COM+ Applications > System Applications > Roles > Server Application and added 3 users. Myself, and two SQL Server groups (SQL ServerMSA user and BrowserUser). Those two groups were the only ones that were available on the host machine related to SQL Server. I refreshed the server and ran the job again and received the same error above.

    I read about using proxies but I am not sure how to implement that alternative or if it will work. I created a proxy and gave it sysadmin permissions and selected to Run As that account but it still failed with the same message except instead of NT SERVICE\SQLSERVERAGENT not having permission it was my login.

    I appreciate any help and assistance with resolving this issue! Thanks in advance!

    Saturday, June 10, 2017 12:55 AM

Answers

  • Thank you for the responses. Here is what I did which has worked so far.

    Created a credential, SSISProxy, with the identity CORPPVT\71054678 (User)
    Created a Proxy account, SSISProxy
    Activate/enable subsystem SSIS Package Execution
    Add princinples, SQLAgentReaderRole, UserRole, and OperatorRole to Proxy

    Logged in as sysadmin

    Changed job owner to User: sa

    For each step:
    Jobs connected to Oracle need to be executed in 32 bit run time (Execution Options)
    Include Password in the Data Source connection string (Data sources)
    Run job as SSISProxy

    Job History still says:
    Message
    Executed as user: CORPPVT\71054678.

    But I'm not receiving the same errors so I think we are good to go.

    Thanks again!


    • Marked as answer by JohnMagrini Saturday, June 10, 2017 3:44 PM
    Saturday, June 10, 2017 3:44 PM

All replies

  • You do need a proxy that is domain account derived to the the SSIS job step under.

    Give that domain account access the needed sources.

    Do not give elevated privileges, only the minimal set of rights to succeed running the package.


    Arthur

    MyBlog


    Twitter

    Saturday, June 10, 2017 4:19 AM
  • Thank you for the reply. I used the post below to add a proxy; however now I am getting the same error with my User name instead of NT Service\SQLSERVERAGENT

    I gave the Proxy the Identity CORPPVT\71054678 which is my Windows login and also is listed as a sysadmin. I used my regular Windows password as well. I followed everything else in the blog post below exactly. Could it be because the Identity I'm using for the proxy credentials is the sysadmin?

    I also tried switching the owner of job to a different user that wasn't a sysadmin and that didn't work. I tried mapping the credentials to logins and that didn't work. I made sure the other user had access to the proxy as well as the Reader, user, and Operator SQL Agent roles.

    https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/


    

    Saturday, June 10, 2017 6:09 AM
  • Hi JohnMagrini,

    try use SQL job owner sysadmin and  proxy account will work.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Saturday, June 10, 2017 7:11 AM
  • Thank you for the responses. Here is what I did which has worked so far.

    Created a credential, SSISProxy, with the identity CORPPVT\71054678 (User)
    Created a Proxy account, SSISProxy
    Activate/enable subsystem SSIS Package Execution
    Add princinples, SQLAgentReaderRole, UserRole, and OperatorRole to Proxy

    Logged in as sysadmin

    Changed job owner to User: sa

    For each step:
    Jobs connected to Oracle need to be executed in 32 bit run time (Execution Options)
    Include Password in the Data Source connection string (Data sources)
    Run job as SSISProxy

    Job History still says:
    Message
    Executed as user: CORPPVT\71054678.

    But I'm not receiving the same errors so I think we are good to go.

    Thanks again!


    • Marked as answer by JohnMagrini Saturday, June 10, 2017 3:44 PM
    Saturday, June 10, 2017 3:44 PM