Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tips for avoiding stored passwords with DontSaveSensitive? Expand / Collapse
Author
Message
Posted Tuesday, November 5, 2013 9:24 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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.
Post #1511534
Posted Tuesday, November 5, 2013 9:51 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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
Post #1511547
Posted Wednesday, November 6, 2013 12:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1511736
Posted Wednesday, November 6, 2013 7:05 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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?



Post #1511871
Posted Wednesday, November 6, 2013 7:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1511887
Posted Wednesday, November 6, 2013 8:58 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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 ....
Post #1511916
Posted Wednesday, November 6, 2013 10:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1511948
Posted Wednesday, November 6, 2013 11:08 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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.

Post #1511962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse