June 9, 2012 at 2:45 pm
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?
June 9, 2012 at 10:52 pm
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
June 10, 2012 at 12:32 pm
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. 😉
June 11, 2012 at 11:08 am
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
June 11, 2012 at 11:56 am
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".
June 12, 2012 at 11:09 am
RPC Serves no purpose...
http://msgroups.net/microsoft.public.sqlserver.server/rpc-vs-rpc-out-for-linked-s/98628
Jared
CE - Microsoft
June 16, 2012 at 1:09 am
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. 😛
September 9, 2013 at 7:21 pm
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.
January 29, 2020 at 12:24 am
I was just wondering the same thing (he says seven years later.. in his first post) and found ya'll on Google. I have two ideas. This post is about the first:
There are cases where having the execution of a query originate on the remote server (looking through the telescope backwards, as a friend says) would be more efficient, such as:
select someThing
from remote.db.schema.hugetable A
join remote.db.schema.hugetable B on A.pk = B.pk
join localdb.schema.tinytable C on C.pk = A.pk
Does the SQL native client provider (or any provider) have this optimization?
If so, when used the direction of everything (including RPC) will be reversed.
January 29, 2020 at 12:41 am
As this is my second post I am much older and wiser now.
If this rubs locally but executes the sp remotely (RPC out):
insert into localTable exec remoteSP;
Would that mean this executes remotely and the remote server makes an RPC to the local server?
insert into remoteTable exec localSP;
It doesn't have to work that way, but if there was a limitation that inserts can only occur on the server the query runs on this would be true.
My limitation is contrived but serves only as an example. If any limitation exists where a class of operations is limited to one or side or the other then queries affected by that limitation will be forced to run on one side or the other. If additionally the the query involves RPC then the reverse RPC would be required.
I can't say this is the case it's common enough that it very well could be.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply