存储过程对链接服务器进行故障转移. [英] Stored procedure failing over linked server.

问题描述

我的存储过程之一可能由于某些安全问题而失败.
它要做的就是从远程链接服务器获取一些数据并将其插入本地服务器.
在SSMS中可以正常工作，但是如果我尝试使用SQL Server Agent计划它，则始终会失败.
SQL Server代理使用本地管理员帐户，并使用SQL Server登录名连接到链接的服务器.
我需要解决什么才能使其正常工作?

One of my stored procedures is failing probably due to some security issue.
All it does is, get some data from a remote linked server and insert it in the local server.
Works fine in SSMS but if I try to schedule it using SQL Server Agent it fails.. all the time.
The SQL server Agent uses a local admin account and connects to the linked server using a SQL server login.
What do I need to fix to get this working?

推荐答案

我相信在SSMS中而不是与代理一起工作的原因是，在SSMS中，您正在使用Windows帐户，在两个服务器上都有登录权限.本地计算机上的本地管理员可能无法访问远程计算机.我曾尝试解决此问题，但我一直回想的是使用Windows域帐户来访问远程数据.

I believe the reason why this works in SSMS and not with the agent is because in SSMS you are using a Windows account, which has login priviledges on both servers. The local admin on the local machine probably does not have access to the remote machine. I''ve tried getting around that but what I keep coming back to is using Windows domain accounts to access the remote data.

这篇关于存储过程对链接服务器进行故障转移.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！