I am recently doing a lot of experiments with linked servers. Last one included enabling remote stored procedure call and it’s really quick – it only requires RPC Out to be set to true in the linked server definition (on the right). You can also use sp_serveroption to set this value.
As it turns out RPC setting does not do anything – see for example here. Remember about granting rights on the remote server – giving too much access may lead to data leaks or uncontrolled data modifications on the remote machine. As usual, we want to have as limited access to remote resources as possible.
I also observed strange behaviour – I used two servers SQL12T1 and SQL12T2 in this demo. I first created linked server on T1 pointing to T2 using SQL Server provider and then I tried to created another one on T2 which points to T1. However, I used default provider (Microsoft OLE DB Provider for SQL Server) without specifying any data connection and my newly created linked server pointed to localhost (take a look below – at first I thought that T1 is redirecting me to T2 somehow, but then it became clear to me it’s connecting to localhost). Keep an eye on it – make sure that you choose SQL Server if you want to input server name only or provide valid connection string for OLE DB.