Sql-server – SQL server agent job to execute SSIS package fails, package succeds if run manually

sql serversql-server-2012ssis

I've got a SSIS package installed on a SQL server (SQL Server 2012). It's fairly simple and just fetches data from a remote data source and adds it into a local table. The remote connection string is using SQL server authentication, while the local connection is using Windows auth. The remote connection password is protected, and the package was imported setting the protection level to Rely on server storage and roles for access control.

If I run the SSIS package manually, it works. If I run it from the command line using dtexec, it works. If I use runas to switch to the domain account that the SQL server agent is running under, and then run the package using dtexec, it works. If I create a SQL Agent job with a single step to run the package, it fails, providing very little detail as to what's going on. I'm guessing it's not able to get the password to log into the remote SQL server, because it fails very quickly. Also, if I tick 'log to table' and view the resulting file, I get the following:

Description: ADO NET Source has failed to acquire the connection {0D8F2CD4-A763-4AEB-8B52-B8FAE0621ED3} with the following error message: "Login failed for user 'username'.".

If I try to add the password in the connection string manually under data sources in the job step dialog, it refuses to save it, always seeming to remove the 'password' bit of the connection string.

I thought that SQL server agent jobs always ran under the context of the account which the SQL server agent is running under. This account is a sysadmin on the local SQL server, and the package works using dtexec under that account, so why would it fail when trying to run as an agent job?

Best Answer

I had this same issue. After some research and convincing the server I didn't have control over to add a Windows Authenticated account to there location and some insight from the below URL, I got it working:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3f51de7b-52c2-471e-811b-d056f707f5bd/what-is-the-difference-between-job-owner-run-as-and-log-on-to-the-server-acounts-in?forum=sqlintegrationservices

Our environment

  • SQL Server 2012 hosting Integration Services Catalog which is bringing together data from various SQL Server versions across a different domain.
  • Two domains, which trust each other
    • Domain A
      • SSIS catalog in domain A, SQL Server Agent job running SSIS package on domain A.
    • Domain B
      • SSIS package pulls data from databases on domain B using Windows Authentication connections setup in the SSIS package. The package runs fine.
      • One service account that belongs to Domain B has access to all the data sources in question.

Solution

This gave me an understanding of the difference in how they run for a SQL Server agent job. Given the fact that my SSIS package is reaching out to a remote server on a different domain and that my error was pertaining to the "Run As" user account in the step, I decided to create another "Run As" account. This is also known as a proxy (SQL Server Agent > Proxies > SSIS Package Execution).

Before creating the proxy, I had to setup a credential. Here the steps that solved this issue for me:

All steps assume your SSIS package is loaded into the Integration Services Catalog and the SQL Server agent job is created with a step to run the package from the SSIS Catalog (Package Source).

  1. Create a credential (Security > Credentials). Use the identity in the credential as the service account mentioned above.
  2. Create a SSIS package execution proxy (SQL Server Agent > Proxies > SSIS Package Execution). To help understand everything, I gave it the same name as the service account.
  3. Go to the SQL Server agent job and then the step. Change the run as from SQL Server Agent Service account to your newly created proxy account. Press OK and OK.
  4. Run the job to test.

This worked for our situation and allowed our SSIS package to run as scheduled and traverse across a domain to a different SQL Server box and pull data.

Related Topic