sql-server - 如何针对链接服务器执行存储过程？

Question

我们目前使用以下方式针对链接服务器执行存储过程:

EXECUTE [LinkedServer].[DatabaseName].[dbo].[MyProcedure]

例如:

EXECUTE Screwdriver.CRM.dbo.GetCustomer 619

这工作得很好；通过链接服务器查询工作正常。

禁用已弃用的“远程访问”功能

显然有一个鲜为人知、很少使用的功能，称为远程访问。除了 say here: 之外，Microsoft 几乎没有透露此功能是什么。

This configuration option is an obscure SQL Server to SQL Server communication feature that is deprecated, and you probably shouldn't be using it.

ⓘ Important

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sp_addlinkedserver instead.

The remote access option only applies to servers that are added by using sp_addserver, and is included for backward compatibility.

来自 SQL Server 2000 联机丛书:

Note Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.

我们只添加过链接服务器，从未使用过此“远程访问”功能，也从未使用sp_addserver添加服务器.

我们都很好。对吗？

除了关闭远程访问会破坏一切

审计员提到我们应该关闭远程访问功能:

• 这是剪贴板上的安全复选框
• 它已被 Microsoft 弃用
• 几乎没有使用过
• 我们不使用它

应该没问题吧？

微软文档how to turn off this hardly used feature:

Configure the remote access Server Configuration Option

EXEC sp_configure 'remote access', 0 ;  
GO  
RECONFIGURE ;  
GO

除非我们这样做:一切都会陷入困境:

Msg 7201, Level 17, State 4, Procedure GetCustomer, Line 1

Could not execute procedure on remote server 'Screwdriver' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

比失败更糟糕？  

为了绝对确定我正在使用链接服务器，我:

EXECUTE sp_dropserver @server='screwdriver', @dropLogins='droplogins'

EXECUTE sp_addlinkedserver N'screwdriver', N'SQL Server'

并重新运行我的过程调用:

EXECUTE Screwdriver.CRM.dbo.GetCustomer 619

Msg 7201, Level 17, State 4, Procedure GetCustomer, Line 1
Could not execute procedure on remote server 'Screwdriver' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

比失败更糟糕!？

我可以confirm服务器是链接服务器(而不是“远程”服务器)，使用:

SELECT SrvName, IsRemote 
FROM master..sysservers
WHERE SrvName = 'Screwdriver'

Srvname      IsRemote
-----------  --------
screwdriver  0

或者使用现代元素:

SELECT Name, Is_Linked
FROM sys.servers
WHERE Name = 'Screwdriver'

Name         Is_linked
-----------  --------
screwdriver  1

总结

我们现在所处的位置是:

• 我已禁用远程访问
• 远程访问仅适用于通过 sp_addserver 添加的服务器
• 它不适用于通过 sp_addlinkedserver 添加的服务器
• 我正在访问通过 sp_addlinkedserver 添加的服务器

为什么不起作用？

这引出了我的问题:

• 如何针对链接服务器执行存储过程？

推论:

• 如何不针对添加的(即“远程”)服务器执行存储过程？

奖金闲聊

使用sp_configure调整的远程访问配置选项也通过用户界面公开。 SSMS UI 错误地描述了该功能:

错误的措辞是:

Allow remote connections to this server

应该这样表述:

Allow remote connections to from this server.

Books Online还错误地记录了该功能:

Allow remote connections to this server

Controls the execution of stored procedures from remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from a remote server.

应该是:

Allow remote connections to from this server

Controls the execution of stored procedures from to remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from to a remote server.

如今 Microsoft 的年轻人不记得他们从未接触过的 20 年前已弃用的功能的作用，这是有道理的。

BOL 2000 的文档

SQL Server 2000 是最后一次记录此功能。出于后代和调试目的转载于此处:

Configuring Remote Servers

A remote server configuration allows a client connected to one instance of Microsoft® SQL Server™ to execute a stored procedure on another instance of SQL Server without establishing another connection. The server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.

If you want to set up a server configuration in order to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.

Note Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.

另请参阅

• Executing a Stored Procedure on a Linked Server
• SQL Linked server - Remote access error when executing SP
• SQL Server Error: Could not execute remote procedure
• MSDN Blogs: Unable to execute a remote stored procedure over a linked server ( archive.is )
• Configure the remote access Server Configuration Option
• sp_addlinkedserver (Transact-SQL)
• sp_configure (Transact-SQL)
• Security Audit requires turning Remote Access off on all SQL 2005 and SQL 2008 Servers

备用标题:禁用 SQL Server 远程访问会破坏存储过程。

Answer 1

好吧，当你是对的时候，你就是对的，无论是否有记录。设置remote access至0 (并重新启动)会导致使用四部分语法的远程存储过程调用失败，即使所有文档都表明链接服务器不应失败。即使在最新版本的 SQL Server 2017 (RTM CU12) 上也是如此，因此这不是特定于版本的。目前尚不清楚这是否是一个真正的限制，或者代码是否只是有缺陷并基于 remote access 阻止它。功能检查，即使它在技术上可行。

涉及四部分名称 ( SELECT * FROM server.db.scheme.table ) 的查询不会失败，大概是因为这仅适用于链接服务器，并且从一开始就不涉及远程访问。

作为解决方法，您可以将调用更改为使用 EXECUTE .. AT :

EXEC ('EXECUTE CRM.dbo.GetCustomer 619') AT Screwdriver

只要链接服务器具有RPC Out，此功能就有效。启用选项(如果由 sp_addlinkedserver 添加且没有特殊选项，则默认为该选项)。

不幸的是EXECUTE .. AT当涉及到参数时就不太方便了，因为它只支持 ?语法:

EXEC ('EXECUTE CRM.dbo.GetCustomer @Customer=?', 619) AT Screwdriver

参数名称在这里是可选的，但我强烈建议使用它来保持事情的可预测性。与 EXECUTE 相同-- 它是可选的，但它清楚地表明我们实际上正在运行任意查询，而不仅仅是调用存储过程。

如果您的程序有OUTPUT参数，这个plain是行不通的； EXECUTE .. AT还不够聪明。您可以指定OUTPUT在调用中，但该值不会被复制回来。解决方法超出了本答案的范围，但它们不会很好。