如何对链接服务器执行存储过程? [英] How to execute a stored procedure against linked server?

查看：34
本文介绍了如何对链接服务器执行存储过程?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

例如:

EXECUTE Screwdriver.CRM.dbo.GetCustomer 619

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

禁用已弃用的远程访问"功能

显然有一个鲜为人知、很少使用的功能，称为远程访问.除了 注意 提供对远程服务器的支持只是为了向后兼容.必须对 SQL Server 的远程实例执行存储过程的新应用程序应改用链接服务器.

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

我们都很好.对吗?

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

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

  • 这是他们剪贴板上的一个安全复选框
  • 它已被微软弃用
  • 几乎从未使用过
  • 我们不使用它

应该没问题吧?

微软文档

不正确的措辞是:

<块引用>

允许远程连接到此服务器

应该这样表述:

<块引用>

允许远程连接到 来自这个服务器.

注意 提供对远程服务器的支持只是为了向后兼容.必须对 SQL Server 的远程实例执行存储过程的新应用程序应改用链接服务器.

另见

替代标题:禁用 SQL Server 远程访问会中断存储过程.

解决方案

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

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

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

EXEC ('EXECUTE CRM.dbo.GetCustomer 619') AT 螺丝刀

只要链接服务器启用了 RPC Out 选项(如果由 sp_addlinkedserver 添加而没有特殊选项，则默认情况下会启用此选项).

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

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

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

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

为此打开一个建议可能是值得的，因为它看起来像那种真正应该按照文档工作的东西(如果微软想永久摆脱远程访问，他们无论如何都需要这样做).

We currently execute a stored procedure against a linked server using:

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

For example:

EXECUTE Screwdriver.CRM.dbo.GetCustomer 619

And this works fine; querying through a linked server works fine.

Disabling the deprecated "Remote Access" feature

There is apparently a little known, seldom used, feature known as remote access. Microsoft has very little to say about what this feature is, except to say here:

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.

And from the SQL Server 2000 Books Online:

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.

We only ever added linked servers, and have never used this "remote access" feature, and have never added a server using sp_addserver.

We're all good. Right?

Except turning off remote access breaks everything

An auditor has mentioned that we should turn off the remote access feature:

  • it's a security checkbox on their clipboard
  • it's deprecated by Microsoft
  • it's hardly ever used
  • and we don't use it

Should be fine, right?

Microsoft documents how to turn off this hardly used feature:

Configure the remote access Server Configuration Option

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

Except when we do: everything goes to hell:

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.

Worse than failure? 

Just to be absolutely sure I'm using a linked server, I:

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

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

and re-run my procedure call:

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.

Worse than failure!?

I can confirm that the server is a linked server (and is not a "remote" server) using:

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

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

Or using the modern objects:

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

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

To Sum Up

We're at the point now where:

  • I've disabled remote access
  • Remote access only applies to servers added through sp_addserver
  • it doesn't apply to servers added through sp_addlinkedserver
  • I'm accessing a server added through sp_addlinkedserver

Why isn't it working?

Which brings me to my question:

  • How to execute a stored procedure against a linked server?

And the corollary:

  • How to not execute a stored procedure against an added (i.e. "remote") server?

Bonus Chatter

The remote access configuration option, that you adjust using sp_configure, is also exposed through the user interface. The SSMS UI describes the feature incorrectly:

It incorrect phrasing is:

Allow remote connections to this server

It should be phrased:

Allow remote connections to from this server.

The Books Online also document the feature incorrectly:

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.

It should be:

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.

It makes sense that the youngsters at Microsoft these days don't remember what a 20 year old deprecated feature that they've never touched does.

Documentation from BOL 2000

SQL Server 2000 was the last time this feature was documented. Reproduced here for posterity and debugging purposes:

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.

See also

Alternate title: Disabling SQL Server Remote Access breaks stored procedures.

解决方案

Well, when you're right you're right, whether it's documented or not. Setting remote access to 0 (and restarting) causes remote stored procedure calls using four-part syntax to fail, even though all the documentation suggests it should not fail for linked servers. This is true even on the most recent build of SQL Server 2017 (RTM CU12), so this is not version specific. It's not clear if this is a real restriction, or if the code is just buggy and blocking it based on the remote access feature check even though it would technically work.

Queries involving four-part names (SELECT * FROM server.db.scheme.table) will not fail, presumably because this only works for linked servers and never involved remote access in the first place.

As a workaround, you can change the call to use EXECUTE .. AT:

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

This works as long as the linked server has the RPC Out option enabled (which it will be by default if added by sp_addlinkedserver with no special options).

Unfortunately EXECUTE .. AT is much less convenient when parameters are involved, because it only supports the ? syntax:

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

The parameter name is optional here, but I'd strongly recommend using it to keep things predictable. Same with EXECUTE -- it's optional, but it makes it clear that we're really running an arbitrary query, not just calling the stored procedure.

If your procedure has OUTPUT parameters, this plain will not work; EXECUTE .. AT isn't clever enough for that. You can specify OUTPUT in the call, but the value will not be copied back. Workarounds for that are beyond the scope of this answer, but they won't be pretty.

It may be worth opening a suggestion for this, because it seems like the sort of thing that really ought to work as documented (and if Microsoft ever wants to get permanently rid of remote access, they'll need to anyway).

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

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆