How to execute a stored procedure against linked server?

Question

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

• 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

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

Answer 1

The Microsoft Docs article has been reworded as of today (full disclosure: I reworded it). The feature is still deprecated, so our guidance is that you should just leave it on.

It now says (emphasis added):

This article is about the remote access configuration option, which is a deprecated SQL Server to SQL Server communication feature.

This option affects servers that are added by using sp_addserver and sp_addlinkedserver. You should leave remote access enabled (the default) if you use linked servers.

Answer 2

I've tested it on 2016 and 2019 version of SQL Server, it works fine. You can disable Remote access from configurations, and you'll be able to execute the procedure on Linked Server. You just need to enable RPC out option in parameters of Linked server.