Sensitive passwords in ADF Integration Services not resolved

0

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:

  • Connect with SQL Server Management Studio and query the SRC1 and SRC2 databases;
  • Have SSDT connect to the SRC1 and SRC2 databases (connections were online)),it could even can show a preview of the data;
  • Execute packages from the SSISDB catalog including the sensitive project parameters.

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?

sql-server
azure
ssis
sql-server-data-tools
asked on Stack Overflow May 27, 2020 by Arno Tolmeijer

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0