ssis security short coming, major issues
Hello, I feel like ssis encryption model has a serious flaw. Especially when linked to SQL Agent jobs. I have posted and others have posted messages about this. Something is plain wrong with ssis encryption keys and password protection. Also, you do not have the choice not to protect the packages. In my case, protecting packages is completely useless. Here is the story. After this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=131340&SiteID=1&mode=1 I created config files for al my packages connections passswords. Now, by our IT Policy, I had to change again my password and of course, all packages now return multiple errors when I open them. Hopefully, the config file did its job and the packages are ran anyways by SQL Agent, however, having to manually retype and resave all packages not to have the errors is just a plain hassle. Not to speak about people not using the config files and the correct "Run As" sql agent account. I stress the fact that in a real world production environment all packages are driven by SQL Agent jobs and MUST run automatically. Here is the error I get after opening a package after changing my password: Error 1 Error loading Constants05.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:\projects\ssis packages\ssis constants\Constants05.dtsx 1 1 So Why is'nt this key automatically adjusted after Windows NT Domain password Change? How can I refresh the key, not to have to reype all the packages connections passwords and rebuilding, Checkin-in again all the stuff? I do not think the solution is "Use an application account which password never changes when you create your ssis packages" however at this time, this is the only solution I can think of. How do you guys deal with this problem? I still do not understand the ssis security model I feel it is diconnected from the reality and unpracticable in a production environment like mine. Thanks Philippe
January 7th, 2006 12:57am

When you set package protection mode to one of the user-based options, SSIS uses DPAPI to protect sensitive information. Changing domain password does not break this encryption and DPAPI takes case of key management (at least with default Windows GINA). Only resetting password (without entering old password) breaks the decryption. Of course, other users don't have access to your password. So if you schedule the package to run under different account, you can't use user-based protection mode. Also, if you use package on multiple machines you need to use roaming profile (DPAPI restriction). If any of these conditions can't be met, switch to password-based protection mode.
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2006 3:50pm

If you use a config file to supply all sensitive information, then you can dispense with encryption at the package level entirely. When developing or at least when deploying you need to ensure the package is set to not save sensitive information. The package can be freely distributed as it has no sensitive information, you have no key hassles or passwords to remember or manage. You just need to maintain your configuration store and ensure you packages use it correctly, in other words to get any sensitive information from it. You need to ensure the confirguration store is secured of course, but that is one store, or more likely one store per environment, but better than lots of packages. Perhaps save with user key should not be the default, but very often you just want to build a quick test package, and for that it is ideal. For production you need a different strategy, as is very often the case, and mine is above. SSIS security is good, because it gives you different options, but I can see why people get confused, I know I am/did!
January 7th, 2006 4:20pm

In case it makes any difference, I always use the strategy that Darren has explained here as well. If you follow best practice of keeping your packages in the dark (http://www.windowsitpro.com/SQLServer/Article/ArticleID/47688/SQLServer_47688.html) then you don't have to save any sensitive information in your packages. -Jamie
Free Windows Admin Tool Kit Click here and download it now
January 7th, 2006 10:38pm

This sounds like the best strategy so far. I will save all packages with "Do not save sensitive". This still may require to type the passwords when you want to work on the package, but this is a lesser issue than the one I was talking about. Security by default is a good thing, but security obsession and impossibility to elect not to use it is very restrictive.
January 7th, 2006 11:32pm

If you use SQL Server configurations, you don't need to ever type in passwords. The package will load the passwords from the configurations when you load them into the designer. Or, alternately, you can store them in registry configurations or secured folder shares in XML configurations. There are many options. There is only one option that we specifically prohibit and that is not using security. The risks are just too high. Sorry about the security obsession thing. These days you can't afford to be passive or even luke warm about security... K
Free Windows Admin Tool Kit Click here and download it now
January 8th, 2006 12:38am

Philippe wrote: This still may require to type the passwords when you want to work on the package, but this is a lesser issue than the one I was talking about. Without wishing to sound repetitive, if you use configurations you won't need to do this. -Jamie
January 8th, 2006 3:37am

You are right guys, I do not have to type a password anymore. I still have to make sure the directory wereI store my config files is secured. Right now the folder is accessible by admin, myselfand 1 ssis agent account and it contents something like this <?xml version="1.0"?><DTSConfiguration> <Configuration ConfiguredType="Property" Path="\Package.Connections[sudson.adonet].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=sudson;User ID=stealth;Password=notsecret;Persist Security Info=True;</ConfiguredValue></Configuration></DTSConfiguration> Even though the directory is protected, I still do not like the password thing being in clear.I guessI have to do some more homework to find a way not to have the password in clear. The learning curve for these security matters is pretty steep and the actual logging feature is not of a great help since nothing gets logged on prevalidation errors. I know MS know that and will improve the logging model. you posted in your blogs some ideas as how to workaround these issues. Now, I have got a 60GB,100 ssis packages testdatamart up and running automatically without errors for some time, I got all my packages re-written for SQL2005 and I love it despite these initial difficulties. users started to test it back in midd December. I am on my road to move to production for April 2006, the missing pieces in the puzzleare my OLAP custom access control model (More security, but business oriented this time) as well as a few cubes that need rewrite Philippe
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2006 4:29am

I'm not observing that "the package will load the passwords from the configurations when you load them into the designer." My package has an OLEDB connection manager, and my config file is set to store the connection string. First, when I enter the complete connection info in the connection properties dialog (including the password) in BIDS, the password is NOT included in the connection string that the config file stores. Second, if I modify the config file to add "Password=something" in the connection string,the passworddoes NOT get loaded when I open the package in BIDS. A separate but probably related problem is this: when I deploy the package, the installation wizard gives me the chance to change config file settings for the new environment. I change two: first, the path to a flat file destination, and the connection string to my OLEDB source (adding the "Password" section, which is still missing - see above). When I run the package in Management Studio (right-click -> Run Package), I observe in the "Connection Managers" section of the "Execute Package Utility" that while the updated path has been recognized (loaded from the config file) the updated connection string has NOT. The connection string is still missing the password, so package execution naturally fails. Sure, I can modify the connection string right there in the "Execute Package Utility", but who's going to do that when the package is running unattended under SQL Agent? Someone please explain how this is supposed to work. Thanks.
April 18th, 2006 7:10pm

Within visual studio I have a password stored in the ftp connection. Everything works fine. When I add a configuration to store the ServerPassword in Sql Server the package fails within visual studio with password not authorized. I have tried several different protection options, none work. I have even tried storing the password in a configuration file. That also fails. I am connecting to a unix ftp site so I even tried all caps in the password. Still does not work. As long as the password is stored in the ftp connection manager the package works fine. Any help would be greatly appreciated. thanks
Free Windows Admin Tool Kit Click here and download it now
April 24th, 2007 11:08pm

I resolved my problem from reading another post. You haveto manually enter the password into the configuration table. Now it works.
April 26th, 2007 8:10pm

There is another solution. Basically I got around this issue by initially saving the package using the "Rely on server storage and roles for access control", then, from that server directly, exporting back to the same server under a different folder using the encryption, and finally deleting the package that was initially loaded to the server or ssis package store. This works for both packages finally stored in the MSDB or File System and gives you a secured package that works directly on the server it is loaded to. I realize that it takes an extra step however it works, and is easier than setting up a configuration file. I am open to hearing the shortfalls to this method if there are any
Free Windows Admin Tool Kit Click here and download it now
August 2nd, 2007 9:30pm

Enigma, If this works for you and meets your requirements then I'd say it the right answer, but the obvious drawback for me is the bit about having passwords stored inside a package. If I change my windows password I do not expect to have to install an application again, so why should I have to change a SSIS package? I dislike encryption for this reason. For this reason it is a wrong solution in my eyes, and would introduces an unnecessary overhead and risk. Changing passwords should be easy, to ensure it is done on a regular basis for the obvious security reasons.
August 6th, 2007 12:42pm

Hello, I get this error when cluster fails over to the second node. I do not use configutation, but all packages reside on the SQL Server and user id is the same which is the Windows account the server and the agent run under. Why does it happen? thanks.
Free Windows Admin Tool Kit Click here and download it now
January 25th, 2008 10:16pm

A good example of a flaw in SSIS security is when the ProtectionLevel property is set to any of the "sensitive" options. To test how secure this security level was, I was able to gain entry into SSIS packages at work that other developers had created which used Protect Sensitive with Password option. Once in, I was able toretrieve SQL Server authentication information stored in package variables, create a connection in Management Studio, and view the entire database. You can also open the same kind of package in BIDS and change the protection level, or modify the information in the XML contained in the dtsx file. Microsoft advertises that "sensitive" information is controlled by Integration Services and may include data in both properties and variables. But developers don't have any way of tagging variables as sensitive. (see http://technet.microsoft.com/en-us/library/ms141747.aspx). I don't know what criteria SSIS uses to determine this, but I created a variable named "Password", and the data was still exposed. The configuration idea is a good one, but we can't use that at our shop. I implemented an scheme that encrypts sensitive data stored in package variables. The encrytion code is compiled separately in a signed assembly which lives in the GAC, and my packages reference them externally. Hopes this helps...
September 4th, 2008 6:43pm

There is no way for the package developer to flag properties as sensitive. Sensitive properties are explicitly marked a such by the component developer. The are generally write-only as well. This does mean that variables cannot be protected. It seems the flaw was with the package developers for allowing information to be persisted in clear text, rather than SSIS. I would suggest that the Encrypt all with password option would have been more appropriate if variables have to be used in this way.
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2008 3:20pm

There seems to be a problem where people seem to ignore a problem when proposing a solution for saving and utilizing oledb passwords - people are always saying to use configuration files, and save as DontSaveSensitive then modify the xml to add back the password. However, there is a big problem with this when using with Sql Agent in that Sql Agent will NOT use configuration files for packages that were saved with configuration files turned on! You must deploy them with it turned off in order for the job step to utilize them at run time. But if you do that, it will not read passwords. Passwords with DontSaveSensitive will only be utilized if deployed with configuration files turned on and the password in the config file. Then the password is basically hard coded into the package. But then your job won't use the config files - for anything! Any changes require redeploying your packages everytime. Its a catch 22.
January 22nd, 2011 7:19pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics