SSIS在取消选中敏感时清除加密密码 [英] SSIS Clear encrypted password when unchecking Sensitive
问题描述
使用的工具
- Visual Studio 2017（带SSIS）
- SQL Server Management Studio 17.9.1
$
参与流程
两SSIS开发人员和带有Integration Services目录的SSMS，用于存储已部署的项目。
概述
b $ b我在SSIS中创建了一个内部项目的解决方案。每个项目都有项目参数，为每个数据库连接指定两个不同的参数：连接字符串和密码。密码标记为敏感。
项目及其所有软件包的ProtectionLevel设置为EncryptAllWithPassword。 该项目被推送到git存储库并且
另一个开发人员下载更改。现在，他需要提供密码才能使用项目（或解决方案中的多个项目）。到目前为止，我们有一个"主密码"。在项目级别上，保护
访问敏感密码等参数。当开发人员转到Project.params并取消敏感标记时，将显示密码。现在一切都很好，因为他需要首先知道项目的密码才能看到密码。
这是棘手的部分
在部署项目时，请更改Integration Services目录，ProtectionLevel正在更改，并且可以从Management Studio导出的项目不再受密码保护。要导出这样的项目，显然需要** ssis_admin **权限，
，但这超出了此问题的范围。部署项目然后从SSMS导回到SSIS时，开发人员可以在没有密码的情况下打开它，并取消Project.params密码的敏感标记。现在可以看到所有密码。这个
错了。
我想要实现的目标
我想用SSMS中的敏感值模仿相同的行为。无论何时取消环境变量上的敏感标记，都会清除该值。
但是，当我在SSIS Project.params中执行相同操作时（取消敏感标记），该值仍然显示，所以我可以看到所有密码。
我希望它按原样存储，但无法看到它的纯文本值。
有可能吗？或者也许有更好的方法来组织这个？我需要能够从SQL Server代理（SSMS）中提供环境变量以及SSIS下我自己的计算机执行包，这就是为什么我需要存储这些密码
以便每次都不重复它们。
是
已知问题
<安迪在这里写过这篇文章
https://andyleonard.blog/2018/04/deploying-to-the-ssis-catalog-changes-the-protection-level/
建议的解决方案是在.Net中创建一个自定义任务来封装它
Tools used
- Visual Studio 2017 (with SSIS)
- SQL Server Management Studio 17.9.1
Involved in the process
Two SSIS developers and SSMS with Integration Services Catalog which stores deployed projects.
Overview
I have a solution with projects inside created in SSIS. Each project has project parameters specifying for each database connection two different params: Connection string and a password. Password is marked sensitive.
Project and all it's packages have ProtectionLevel set to EncryptAllWithPassword. The project get's pushed to git repository and
another developer downloads changes. Now, he needs to provide password in order to be able to work with the project (or multiple projects within solution). So far so good, we have a "master password" on project levels which protect
access to parameters such as sensitive passwords. When a developer goes to Project.params and untick sensitive mark, the password is shown. All good for now as well, since he needed to know the password for the project first to see the passwords.
Here's the tricky part
When the project is being deployed do Integration Services Catalog, ProtectionLevel is being changed and the project which can be exported from Management Studio is no longer password protected. To export such a project one obviously needs **ssis_admin** permission,
but that's out of scope for this issue. When the project was deployed and then imported back from SSMS to SSIS, a developer can open it without password and untick the sensitive mark for Project.params passwords. All passwords are visible for him now. This
is wrong.
What am I trying to achieve
I want to mimic the same behaviour with sensitive values we have in SSMS. Whenever you untick a sensitive mark on an environment variable, the value is cleared.
However, when I do the same in SSIS Project.params (untick sensitive mark), the value is still shown so I can see all the passwords.
I'd like it to be stored as it is, but unable to see it's plain text value.
Is it possible at all? Or maybe there's a better way to organise this? I need to be able to execute packages from within SQL Server Agent (SSMS) providing environment variables as well as from my own computer under SSIS, which is why I need to store these passwords
in order not to repeat them every time.
Yes
Its a known issue
Andy had written about it here
https://andyleonard.blog/2018/04/deploying-to-the-ssis-catalog-changes-the-protection-level/
The solution suggested was to create a custom task in .Net to encapsulate it
这篇关于SSIS在取消选中敏感时清除加密密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
