通过

What is the RPC and RPC Out option on a SQL Server linked-server?

  • 项目

Someone asked me this week - what are the relevance of the RPC and RPC Out settings on a linked server? Do you need both of them or only one of them set to True?

The documentation is online in this article, but it doesn't clearly show examples or provide enough context, so I will try to do that here.https://technet.microsoft.com/en-us/library/ms186839(v=SQL.105).aspx

1. The first RPC setting is mainly for a legacy feature called Remote Server, which is documented here. https://msdn.microsoft.com/en-us/library/ms190187.aspx

You probably will not be using remote servers in SQL Server 2005 -SQL Server 2014 versions.

If you do happen to use "remote servers", the RPC setting enables a certain security feature. If it is turned off, when the second server (the one receiving the login attempt from the first server to which the client connected) tries to validate the remote login, it fails with error

18482 “Could not connect to server '%.*ls' because '%.*ls' is not defined as a remote server. Verify that you have specified the correct server name. %.*ls.”

EXEC master.dbo.sp_serveroption @server=N'MYREMOTESERVER', @optname=N'rpc', @optvalue=N'true' 

 

Kudos to my peer Ignacio Alonso Portillo for helping figure out this legacy option.

 

2. The RPC OUT  setting is very pertinent to linked servers on SQL Server 2005 -SQL Server 2014 versions.

Think about an RPC (Remote Procedure Call) as being a stored procedure being run remotely from Server 1 to linked Server 2 "myserver".

There are various syntaxes, the first using 4 part naming servername.databasename.schemaname.procedurename , the second here using EXECUTE(databasename.schemaname.procedurename ) AT servername convention.

We can test with a simple procedure sp_helpdb that lives in master database, and will list out the databases and some of their key properties.

  • EXEC [myserver].master.dbo.sp_helpdb
  • EXEC ('master.dbo.sp_helpdb') AT myserver

These kind of remote stored procedure calls will be blocked unless RPC OUT is set to True.

Msg 7411, Level 16, State 1, Line 1 Server 'myserver' is not configured for RPC.

 

You can set it to True or False in the linked server's properties (a right click menu in from the Linked Server Name in SQL Server Management Studio) .

 

Or you can set it by calling the stored procedure to set the server options like this test:

 -- Test RPC OUT as false and get the error

EXEC master.dbo.sp_serveroption @server=N'MYSERVER', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC [myserver].master.dbo.sp_helpdb

-- Msg 7411, Level 16, State 1, Line 1

-- Server 'myserver' is not configured for RPC.

GO

 -- Test RPC OUT as true and see success

EXEC master.dbo.sp_serveroption @server=N'MYSERVER', @optname=N'rpc out', @optvalue=N'true'

GO

EXEC [myserver].master.dbo.sp_helpdb

-- Command(s) completed successfully.

 

I hope this helps someone out there. Let me know if not. ~Jason

Comments

  • Anonymous
    July 19, 2015
    This was incredibly helpful. clear and plain to understand. thank you very much!

  • Anonymous
    September 30, 2015
    This is exactly what I was looking for.  Thanks a ton!

  • Anonymous
    March 02, 2016
    thank you very much for your information. :)

  • Anonymous
    July 07, 2016
    Very helpful, thank you

  • Anonymous
    July 27, 2016
    A very good explanation as I was exactly looking into from the same angle. Thanks a bunch!

  • Anonymous
    October 12, 2016
    The comment has been removed

  • Anonymous
    October 24, 2016
    Thanks for explaining this!very helpful!

  • Anonymous
    June 26, 2017
    Thank you for this advice. I was getting a server is not configured for rpc and you've made it simple to resolve.Very grateful!

  • Anonymous
    September 12, 2017
    Muchas gracias por su ayuda

  • Anonymous
    September 28, 2017
    What about the sp_configure 'remote access' setting? For security purposes, we turned this off. The 4-part name syntax now doesn't work, even though we are using linked servers and not the deprecated "remote server" (using sp_addserver). Both RPC and RPC Out are True on the linked server definition. My understanding is that the "remote access" setting would not impact linked servers.

  • Anonymous
    November 10, 2017
    This worked beautifully! Big thanks

  • Anonymous
    March 02, 2018
    Very helpful indeed.Thank for having posted this info.

  • Anonymous
    March 19, 2018
    The article is still helping DBAs out. Helped me out today. Thanks!

  • Anonymous
    May 22, 2018
    Thanks so much, I am setting up replication and was getting this error while running the [exec sp_adddistributor] command on my Publisher. Searched a lot on google but no success. This little thing - RPC solved the issue..

  • Anonymous
    June 21, 2018
    Thanks man