September 27, 2010 at 1:42 am
Hi,
I have a simple package which reads from SQL Server and loads into a flatfile.
The OLE DB source connection uses SQL authetication.
I set up package configurations with XML to store the credentials of the OLE DB connection.
The value for the password is empty the first time, but when I put the password in the config file, my package runs without any problem.
If I do the same, but instead I store the configuration info in a SQL table (which is stored in the same DB as the one i'm going to read the data from), my package won't work stating that he can't login.
If I check the config table, everything is set-up correctly though (including the password).
Protection level is set to DonSaveSensitive, as it is supposed to be in this situation.
Anyone who know's a solution for this problem?
grts
September 28, 2010 at 9:55 am
anyone?
September 29, 2010 at 2:25 am
How does your package login to the database to get the config info?
Does it use SQL Server authentication?
If so then that password won't be saved either and you won't be able to log in.
You can get around this by using windows authentication (Nowadays I rarely see a need to use SQL Authentication) or;
You can still use SQL Server authentication, but save your pacakge to MSDB and use the 'rely on server storage' option to save your SQL password.
There are a few other options but thats a start.
September 29, 2010 at 2:51 am
that's the whole point.
If I use XML to store config to the database where my other configs are stored, then everything works fine
But if I store my "main" config also in a databse with sql authentication, then the package won't run.
This is my config:
IF
main config = XML (points to where config table is located)
source db config = sql config with sql authentication to source db
THEN everything runs fine under "don't save sensitive"
IF
main config = sql config with sql authentication (in other database the the on of the source config)
source db config = sql config with sql authentication to source db
THEN package won't run under "don't save sensitive"
So if I understand you correctly, using sql authentication and configurations in a sql server table, is not possible unless i store it with 'rely on server store'
But when is select this option, my package won't run in BIDS and gives me the following error:
Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
And I'm not even using an XML file to store my configs anymore.
September 29, 2010 at 3:00 am
A. Why won't you consider using Windows authentication rather than SQL authentication - this gets around the entire issue as no passwords are saved. There are also many other benefits
B. To get your package into MSDB, you need to
1.. Use 'User key protection' in your package and save to a DTSX file
2. Now go to SSMS, connect to the Integration services service and import your package. It should import your pacakge and preserve the password..... I think. I actually use a command line to get it into MSDB.
But seriously consider using windows authentication rather than SQL authentication to connect to your database as then there is no password to save.
September 29, 2010 at 3:04 am
If you really really want to continue using SQL Server authentication, you can also use
"Encrypt Sensitive With Password" protection level
You then need to provide a password at design time (when you open it in BIDS) and at run time (when you schedule it in SQL Agent or run it from the command line)
I also think this is an imperfect solution.
The "Encrypt Sensitive with user Key" option also works but the user who designs the package needs to be the same as the user who runs the package.
How are you running your package? As a job in SQL Agent?
September 29, 2010 at 3:29 am
nick.mcdermaid (9/29/2010)
A. Why won't you consider using Windows authentication rather than SQL authentication - this gets around the entire issue as no passwords are saved. There are also many other benefitsB. To get your package into MSDB, you need to
1.. Use 'User key protection' in your package and save to a DTSX file
2. Now go to SSMS, connect to the Integration services service and import your package. It should import your pacakge and preserve the password..... I think. I actually use a command line to get it into MSDB.
But seriously consider using windows authentication rather than SQL authentication to connect to your database as then there is no password to save.
I know it works perfectly with windows authentications.
But that decision is not our choice. We implement our BI solution at different customers, so I was wondering what would happen if there is no option for windows authentication with a certain customer.
The solution above seems good to me, but might not be ideal in a big deployment.
nick.mcdermaid (9/29/2010)
If you really really want to continue using SQL Server authentication, you can also use"Encrypt Sensitive With Password" protection level
You then need to provide a password at design time (when you open it in BIDS) and at run time (when you schedule it in SQL Agent or run it from the command line)
I also think this is an imperfect solution.
The "Encrypt Sensitive with user Key" option also works but the user who designs the package needs to be the same as the user who runs the package.
How are you running your package? As a job in SQL Agent?
I also know these things work, but again they are, as you say, an imperfect solution.
I was just wondering if there was a "perfect" solution for SQL Authentication, without having to use an XML file 🙂
But I guess that using 1 XML file will be the nearest to "perfect" solution for this problem.
It's just a little less secure...
Anyway, thanks for the input!
September 29, 2010 at 3:49 am
Hi
BaconPopper ,
Actually I dont know about SSIS as a master , just I' want to say That, To access any object in data base you need user & Password. And you say the password in a table which is object of the same database. haw can package find the password without going into database.
This is logic in my opinion only,
Ali
MCTS SQL Server2k8
September 29, 2010 at 4:29 am
I guess the thing is that each customer environment is different. For example there are two authentication modes in SQL: A. Windows only and B. SQL + Windows. A SQL Authentication solution will not work in a customer environment with windows only authentication.
Anyway: basically if you want to store a password in your DTSX, you need to
1. Save it using the "encrypt sensitive with user key" protection level
The password is now encrypted in the package and only the user that encrypted it can unencrypt it
2. Use DTUTIL.EXE (running as the same user) to transfer the .DSTX into the server (MSDB), using Server Storage protection level
Now you can run your package from the server and it will contain your password.
If you need to change the password you probably need to export it out to DTSX, change it and import it again.
It all seems overly complicated to me. I was under the impression that there was a 'encrypt with server key' protection level which would make it easier, but I haven't been able to find it.
September 29, 2010 at 4:38 am
Some other things you might want to keep in mind:
1. You cannot disable windows authentication into a SQL Server database. So clients would have to specifically not want to add a windows user as a login to the database in order for SQL authentication to be the only option.
2. Any security conscious client will not allow a password in clear text in a file. I have heard suggestions that the file be in a restricted secured area, but I just don't buy that. Again its an imperfect solution. Whatever the SSIS runs as needs to have access to that file and generally anyone could create a job to go copy that file somewhere unrestricted.
3. Having said all this, as soon as you need to connect to a database that does not provide windows authentication, then yes, you do need to come up with a solution. I think the best solution is to store passwords in a database configuration, but access that configuration via window authentication.
September 29, 2010 at 6:52 am
Maybe the most secure solution in this situation is to put the connectionstring of the config in an environment variable instead of an XML file.
Anyway, in most of our deployments this problem will not arise because we will be able to use Windows Authentication.
I was just checkin' out some best practice solutions in case only SQL Authentication is available.
I think I can conclude that there are different manners to utilize SQL Authentication, but that they are less secure/'perfect'.
September 29, 2010 at 4:15 pm
... but its trivial enough to view an environment variable, or anything in the registry for that matter.
If you are considering having your own special database to hold all package configuration information (and I assume logging etc.) then I suggest its not a big deal to specify that your special config database must be accessed by a windows login.
There are many other options revolving around using the "user key" protection level, then using an appropriate account when running your package.
Thats a few options anyway.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply