SQL Agent Job running SSIS package using SQL Authentication

0
votes

I am running SQL Server 2014

I am trying to set up a scheduled SQL Agent job that runs an SSIS package and I need it to use a SQL Authentication login in order to access all the applications/servers the SSIS package accesses.

I have created a Credential that is mapped to the SQL Authentication login and connected it to a Proxy which the SQL Agent runs as when accessing the SSIS package, however, the job fails with an error message:

Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1). The step failed.

I have confirmed that:

  • I can access all servers/applications using the SQL Authentication account
  • The SSIS package connections are configured for SQL Authentication
  • I can run the SSIS package manually using the SQL Authentication fine
  • All accessed SQL Server Databases are configured for both Windows AD and SQL authentication login

When I create the SQL Server Agent Job, the step that runs the SSIS package has an option for the SQL Server Authentication login but the option is greyed out and I cannot select it:

enter image description here

I feel like I am missing a very obvious step but it's eluding me, any assistance will be appreciated.

1
sql-serverssis
SSIS catalog can only be acceded by windows accounts, that's why the option is disabled. – Oscar
Edit the job, edit the step, click on advanced, at the bottom you will see the "Run as User", click the "..." and select the user account that you want. SQL or Windows. – Chuck

1 Answers

0
votes

@oscar and @chuck are both correct SQL Agent jobs running SSIS packages cannot run in SQL Authentication mode they have to use Windows Authentication. The user that runs the agent job unless you specify otherwise is the SQL Agent service account. It is that service account and the SQL Service account on the server you are running it on that will need certain permissions. However, you can change which account it executes as per chuck's note. Of course depending on who many different servers you are passing credentials and what is being accessed you can also get into a kerberos double hop problem...... Trust me solving that last part not so fun but still doable.

However the thing is if you must use SQL Authentication you don't need a windows user at all. you can put the SQL Authentication credentials in your connection strings of the connection managers in SSIS package. The SQL Agent service account can handle any file access issues for you and your connection strings can pass the sql credentials. no double hop problem and easy. If you are worried about embedding credentials you should use a less privileged sql account but you can also encrypt them and store them with the package. There are also several ways of dynamically getting them from an encrypted state.