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.

Nedry

What is result of a following query?: SELECT * FROM [Screwdriver].sys.configurations WHERE name = 'remote access' – Hydrotherapeutics

@SlavaMurygin Invalid object name 'screwdriver.sys.configurations'. But if you're interested in the remote access setting in the sys.configurations table on the target server: it is on (value=1) – Nedry

Answer 1

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.

Teleran

Answer 2

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.

Agra

Thanks for doing that. It will help out greatly the next time someone insists we turn it off. – Nedry

Answer 3

I was able to get it to return a data from the IBMi/AS400 using the following syntax. It wasn't working prior to adding the {} in the stored procedure. It seemed to run but wouldn't return any data. I also set the library list on the linked server connection. If you don't do this, you will have to qualify the files in your stored procedure as well as the stored procedure call here.

EXEC ('{CALL IBMiStoredProcedureName (?)}', @PurchaseOrder) AT [LinkedServerName]

If you have multiple parameters, the syntax would be as follows.

EXEC ('{CALL IBMiStoredProcedureName (?,?)}', @PurchaseOrder, @POLine) AT [LinkedServerName]

Sherrard

Answer 4

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.

Carrero