Can't configure OLE DB connection manager with sensitive parameter in SSIS 2012

I am trying to use sensitive parameters in SQL Server Integration Services 2012 to configure my data source connections. I am using OLE DB connections to a SQL Server 2012 database.

I found that you cannot use a sensitive parameter to assign a connection string or OLE DB connection manager password property. The connection string refuses to assign a value from the sensitive parameter.

The password property will accept the parameter as a value for the expression, but the connection doesn't actually work. I just get the "Login failed for username" message, so I am guessing that it is assigning the password incorrectly. When I change the password parameter to not be sensitive, the connection works correctly.

When assigning a password as a parameter, I must assign all other connection properties (username, servername, initialcatalouge, and I must also set certain values ​​in the connection string).

Is there a way to use sensitive parameters to customize my connection, or is this just a limitation of the OLE DB connection manager?

I would rather not encrypt the entire packet, because that means encrypting the entire solution. It is useful to be able to track changes in XML using version control, which would not be possible if the entire solution was encrypted. However, it is important that the database connection information is not stored in text format.

The only other solution I can think of is to move the connection manager out of the package and make it a project-level connection. I found that they preserve all their connection information without the need for runtime configuration and preserve the encrypted parts. From looking at the XML it seems like these are even sensitive fields.


source to share

All Articles