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
Answer
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.