April 30, 2010 at 12:52 pm
I developed a test package which runs fine within BIDS. I deployed it to SQL Server and created a configuration file for it. ProtectionLevel is set to DontSaveSensitive. I set the configuration file to expose the Connection String for the one Connection Manager used in the package. Ideally, I would like to NOT have to keep the password inside the Configuration File, but pass it in during execution.
Once I run DTEXECUI, I provide all the server info and the configuration file that I created. I then go to the Connection Managers tab. I check the box for the one Connection Manager in the package. Before I do anything, the Connection String looks like this:
"Data Source=SERVER;User ID=USER;Provider=MSDAORA.1;Persist Security Info=True;"
Once I add the password, the Connection String then looks like this:
"Data Source=SERVER;User ID=USER;Password=pwd;Provider=MSDAORA.1;Persist Security Info=True;"
From what I've read, the connection string is formatted correctly after my edit.
However, when I hit the Execute button, I get "Error: Failed to acquire connection SERVER.Connection_name Connection may not be configured correctly or you may not have the right permissions on this connection".
Below that error I get another error which eventually ends with:
"ORA-01017 Invalid username/password;Logon denied"
I know for a fact that the username and password work. When I put the Password=pwd; into the Connection String inside of the Configuration file itself, the package executes without errors. Just for laughs, I entered two different passwords into both the DTEXEC connection string and the one in the configuration file. The package always uses the connection string in the configuration file. I even set DelayValidation to True in both the Connection Manager and the only task in the package, just to see if that would change which Connection string the package would use. It did not change a thing.
I'm sure the problem is my lack of knowledge in the SSIS area. I know the learning curve is pretty steep for SSIS and being a former DTS user doesn't seem to make things any easier. Surely I'm missing something here.
However, if this is working as designed, I guess I'm not sure what the point is in allowing the user to manually enter a Connection String within DTEXEC(UI).
May 6, 2010 at 8:24 pm
I think you have what I would call an order of operations problem.
When you change a connection or pass in a modified variable value it is set at the start of package execution BUT those same values can be updated from a config file. However the values are updated just after the package starts..
So here is what happens..
1. Values set at design-time are applied.
2. Values updated at run-time from the command line are applied.
3. Values updated from package configurations are applied.
I haven't seen anything in BOL or MSDN that explicitly says this, it is based on my personal experience.
CEWII
May 7, 2010 at 5:56 am
Edit: Misread original post. Deleting this comment and looking for a better solution.
May 7, 2010 at 6:02 am
gvaldridge (4/30/2010)
However, if this is working as designed, I guess I'm not sure what the point is in allowing the user to manually enter a Connection String within DTEXEC(UI).
Elliot is correct about the order in which things are evaluated. The point of the Connection String within the utility is the assumption, I believe, that you don't have a config file with that specific connection manager in it.
I bet if you remove the config file from the package (if the connection manager use string is the only object in it) and test the package in the utility, it will work fine.
The best way to deal with config files that contain passwords is to keep them on a restricted server share so that only the SQL accounts and the DBAs can see them. That's been my experience.
May 7, 2010 at 8:30 am
The password will still be visible to someone even if it is passed in during execution either throught the command line or the config files. If you do not think the option of securing the config files is adequate then your only other real choice is to use Active Directory authentication by configuring your service account with appropriate db rights and\or server rights.
May 7, 2010 at 8:40 am
rustman (5/7/2010)
The password will still be visible to someone even if it is passed in during execution either throught the command line or the config files.
Depends on how that someone is reading it. Someone who's reading communication between the package and server will absolutely be able to pick it up, IF they know how. It's clear text send.
However, if his concern is merely to keep people from opening the config file and reading it from there, a restricted share should work just fine.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply