SSIS Package runs on the server but not as a scheduled job (SQL Agent)

3
votes

I have scheduled my SSIS package using SQL Server Agent Jobs. If I right click on the package and choose Execute, it does run successfully. However, when I set up the job and try to run it I get errors related to user login. I'm reading about proxy users and other option but it doesn't seem to work.

The error:

FullSnapshot:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'DOMAIN\SERVER$'.".

The server is "SERVER" and the package is grabing data from other server into "SERVER".

What might be the possible cause for this error? Please give your suggestions. Please let me know if you need more information on that.

Thanks in advance

I guess the question is how to give permissions to the service account for SQL Server Agent on SERVER to access the second server. Because when I run different package that doesn't need to access external SQL server it works fine

1
sqlsql-serverssis

1 Answers

9
votes

When you right click and execute the package executes with your credentials. When it's executing from the server, it's using the service account for your SQL Server Agent. In this case, your Agent service appears to be an automatically generated system account which does not have access to do everything the package requires.

You will either have to set up a proxy user, or change your agent service account.

This link from a quick googling looks to provide a good walkthrough: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/