tag:blogger.com,1999:blog-8600927897234142120.post999618552651907523..comments2023-12-23T03:43:29.570-08:00Comments on BI Polar: SSIS and the Package ProtectionLevel PropertyMatthew Rochehttp://www.blogger.com/profile/17230399970488763978noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-8600927897234142120.post-3846783638606124392011-12-06T16:27:46.851-08:002011-12-06T16:27:46.851-08:00Hi Matthew
Great article. Informative and Enterta...Hi Matthew<br /><br />Great article. Informative and Entertaining. I've been using the same approach for a while. It has always puzzled me though, how is it more secure keeping a password in clear text in an XML config file than in clear text in a dtsx package? <br /><br />In our scenario, where we are generating SSIS packages, it would be more desirable to have a 'keep but don't encrypt sensitive' option. We generate the package for each target environment.<br /><br />Thanks - Adam Gilmore - Dimodelo Solutions - dimodelo.comAdam Gilmorehttps://www.blogger.com/profile/04977175847170506361noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-36049120691822322412009-08-04T08:56:33.191-07:002009-08-04T08:56:33.191-07:00This is useful.
Cheers,
-- LeeThis is useful. <br /><br />Cheers,<br /><br />-- LeeEnglestonehttps://www.blogger.com/profile/03820913638307777241noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-51729457011826567992009-05-19T17:30:12.633-07:002009-05-19T17:30:12.633-07:00@Jeremy - The primary point of package configurati...@Jeremy - The primary point of package configurations is location independence. With configurations you can run your package in different environments (referencing different servers and databases and such) without needing to change your packages themselves. You just update the configurations and the packages remain untouched.<br /><br />If you will be (for example) executing your package against a development server while you're building it, against a test server while you're testing it, and against a production server after you deploy it, then yes - a configuration will come in awfully handy even if you are using Windows integrated authentication.Matthew Rochehttps://www.blogger.com/profile/17230399970488763978noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-83829089584399822702009-05-19T14:42:06.246-07:002009-05-19T14:42:06.246-07:00OK I'll leave those alone, thanks for the info.
B...OK I'll leave those alone, thanks for the info.<br /><br />But I still need to know if I should be using a Package Configuration, or not. Clearly the package works without one, and since I'm using Windows Authentication I don't have any sensitive information to encrypt. I'm just wondering if using a Package Configuration is recommended anyways to eliminate connection issues such as what I've described.Unknownhttps://www.blogger.com/profile/01625508489807560621noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-58443432780732497172009-05-19T12:31:00.000-07:002009-05-19T12:31:00.000-07:00@Jeremy - ProtectionLevel is a property of the Pac...@Jeremy - ProtectionLevel is a property of the Package, not the connection manager. I'm honestly not sure what you're trying to do with the connection manager. I would strongly recommend NOT mucking about with it. Also, remember that the XML behind the DTSX file is explicitly unsupported. There's no ovious reason why you would want or need to go there, and if you do make changes to the XML, you can damage the package in ways that cannot be repaired.Matthew Rochehttps://www.blogger.com/profile/17230399970488763978noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-61558865363699283052009-05-13T11:42:00.000-07:002009-05-13T11:42:00.000-07:00OH and that ProtectionLevel Property Attribute on ...OH and that ProtectionLevel Property Attribute on the Connection Manager is greyed out. Remember I'm looking at it through the Package Configuration Wizard. <br /><br />AND, I see nowhere to view or edit that ProtectionLevel Attribute when editing or viewing the properties of the Connection Manager itself when editing the package in BIDS (outside of the Package Configuration Wizard). Perhaps I'll look at the underlying XML. double arrg.Unknownhttps://www.blogger.com/profile/01625508489807560621noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-55722177803764963962009-05-13T11:38:00.000-07:002009-05-13T11:38:00.000-07:00Hey Matt,
I'm using the do not encrypt option as ...Hey Matt,<br /><br />I'm using the do not encrypt option as you recommended, having run into this clusterF myself in the past. I'm also using Windows Authentication running the dstx via a Windows 2003 Scheduled Task and a dedicated domain user account. In this case I really don't need a package configuration, right, because I'm not embedding passwords anywhere and I don't want to save any sensitive information? <br /><br />Second question - even though I'm using the do not encrypt option at the package level, when experimenting with Package Configurations (which I'm hoping not to have to use in this case), I noticed that my Connection Manager for a OLE DB SQLNCLI.1 data destination has Property Attributes that specify ProtectionLevel, Type = Object, Level = 1. I thought level 0 was do not encrypt, and I thought that the Connection Managers would automatically inherit their ProtectionLevel settings from the package setting. I'm concerned that my Connection Manager may be trying to encrypt some information (like that pesky connection password that seems to be required in connection managers even though I set it to blank). <br /><br />I say all this because my package runs successfully every 15 mins on the W2003 / SQL 2005 box against a target DB on a similar SQL box in a separate forest. Nearly every day now, the package fails with DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER, but then runs again successfully at the next 15 minute interval. Arrg. Intermittent issues are the worst. <br /><br />Thanks for any help you can provide with these questions.Unknownhttps://www.blogger.com/profile/01625508489807560621noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-70682237422571654052009-04-06T14:00:00.000-07:002009-04-06T14:00:00.000-07:00Hi Saggi,I have never seen a list of what properti...Hi Saggi,<BR/><BR/>I have never seen a list of what properties are marked as sensitive. If you look at the SSIS API for building components you'll see that the component developer can flag any property they want as "sensitive" but from my experience this information is not included in the documentation for existing components. I'm sorry I can't help you there.<BR/><BR/>If you really need encryption, the one option I've seen is this: http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html<BR/><BR/>I've never tried it personally, and don't know anyone who has, so proceed with caution...<BR/><BR/>MMatthew Rochehttps://www.blogger.com/profile/17230399970488763978noreply@blogger.comtag:blogger.com,1999:blog-8600927897234142120.post-57392622736320575602009-04-06T08:52:00.000-07:002009-04-06T08:52:00.000-07:00Hi,This post's just in time as one of my customers...Hi,<BR/><BR/>This post's just in time as one of my customers just asked me for SSIS deployment best practices. <BR/><BR/>As far as I know, when using windows authentication in connection strings, nothing is encrypted within the package and so the package can be easily deployed. <BR/><BR/>Do you know what else is considered sensitive other than passwords in connection strings? (I read the BOL article and "task generated XML nodes" and "variables marked as sensitive" aren't really descriptive.<BR/><BR/>The problem with using configurations is that the password is saved in clear text (or transparent encryption if you insist). Authorization (via permissions) and encryption are two complementing security facades...<BR/><BR/>If I understand correctly ServerStorage again only relies on authentication, so I guess you can't have both encryption (of some sort) and easy deployment...Unknownhttps://www.blogger.com/profile/00080815162157111129noreply@blogger.com