安全问题?尝试SQL Server代理代理…

在过去的美好时代的SQL服务器,所有我们要做的就是使我们的服务帐户本地管理员组的成员,我们都设置。当然,那些日子已经实践是现在正确地视为安全风险的比例升高。SQL服务器设置。exe现在将给你普通域用户帐户最低限度的权限来运行服务——仅此而已,没有什么更少。这是好的做法,但它有自己的含义。例如,运行一些简单的复制数据库向导可能是一个挑战。CDW允许您将数据库从一个SQL Server复制到另一个连同相关的登录。它生成的工作执行一个SSIS包,默认情况下,使用SQL Server代理服务帐户的安全上下文中。现在这些最低权限设置生成的。exe不再足以完成这项工作,原谅双关语。一个解决方案是提升服务帐户的权限实施相同的老安全漏洞只是这样一个工作可以成功。 That is not really an option. Thankfully there is a more elegant solution, namely using Proxy and Credential objects. A Credential allows you to define a Windows account with a certain permission set that you want to map to a SQL Server Login. This effectively gives additional permissions to the login. A Credential can also be used to give permissions to a SQL Server Agent Proxy. The Proxy can then be used instead of the SQL Server Agent Service Account when running, for instance, an SSIS package in a Job. So in our CDW case, we could create a Credential (under SSMS Security folder) that identifies an account that does have the required permissions. Then create a Proxy (under SQL Server Agent Proxies folder), and make sure it references the Credential you created earlier. A Proxy can be defined for many types of operations, in our case for SSIS Package Execution. Finally, when the CDW prompts for the Integration Services Proxy account, you can use the drop-down to select your new Proxy and the Job will succeed instead of getting access-denied errors. This strategy can also be used for any Job which executes an SSIS package that requires temporary elevated permissions. So you get to run your Job successfully while leaving the service account to do its job – securely. Cheers Brian