The setup of our company’s SSIS implementation is as follows:
On-premise source databases, VPN connection to Azure ADF, and VPN connection to Development Machine
Project parameters are used for setting up Project Connection Managers. During deployment to Azure, an SSISDB catalog Environment is being created and referenced by the SSIS project. All project parameters map to an environment variable. The SSIS project and all included packages have ProtectionLevel=DontSaveSensitive set. Connection Manager properties ConnectionString, ServerName, InitialCatalog, UserName and Password are parameterized, the latter is marked Sensitive.
The data provider is OLEDB (Native OLE DB\SQL Server Native Client 11.0). The connection string contains, apart from Data Source, User ID and Initial Catalog settings, the options “Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;”. On the Remote Development machines, in Visual Studio (SSDT), Configuration settings are created for local development (DevLocal), Development in Azure (Development) and Acceptance in Azure (Acceptance). The project parameters are set to the proper values for every configuration using the ‘Add Parameters to Configurations’ option in the Project Parameter editor.
Project Parameter Editor, third image button highlighted 'Add Parameters to Configurations'
In two situations, there is a problem when I want to execute the packages in the project. First, when I want to debug the package in Visual Studio, during the validation process, no connection could be established with the source database due to:
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'DatabaseUserName'.".
Then follows
[SRC REF [17]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SRC1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Set DelayValidation to True does not solve the issue. The second time this problem occurs, is when I want to run the packages from Azure Data Factory’s Integration Runtime. The same DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error message appears. When I mark the password property as non-sensitive, the problem goes away. Note that even in the marked-as-sensitive situation, on the Remote Development Machine I was able to:
How can I get to execute the packages correctly in ADF, also with password parameters being marked as sensitive? How can I use SSDT and debug packages when connections are using configurations and passwords are marked as sensitive?
User contributions licensed under CC BY-SA 3.0