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

Question

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

Solution 1

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:

• Run an SSIS Package Under a Different Account
• Running a SSIS Package from SQL Server Agent Using a Proxy Account
• Executing a SSIS package as different user from SSISDB

Solution 2

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.

Comments

[Michael]

so if the SQL Agent is running as a system account, this system account needs access to the original database in the execute SQL task?

[Tab Alleman]

It needs access to whatever databases, folders, etc are needed by the package. You haven't shared your code, so I can't be more specific than that.

[Michael]

Hello - I'm using ADO not OLEDB connection manager if that helps?

[Hadi]

@Michael it is the same case. I removed the OLEDB word from my answer.

[J Weezy]

It is recommended to follow the concept of least privilege. Whatever account is running the package in production should only have the necessary permissions that are required to perform the specified set of tasks. In other words, do not grant administrator level privileges to a service account. Instead, GRANT specific permissions. docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/…