CODEWITHSUNDEEP
sql-serverssissql-server-2016sql-agent-jobssis-2016
Michael
Michael

Reputation: 2657

SSIS Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection

I'm using MS SQL Server 2016. I have a SSIS package, with one execute SQL task. I can execute it fine with Visual Studio. When I deploy to the SSIS Catalog, I receive the below error:

Execute SQL Task: Error: Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection

Likewise, if I try to execute via a SQL Agent job I recieve the below:

Description: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. Source: .Net SqlClient Data Provider Started: 15:02:11 Finished: 15:02:12 Elapsed: 0.125 seconds. The package execution failed. The step failed.

What am I missing? Any help would be greatly appreciated.

The connection manager within SQL server is using ADO.net, not OLEDB

Upvotes: 5

Views: 30177

Answers (2)

Hadi
Hadi

Reputation: 37358

The issue cause is that in the connection manager you are using a windows integrated security which use the current windows user to establish the connection.

When the package is executed from SQL job it uses the SQL service account which don't have the permission to connect. In order to solve that you can define a proxy account and run the job as your windows user:

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

You're probably missing the fact that when you run a package in Visual Studio, it runs with YOUR credentials, and when you deploy it to SSIS and run it as a job, it runs under the system account that your SQL Agent uses.

So probably you haven't given the necessary permissions to the SQL Agent account.

Upvotes: 1

Related Questions