问题描述
SSIS 包有一个名为 ProtectionLevel 的属性,其中包含多个可能的值.任何人都可以提供可用的 ProtectionLevel 选项的解释以及它们在包中的行为方式的示例吗?使用 ProtectionLevel 属性的优点和缺点是什么.
SSIS packages have a property called ProtectionLevel with several possible values. Can anyone provide an explanation of the available ProtectionLevel options and examples of how they are behave in a package? what is the advantage and disadvantage of using ProtectionLevel properties.
谢谢.
推荐答案
包保护级别有几种不同的风格.这个想法是 SSIS 知道像连接字符串这样的东西可能包含敏感信息,比如密码.如果您是供应商并且您的产品是 WhizBangPackage,您不希望人们看到其神奇之处,那么该软件包本身可能包含专有信息.出于这些以及更多原因,MS 拥有如何将底层 XML 以及 SSIS 包的全部内容写入磁盘的概念.
Package Protection Levels come in a few different flavors. The idea is that SSIS knows that things like connection strings might contain sensitive information, like passwords. The package itself might contain proprietary information if you are a vendor and your product is the WhizBangPackage you wouldn't want people to see how the magic works. For these reasons and more, MS has the concept of how the underlying XML, and that's all an SSIS package is, should be written to disk.
EncryptSensitiveWithUserKey 这是默认设置.任何可能敏感的东西都被假定为敏感的.保存包后,VS 将使用原作者的 Active Directory 帐户的一些位来加密诸如连接字符串之类的内容.即使该连接字符串使用 SSPI 并且因此没有密码,它仍会加密底层 XML 中的连接字符串.当包运行时,SSIS 将与 AD 对话以解密该信息.通常,在软件包的原始作者不再在公司工作并且他们的 AD 帐户被删除之前,一切都运行良好.我们在使用 SQL Server 2005 时遇到的是运行包的 SQL 代理作业无法解密包.开发人员可以打开包,它在交互模式下运行良好,但在非交互模式下失败.立即的解决方案是将作者更新为具有活动 AD 帐户的人并重新部署.这可能会在当前/未来版本中修复,但这是我对这个版本的战争故事.
EncryptSensitiveWithUserKey this is the default setting. Anything that could be sensitive is assumed to be sensitive. When the package is saved, VS is going to use some bits of the the Active Directory account of the original author to encrypt things like connection strings. Even if that connection string is using SSPI and thus, no password, it'll still encrypt the connection string in the underlying XML. When the package runs, SSIS will talk to AD to unencrypt that information. Generally, that all works well and good until the original author of a package is no longer with the company and their AD account is removed. What we ran into with SQL Server 2005 was the package can't be decrypted by the SQL Agent job running the package. A developer could go and open the package and it'd run fine in interactive mode but fail during non-interactive mode. The immediate resolution was to update the author to someone with an active AD account and redeploy. This may be fixed in current/future release but that's my war story on this one.
DontSaveSenstive 这是我唯一需要使用的设置.保存时,SSIS 不会将任何看起来像密码的内容写入 .dtsx 文件.根据我的经验,在您当前的设计会话中保存后,它也会将其清空,从而导致立即验证错误.特别是,这使得 FTP 任务成为一个 PITA,除非您正在使用配置,您应该这样做,因为这是在环境之间迁移包的唯一合理方式.使用配置帮助 SSIS 连接管理器记住"密码,而不会碰到磁盘.
DontSaveSenstive this is the only setting I ever had need to use. SSIS will not write anything that looks like a password into the .dtsx file when you save. And it was my experience that it'd also blank it out after saving in your current design session leading to immediate validation errors. In particular, this makes the FTP task a PITA to work with unless you are using configurations, which you should as that's the only sane way of migrating packages between environments. Use configuration to help SSIS connection managers to "remember" the password without it ever hitting disk.
EncryptSensitiveWithPassword 现在,您不再使用 AD 帐户来加密敏感位,而是使用开发人员提供的密码.这样做的缺点是对于超过 1 人的团队,您现在拥有一个共享密码,而一个多人共享的密码违背了拥有密码的目的.
EncryptSensitiveWithPassword Instead of using an AD account to encrypt the sensitive bits, you now use a password the developer supplies. The downside to this is for teams of more than 1 person, you now have a shared password and a password shared by more than one person defeats the purpose of having a password.
EncryptAllWithPassword 这不是仅仅加密敏感位,而是用密码加密整个 XML.与以前相同的缺点,共享秘密 = 没有秘密.此外,如果您丢失了钥匙,您会遇到麻烦并正在重新创建您的包裹.
EncryptAllWithPassword Instead of just encrypting sensitive bits, this is going to encrypt the entirety of the XML with a password. Same downside as before, shared secret = no secret. Also, if you lose the key you're humped and are recreating your package.
EncryptAllWithUserKey 和password一样,以作者的AD账号为key对整个文件进行加密.与上述相同的缺点是,该帐户消失了,并且没有解锁包裹的钥匙.
EncryptAllWithUserKey Same as with password, it encrypts the entire file with the author's AD account as the key. Same downside as above, that account goes away and there's no key to unlock the package.
ServerStorage - 无论您的本地设置如何,假设您部署到 msdb 目录,程序包都将在数据库中加密.老实说,我从来没有用过它.我们部署到 msdb,但依靠我们的配置来保护敏感数据的私密性.
ServerStorage - Doesn't matter what your local setting, assuming you deploy to the msdb catalog, packages will be encrypted in the database. I've never used it to be honest. We deploy to the msdb but rely on our configuration to keep sensitive data private.
这篇关于使用包保护级别的 SSIS 包的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!