8

I have a remote database running on an IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is attempting to use named pipes for the connection.

I created a system DSN using the SQL Server Native Client 10 and fed it the IP, Database, User, and Password. Tested it and it was fine.

  1. Went to Linked Servers and Create New.
  2. Specified a name "MAIN"
  3. Selected SQL Server Native Client 10 in the combo box
  4. Entered SQL Server in Product Name Field
  5. Entered my DSN name in Data Source Field
  6. Entered SQLNCLI10 in Provider Name Field
  7. Entered my database name in the Catalog Field
  8. Went to the security options and specified my local to remote login pairing.

After clicking OK, I get an error that it can't find the server and it shows that it is attempting to use named pipes.

What should I do to correct this?

2 Answers 2

10

I don't see a reason why you should be using a DSN. Create the linked server using the SQL Server Native Client directly:

EXEC master.dbo.sp_addlinkedserver
    @server     = N'MAIN',
    @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
    @provider   = N'SQLNCLI', 
    @datasrc    = N'I .P_Address';

Then you can create the local/remote login pairs:

-- Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'MAIN',
    @useself     = N'False',
    @locallogin  = N'Local_user_name',
    @rmtuser     = N'Remote_user_name',
    @rmtpassword = N'Remote_password';v
2
  • That worked! None of the examples say I can use an IP directly.
    – Alex Erwin
    Apr 20, 2013 at 21:16
  • Hi, Which part of the above specifies the use of TCP/IP over Named Pipes?
    – Zeek
    Aug 28, 2019 at 13:22
2

The IP address. If it's an IP it gets interpreted as TCPIP

3
  • Wow. That was quick. Thanks :) I have a similar but not identical link server configuration and it is still trying to use Named Pipes. Looking at the sys.servers table, the only difference I see is that the product (svproduct?) defaulted to 'SQL Server' rather than 'SQLServ' and, for me, both the name (server?) and data_source (datasrc) are the IP number of the remote server. Neither of these differences seem likely to be the cause. Error shown below...
    – Zeek
    Aug 28, 2019 at 13:37
  • OLE DB provider "SQLNCLI11" for linked server "nnn.nnn.156.nnn" returned message "Login timeout expired". OLE DB provider "SQLNCLI11" for linked server "nn.nnn.156.nnn" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". Msg 1326, Level 16, State 1, Line 9 **Named Pipes Provider**: Could not open a connection to SQL Server [1326].
    – Zeek
    Aug 28, 2019 at 13:38
  • Never mind. I just dropped my original remote connection and re-created it using your commands and it now works just fine :) Thank you Spaghetti DBA ;)
    – Zeek
    Aug 28, 2019 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.