Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server options "rpc" vs "rpc out" Expand / Collapse
Author
Message
Posted Saturday, June 9, 2012 2:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 16, Visits: 505
I've been baffled for quite some time about the two options for linked servers, "rpc" and "rpc out". Before you start typing, read on please - from what I've seen, there's a high chance you're 50% wrong with what you're about to say. I believe there is a deep-rooted and widely believed myth at work here. I believed it. You probably still do - and I don't blame you, given the lack of detail most documentation on the subject provides. But read on, please.

Without exception, every single blog, article, and forum post I find, including here, says the same thing: enable both options. My problem with this, and why I believe it's a myth is, why would both options exist, if they both have to be enabled/disabled at the same time every time? There MUST be a reason why they are separate options. And I can prove they are separate options.

Not one single person, among all the posts I have found, has ever explained why BOTH are necessary, or to be even more specific, why the "rpc" option by itself is ever needed. And in fact, I can prove it's not - at least, not most of the time, not for SQL-to-SQL linked servers.

This is all assuming SQL Server to SQL Server links.

"rpc out" definitely must be enabled to call a stored procedure on the remote server.

But what specifically does "rpc" do? If "rpc out" is enabled, and "rpc" is disabled... you can still call a stored procedure on the remote server. If you don't believe me, try it yourself - disable the "rpc" option, but keep "rpc out" enabled, and call a remote procedure. This proves the two options do have separate effects, and that "rpc out" is indeed necessary. But it also proves, that at least for a SQL-to-SQL link, the "rpc" option does NOT automatically need to be enabled to call a remote stored procedure.

Books Online, Technet, msdn, and every source that parrots any of them say the same thing:

"Enables RPC from the specified server."

Please re-read that... and hone in on the word "from." What oh what does that word mean in this definition? It seems to imply that, via the linked server, the remote server can call a procedure on the local server. Don't get confused. The "normal" thing to do is the reverse - from the local server, call a stored procedure on the linked server. For example, you run a script on SERVERA. SERVERA has a linked server defined that points to SERVERB. In your script, you call a stored procedure - via the linked server - that lives on SERVERB. That is normal, and most of us have done it plenty of times.

What "RPC from the specified server" implies, to me anyway, is that somehow you're connected to SERVERA, and doing whatever it is you're doing, SERVERB calls a stored procedure on SERVERA - via the linked server on SERVERA; there is no linked server defined on SERVERB. As if the linked server on SERVERA is somehow birectional - you define it on one server, but it can be used by BOTH servers.

Does this ever happen? How? I believe Oracle databases can do this, but I've never seen SQL do it, not even when linked to an Oracle database.

Has anyone ever had to enable "rpc" and only "rpc" while "rpc out" remains disabled? Why?
Post #1313539
Posted Saturday, June 9, 2012 10:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
When you did your teat where only "rpc out" was enabled were you using SQL authentication or Windows to auth to the remote server?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1313564
Posted Sunday, June 10, 2012 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 16, Visits: 505
Excellent question! I hadn't thought about it before, actually. Unfortunately it didn't yield an answer, but it does clarify my question. So I tested.

I created a test procedure on a remote server. Its definition is simply SELECT 'this is testproc', and on the remote server I granted execute to public on it (I wouldn't normally, but this is to test linked server security, not the end privileges on the remote server).

Also, I tried two different providers. I know if I specify "SQL Server" as the data source, I'm stuck to using the actual server name for the remote server (or configuring an alias, or editing the local hosts file, or...). I don't normally do that, but I tried that for testing as well since it does technically force using a different provider (SQLNCLI10 vs SQLOLEDB... though it still ends up actually using SQLNCLI10 under the hood either way). Normally I use "other data source" with "Microsoft OLE DB Provider for SQL Server" ... and simply put "CQL Server" as the product name. Yes, it's misspelled deliberately - it effectively tricks SQL into letting me name the linked server whatever I want and still link to a SQL Server. Very nice for abstracting code that uses a linked server, it can be identical across production and non-production systems even though different actual servers are involved.

Anyway, I tested all of the below scenarios, using both providers:

With SQL Server source (native/SQLNCLI10):

Mapped:
Windows to Windows (impersonate): works
Windows to SQL (specified): works
SQL to SQL (specified): works
(Can't do SQL to Windows; remote login must be SQL)

Use current:
Windows to Windows: works
SQL to SQL: works

Be made using specific (i.e. "all connections", remote login must be SQL):
Windows to SQL: works
SQL to SQL: works


With Other data source ("CQL Server"/SQLOLEDB):

Mapped:
Windows to Windows (impersonate): works
Windows to SQL (specified): works
SQL to SQL (specified): works
(Can't do SQL to Windows; remote user must be SQL)

Use current:
Windows to Windows: works
SQL to SQL: works

Be made using specific (i.e. "all connections"):
Windows to SQL: works
SQL to SQL: works

All of the above worked fine, "rpc" was never enabled for any of them; only "rpc out".

Just for fun I even attempted to disprove the "remote user must be SQL" statements; we all know Microsoft is 100% accurate with their documentation, after all... NOT. Well... they're right in this case, the remote user must be a SQL login. You'll even see the login failure in the remote server's log (if you have failures logged at least): "Attempting to use an NT account name with SQL Server Authentication". So it's clear even the link is specifically telling the remote server "SQL, you authenticate this" and the remote server responds "uh, I don't think so." I never had a reason to try that before so I didn't until now. But now at least I can confirm it's true.

I also ran my tests first with a simple call to the procedure, then forcing a distributed transaction ("remote proc transaction promotion" enabled as well, and insert/exec). Network DTC is enabled on both instances - which by the way are both on separate clusters as well. I can't think of why that would make a difference either, but I tried it ... and indeed there was no difference.

If someone sees a security permutation I didn't test that is possible, by all means let me know. But so far it doesn't appear security has anything to do with the "rpc" option. I didn't need to enable it for any of them.

p.s. One thing I forgot to mention: Kerberos authentication is enabled for all connections as well. I don't think that matters, but... I'm not about to go deleting SPN's to find out.
Post #1313614
Posted Monday, June 11, 2012 11:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
This is from BOL
RPC
Enables RPC from the specified server.

RPC Out
Enables RPC to the specified server.



I think you need to enable RPC for Linked server B when....

you have created linked server on Server A for Server B
and executing a procedure on Server B from Server A
and that procedure is referring another Linked Server C
Post #1313970
Posted Monday, June 11, 2012 11:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 16, Visits: 505
That was a good idea. But sadly, it doesn't appear to be the case, either. I just tried that; didn't need "rpc".

SERVERA -> SERVERB -> SERVERC

A procedure on SERVERC simpy returns @@SERVERNAME.
A similar procedure on SERVERB inserts its own @@SERVERNAME and the results from the proc on SERVERC (2 rows).
An identical procedure on SERVERA, calls the proc on B... so, 3 rows - one for each server.

The procedure on A and B:
create proc testproc as
create table #x (eek varchar(500))
insert into #x select 'this is testproc on server ' + @@SERVERNAME
insert into #x exec linkedserver.testdb.dbo.testproc;
select * from #x
go
grant execute on testproc to public

(the proc on C is simply: SELECT @@SERVERNAME)

Result: 3 rows, one from each server. "rpc" was not enabled, only "rpc out".
Post #1313980
Posted Tuesday, June 12, 2012 11:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 2,691, Visits: 3,376
RPC Serves no purpose...
http://msgroups.net/microsoft.public.sqlserver.server/rpc-vs-rpc-out-for-linked-s/98628


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1314598
Posted Saturday, June 16, 2012 1:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 16, Visits: 505
Well, that does confirm or at least support what I'd already learned, that "rpc" is useless, according to anyone else that wondered and checked it out. But I'm still left with the question "why does the option exist?"

I'm starting to wonder if it might be easier, and perhaps even more useful, to figure out how many licks it takes to get to the center of a Tootsie Pop.
Post #1316961
Posted Monday, September 9, 2013 7:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:16 PM
Points: 15, Visits: 60
I find enabling RPC assists in communications between the 2 SQL servers.

I've got a SQL 2005 Standard Server (ServerA) talking to a SQL 2005 Express (ServerB) server. Initially inserts and updates from Server A to B were quick but as the data grew the performance was suffering massively. I looked into optimising the queries as much as possible however it didn't make sense. Server A does a join with Server B and updates the data on Server B and this was taking forever and maxing out the link between sites (Server A is in our Head Office and Server B resides at a Co-Location within a DMZ). After enabling both RPC settings for the Linked Server B on Server A the performance was extremely better than before.

I'm going to do more testing (with larger options and record sets) but so far I'd say if enabling RPC doesn't affect your system, its probably better to do so.
Post #1492965
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse