A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).
We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.
Thanks.
SSIS Amateur wrote:
A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).
We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.
Thanks.
I recommend setting ProtectionLevel=DontSaveSensitive and then using configurations to store your conenction strings. This is the "least hassle" choice in my experience.
-Jamie
|||
OK, we'll try that.
Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.
Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?
|||OK, we'll try that.
Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.
Seems strange but I can't explain it. Sorry.
Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?
Yes, it could. You should read the BOL topic on it.
-Jamie
|||
Jamie,
We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).
It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.
|||Jamie,
We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).
It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.
Good observation about the password. You have to manually edit the connection string yourself and put the password in there.
As you may know there was a big push in Microsoft not long ago around tightening security holes in its products and this is one of things that came out of that - it is considered a security risk for SSIS to store a password for you. Microsoft want you to responsible for creating security risks, not them.
-Jamie
|||Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing? Because I can't seem to be able to successfully store the password in the Package editor.|||
OK, I have seemed to have figured my problem out. For those of you who may be a little new to SSIS or new to configuration files, here's a run down on my problem and how I fixed it.
My company scheduling software was getting prompted for a password when a .bat file containing a DTExec command was initiated, this was due to connecting to AS400 servers while transferring data. So we had to look into a way to prevent the scheduled .bat file executions from prompting for a password. Here is the solution, in detail for those of you who are lacking in SSIS experience like myself, and have a similar situation. Some of it is very obvious to most, but was not to me, so I included everything I could think of. Thanks once again to Jamie for all the help.
1. In the package, change protection level to "DontSaveSensitive" (click anywhere in the control flow, off of a task and look at properties).
2. Create a package configuration to store connection string (there are many examples on the web, here is one).
http://msdn2.microsoft.com/en-us/library/ms140213.aspx
3. You will have to manually edit the resulting .dtsConfig to include passwords. Open in notepad and add passwords to each connection. It will be stored in the project folder.
4. Build the package and deploy it, save the configuration file where you feel best. I saved on folder a sql server itself (this is an option when deploying).
5. Change (or create) your .bat file to include the /CONF option, this will be the fully qualified path each of your .dtsConfig files.
6. Rerun you .bat to make sure it works properly.
Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing?
Yep. That's exactly what I mean.
No comments:
Post a Comment