SQL Server 2005 - 链接服务器和 代理职位
我有一个奇怪的场景，目前无法解释。 我希望这只是“星期五的感觉”，或者希望这里有一个好心的鞋底能拯救我的大脑，并将我从“但是为什么！？”的无休止的循环中拯救出来。 :)
两台服务器，运行 SQL Server 2005，DNS 条目为：（
1. ServerA
2. ServerB
好吧，它们实际上并没有这么称呼，但它就足够了...）
在两个 SQL Server 实例上，都配置了指向另一台服务器的链接服务器。
出于明显的安全原因，LinkedServer 安全配置设置为：
- Be made using the login's current security context
其他“链接服务器选项”是...
Collation Compatible: True
Data Access: True
RPC: True
RPC Out: True
Use Remote Collation: True
Collation Name: <blank>
Connection Timeout: 30
Command Timeout: 10
在两个实例上使用相同的密码创建登录名。 登录名被授予相关存储过程的适当执行权限。
我编写了一些代码，并在该登录名下执行它，一切正常万岁
但是当我创建代理作业来运行这些存储过程时，一切都出了问题。 代理作业的所有者是“automated_job_login”，但我的错误日志记录显示以下内容： - 用户“automated_job_login”登录失败
（该名称再次被更改以保护有罪者。）
我一生都无法弄清楚为什么当我以该用户身份登录时它会起作用，但在连接到链接服务器时作业会出错。 （它肯定是在链接服务器连接点。）
为了让事情变得更加混乱，如果我将链接服务器安全配置更改为“使用此安全上下文进行：”并使用正确的密码指定“automated_job_login”，它可以正常工作。
我错过了一些东西，我知道我一定错过了，但我找不到什么。 我读过文档，直到我的眼睛流血，我失败了。 请帮助我:)
[将链接服务器安全选项保留为“使用此安全上下文进行：”不是一个选项，因为这会给该服务器的所有用户提供对其他服务器的不可接受的访问级别。]
I have a strange scenario that I am currently unable to explain. I live in hope that it's just "the Friday feeling" or that some kindly sole here will bail my brain out and save me from endless loops of "but why!?" :)
Two servers, running SQL Server 2005, with DNS Entries of:
1. ServerA
2. ServerB
(Well, they're not really called that, but it will suffice...)
On both SQL Server Instances there are Linked Servers configured pointing to the other server.
For obvious security reasons the LinkedServer Security configuration is set to:
- Be made using the login's current security context
The other "Linked Server Options" are...
Collation Compatible: True
Data Access: True
RPC: True
RPC Out: True
Use Remote Collation: True
Collation Name: <blank>
Connection Timeout: 30
Command Timeout: 10
A login is created with the same password on both Instances. The logins are given the appropriate execute permissions to the relevant stored procedures.
I write some code, and execute it under that login and it all works hoorah
But when I create an Agent Job to run these stored procedures it all goes wrong. The owner of the Agent Job is 'automated_job_login' but my error logging gives the following:
- Login failed for user 'automated_job_login'
(Again that name has been altered to protect the guilty.)
I can't figure out for the life of me why it will work when I log in as that user, but the job errors when connecting to the linked server. (It's definately at the point of the linked server connection.)
To make things more confusing, if I change the Linked Server Security configuration to "Be made using this secuirty context:" and specify 'automated_job_login' with the correct password, it works fine.
I'm missing something, I know I must be, but I can't find what. I've read documentation until my eyes bleed and I've failed. Please help me :)
[Leaving the Linked Server Secuiry option as "Be made using this secuirty context:" is not an option as this would give all users of that server unnaceptable levels of access to the other server.]
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SQL 代理作业可能由您的登录名拥有，但它不会在该登录上下文中执行。 它位于 SQL 代理服务帐户上下文
因为如果您有 SQL Server 2005，则可以使用 EXEC AS USER = 'mylogin' 作为存储过程选项。
否则，您必须使用
sp_add_jobstep。 在 SSMS 中，您可以设置上下文。 工作所有权与设置 ths 略有不同，IIRC。The SQL agent job may be owned by your login, but it is not executed in that login context. It's in the SQL Agent service account context
Because you have SQL Server 2005, you could use EXEC AS USER = 'mylogin' as a stored proc option.
Otherwise, you have to set the database user name using the
@database_user_nameparameter ofsp_add_jobstep. In SSMS, you can set the context. Job ownership is slighty different to setting ths, IIRC.