Sql-server – How to force a linked SQL Server to use TCP/IP instead of Named Pipes

named-pipessql servertcpip

I have a SQL Server instance, Instance1, that has a linked SQL Server, Instance2. The linked server technically works, however I am positive that Instance1 is connecting to Instance2 via Named Pipes. I can see this very clearly by testing a sample query using TCP/IP and Named Pipes. Query run times are a difference between <1 second for TCP/IP and >45 seconds for Named Pipes.

Can I force the linked SQL Server on Instance1 to use TCP/IP when connecting to Instance2? I have not been able to find any information relating to this, do I have any other options if this can't be done?

I tried creating the linked server by specifying the port but it seems to use Named Pipes anyway, i.e., I created it as Instance2,1433. Is there maybe a way to tell the master database to use TCP/IP for linked servers?

Any help is greatly appreciated. I'm hoping the answer is not "Turn off Named Pipes in Instance2 SQL configuration" although I realize I might have to do that if I can't get this to work.

Edit: I have without a doubt verified that it is Named Pipes that is causing the problem, and I've seen this same problem with Named Pipes before (it's well documented behaviour of Named Pipes). I just haven't seen how to solve it when using Linked Servers.

Best Answer

I tried, with success, the Connection String method to force a protocol: Data Source=protocol:servername, portnumber;

Example:

EXEC master.dbo.sp_addlinkedserver @server = N'DB_OBRA', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'tcp:10.0.34.33'