November 5, 2013 at 9:24 am
Hi all,
We have an in development data warehouse project in SSIS 2012 and I am trying to set up our practices for how to collaborate/source control/deploy packages.
One of our issues is that a key data source is an oracle database which we can't use windows authentication for. For now, our packages have been using "save sensitive data with user key", which is not ideal obviously for collaboration or scheduling. They have gotten around the scheduling issue by putting the password right in a connection string formula for now .... obvious plain text and not ideal.
What I have been trying to do is change packages to "don't save sensitive", and then parameterize the password for the connection (which I could then have passed in via environment after publishing to a catalog).
What I was expecting was that the parameter would not be saved with the project, and the parameter would need to be reentered when opening in SSDT. But once the parameter was provided, when executing the package it would use it for the connection.
What seems to happen instead is that the "don't save sensitive" setting keeps the password from going to the connection string at all, and I get a "null password given" error during validation when I try to execute.
Any idea what I'm doing wrong. We can implement the "encrypt sensitive data with password" idea for the team if absolutely necessary, but I'd rather avoid it if it can be done. My googling has sort of indicated this should be possible, but has not helped with troubleshooting.
November 5, 2013 at 9:51 am
Edit: it looks like when compiling, both project level connection managers and parameter files get saved to disk. Seems like there still isn't a way to pass these values in at runtime only (except for a command line overwriting the property perhaps).
Not to convenient .... I'll play around a bit longer but looks like I have to use encryptwithpassword.
Looks like the only alternative is to develop and test/debug in ssis with an encrypt setting on, and then change to dontsavesensitive when deploying/promoting. Pretty annoying
November 6, 2013 at 12:20 am
As you're on SSIS 2012, you should consider creating an 'environment' which contains your connection strings and use the 'Configure' option to map those connection strings to connection managers in the package - or even at the project level.
Here's a link to introduce you to the topic.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 6, 2013 at 7:05 am
Hi Phil,
I am using environments for when the project is published to the server (or will be, that is part of what I was setting up).
But when developing a package and debugging/testing I need to execute it from SSDT, obviously. And with dont save sensitive on, I can't even do that unless I hard code the connection string, or add the password to a file manually.
That is the part I'm kind of stuck on. I gather that with prior versions the answer was to create configurations, and that you could have a configuration in design, and different ones in places you deploy to. My understanding was that in 2012 you set up parameters for testing in design, and then mapped them to environments in deployment.
But if the system will neither save nor prompt for sensitive parameters in design, how do you test in design?
November 6, 2013 at 7:44 am
Nevyn (11/6/2013)
Hi Phil,I am using environments for when the project is published to the server (or will be, that is part of what I was setting up).
But when developing a package and debugging/testing I need to execute it from SSDT, obviously. And with dont save sensitive on, I can't even do that unless I hard code the connection string, or add the password to a file manually.
That is the part I'm kind of stuck on. I gather that with prior versions the answer was to create configurations, and that you could have a configuration in design, and different ones in places you deploy to. My understanding was that in 2012 you set up parameters for testing in design, and then mapped them to environments in deployment.
But if the system will neither save nor prompt for sensitive parameters in design, how do you test in design?
My apologies - I did not read your initial post carefully enough.
I have not used this, but maybe it will give you something to explore further. If you are editing a parameter (password, perhaps), you will see that there are three command icons - the rightmost being 'Add Parameters to Configurations'. These configurations seem to be available during development only, but I think that would suit you, based on your posts. Perhaps you can add a parameter to a configuration and then apply that configuration to your package - somehow.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 6, 2013 at 8:58 am
Thanks for the suggestion, but it appears to still not work.
This feature would work for testing different environments in SSDT (for example, a different server name to connect to in a parameter, and you test each server just by changing the active configuration).
But it looks like it has the same overriding issue. Those project configurations are in files, so you can't save a password in them without encrypting, I don't think.
I have noticed that package configurations are still available, so I suppose I could set those up, but I doubt that would be the preferred method. I think we are going to go with "EncryptSensitiveWithPassword" protection. Only downside is all the password prompts, but at least with project deployment it seems to prompt only once per project. We can work around that by organizing our solutions/projects to minimize prompts.
But if any microsoft developers are browsing the forum, a "prompt for sensitive parameters at runtime" option would be awesome to include with project configurations in a future release ....
November 6, 2013 at 10:29 am
Nevyn (11/6/2013)
Thanks for the suggestion, but it appears to still not work.This feature would work for testing different environments in SSDT (for example, a different server name to connect to in a parameter, and you test each server just by changing the active configuration).
But it looks like it has the same overriding issue. Those project configurations are in files, so you can't save a password in them without encrypting, I don't think.
I have noticed that package configurations are still available, so I suppose I could set those up, but I doubt that would be the preferred method. I think we are going to go with "EncryptSensitiveWithPassword" protection. Only downside is all the password prompts, but at least with project deployment it seems to prompt only once per project. We can work around that by organizing our solutions/projects to minimize prompts.
But if any microsoft developers are browsing the forum, a "prompt for sensitive parameters at runtime" option would be awesome to include with project configurations in a future release ....
I'm not sure it's any better, but I just created a one-package project with Don't Save Sensitive as the isolation level.
I then added an OLEDB connection manager with a username / password.
I added a string parameter to the package called password and set 'sensitive' to false for it. I set the value of this to the actual password for the connection - obviously.
I was then able to right-click/parameterise the connection manager and map the $Package:Password parameter to the password property of the connection manager.
I could save and run this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 6, 2013 at 11:08 am
Thanks for the effort, but I think you are right (its not better). What we had done before is set the connection string using an expression from a variable, which is basically the same thing. Both end up storing the password in clear text in a file.
February 28, 2019 at 3:23 am
I was having this exact issue & found changing debug option Run64BitRuntime = False resolved this issue and enabled my package to pickup the value of password stored in the param.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply