SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER- have tried everything

Question

I have created a SSIS package and I am trying to run it locally. We use package configurations that point to sql tables and a XML config file. The package ran successfully for about a week, even when deployed to a SQL Server Agent Job in our STAGE environment.

Now, the only way I can get the package to run is by not using the Package Configurations and choosing EncryptSensitivewithPassword. If I change the package to DontSaveSensitive, I continuously get the error below:

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'Test_User'.". Error: 0xC020801C at AgentCompany, Lookup [37]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Test" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

It is so strange that about a week ago, this package ran fine with the configurations and the DontSaveSensitive Option.

I have updated the config file to ensure that it is establishing the connection string to the appropriate database. I also test the connectivity on the connection managers and they all test successfully.

I also double checked the SQL Database where the user is trying to connect to ensure that it has permissions there and it does.

I am very confused. Please Help!

Updating dtsconfig file Re-creating the connection managers Making some DFT task DelayValidation to true Changing the RunTime to 32 bit EncrpytPasswordSensitive with package configs removed---This works but this is not the standard at my company and this is not how I developed and tested the package before

Answer 1

Initially it was bit weird but following worked in my case: I faced same SSIS packages issue with integration services 16.0 i.e for SQL server 2022. I installed integration services 15.0 from SQL 2019 and retried same after installation is successful and it worked without any error and without doing additional changes. Later I uninstalled integration service 15.0 and checked again and it was working without error with having integration services 16.0 only.

Findings: When dtsx package is generated it uses SQL Server Native Client(SQLNCLI or SQLNCLI11) for connection string in the package and tries to connect with it and connection was failing in our case which results in the error mentioned above.

Root cause for connection failure in our case with SQL 2022:

Microsoft has removed SQL Server Native Client from SQL Server 2022 installation which led to this connection failure causing error during dtsx package generation. For more information: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-ver15

Why did it work on installing SQL 2019 and remain working even after uninstalling SQL 2019?

When we install SQL 2019, it installs SQL server native client as well along with it, so it made it working. But when we uninstall SQL 2019 then it only uninstalls SQL 2019 and keep SQL server native client installed so it remained working even after SQL 2019 is uninstalled.

How to get it fixed without installing SQL 2019?

Download & install only SQL server native client i.e sqlncli.ms https://www.microsoft.com/en-my/download/details.aspx?id=50402

Long term fix: Use MS OLE db provider for SQL server, for which we need to upgrade connection strings for the dtsx packages and verify backward compatibility for all the available SQL server versions. Ref: https://learn.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-sql-server?view=sql-server-ver15

Answer 2

When you open/run a package, an OnInformation event is fired that says something like

The package is attempting to configure from the XML file "c:\ssisdata\so_56776576.dtsconfig".

When Visual Studio/SSDT opens/runs a package which says it uses configuration but for reasons, cannot get them, you should then see messages like

Warning loading so_56776576.dtsx: Failure importing configuration file: "c:\ssisdata\so_56776576.dtsconfig"

and

Warning loading so_56776576.dtsx: The configuration file "c:\ssisdata\so_56776576.dtsconfig" cannot be found. Check the directory and file name.

and

Warning loading so_56776576.dtsx: Failed to load at least one of the configuration entries for the package. Check configuration entries for "Configuration 1" and previous warnings to see descriptions of which configuration failed.

If someone has manually edited the config file and broken the XML, you'd see a warning like

Cannot load the XML configuration file. The XML configuration file may be malformed or not valid

The important thing to note with regard to configuration - if a configuration cannot be found, SSIS will continue along with the design time values. That is why it is crucial to check the warnings emitted when your package runs. If you are running manually, ensure that you have /rep ew specified so you report Errors and Warnings.

Guesses as to root cause

The package has the protection level of EncryptSensitiveWithUserKey which means the AD credentials of the package creator are used to hash things that might have sensitive information in them. I could be using AD authentication in my connection string and specify that the connection should be trusted but that entire block is still going to get encrypted against my Active Directory account. When you come along and attempt to maintain the package, it's not going to be able to decrypt the sensitive data as you are not me.

The two ways around that are to use a shared key (EncryptSensitiveWithPassword/EncryptPackageWithPassword) which is cumbersome to deal with plus it goes against the whole spirit of secrecy since everyone knows the secret. The other approach as you've identified is DontSaveSensitive and that's my go to for all of this.

The problem to be overcome is that with DontSaveSensitive is that every time you save, SSIS is going to wipe out any knowledge of user name and password from places that might be holding on to it - like a connection manager. The 2005/2008 strategy to hedge against this was to use Configuration or explicit overrides at run time to supply user name and password. My typical approach was to use configuration based on a table instead of XML as I was better at securing sensitive data in a table than I was mucking with ACL on the file system. The other challenge we had with multiple developers and file based configuration was that either everyone had to set their file systems up the same (and we developers are unique rainbow snowflakes so that's unlikely) or we need to use a network shared file which is great until someone adds their own values to it and breaks it or removes your changes or any of a host of other challenges.