部署SSIS包并保留连接密码
我有一个 SSIS 包，它查询视图中的某些记录，然后使用数据库邮件发送电子邮件。我想将包部署到这些视图所在的 SQL Server，然后使用 SQL Server 代理安排我们，但我遇到了许多问题。
主要问题是，作业中存在一个连接管理器，它存储了有权访问视图和电子邮件配置文件的 SQL Server 帐户的凭据。我已将包设置为 EncryptSensitiveWithPassword 而不是 User Key，但我似乎找不到任何方法在作业运行时将该密码实际传递给 SQL Server 代理。因此，当它执行时，它会失败，因为它无法连接。
很多网站都说要使用配置文件，但是当我走这条路线时，它完全中断了工作，抱怨无法建立 OLEDB 连接。在这种情况下，我什至无法在本地计算机上运行该作业。
其他一些站点建议将包复制到计算机上的本地 ssis 包存储中，但我只能通过 SQL Server Tools 访问计算机，我无法远程连接或在文件系统上安装任何内容，因此我必须通过安装使用 SSIS 部署过程的清单文件。
我读过一些有关在另一个数据库表中存储设置的内容，但这再次提出了服务器上的权限问题。
最后，我尝试在作业调度程序中传递 /password 或 /decrypt，但是当我单击“确定”时，调度程序会删除该参数。
这是一个内部服务器，安全性并不是一个大问题，知道如何以明文形式传递解密密码，或者只是告诉 SSIS 不要费心保护敏感数据吗？
I have an SSIS package that queries a view for some records then sends off an email using Database mail. I want to deploy the package to the SQL Server that these views exist in and then schedule us using SQL Server Agent but i've run into a number of issues.
The main problem is that within the job there is a connection manager that has stored credentials for a SQL Server account that has access to the views and email profile. I've set the package to EncryptSensitiveWithPassword instead of User Key but i can't seem to find any way to actually pass that password the the SQL Server Agent when the job is run. So when it executes it fails since it can't connect.
A bunch of websites say to use the config file, but when I go that route it breaks the job entirely complaining that the OLEDB connection can't be established. In that scenario I can't even run the job on my machine locally.
A few other sites recommended copying the package to the local ssis package store on the machine, but I only have access to the machine through SQL Server Tools, I can't remote in or install anything on the file system so I have to install through the manifest file using the SSIS deployment process.
I've read something about storing settings in another DB table, but that again presents issues with permissions on the server.
Finally I tried passing /password or /decrypt in the job scheduler, but when i click ok it scheduler removes that parameter.
This is an internal server and security isnt really a big concern, Any idea how i can pass the decryption password in plaintext or just tell SSIS to not bother securing sensitive data?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
技巧是：
这意味着密码与包一起保存，因此可以在任何人执行包时使用（如果您不希望这样做）。 或者，您可以更改连接，以便它使用集成安全
性（Windows 身份验证而不是 sql 登录）并更改作业，以便它以有权访问数据库的用户身份执行包。从包查询的视图来看，这是首选选项，因为您在任何地方都不需要密码，而是通过用户身份和权限来管理它。
The trick is:
This means that the password is saved with the package and therefore can be used when anyone executes the package. If it's something you don't want anyone to execute you then lock down security appropriately.
Alternatively you could change the Connection so it uses Integrated Security (windows authentication instead of a sql login) and change the Job so it executes the package as a user that has access to the database & view that's queried by the package. This is the preferred option from the view that you don't need passwords anywhere and instead manage it via user identity and permissions.
听起来当您使用配置文件时，您没有正确设置配置文件。
您在本地运行时收到的确切错误是什么？我可以尝试重现这个问题。
It sounds like when you're using the config file that you're not setting up the config file correctly.
What is the exact error that you're receiving when running it locally? I can try to recreate the issue.
您是否在 bids 中设置了包来存储密码，然后将包保存到 ssis 并告诉它对密码进行加密？如果您没有将它们保存在本地，则可能是您丢失了密码，然后在没有密码的情况下推送了包。
do you have the package set up in bids to store the passwords, and then save the package to ssis and tell it to encrypt the passwords? if you're not saving them locally, it could be that you've lost your passwords and then pushed the package without them.