Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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'IP_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';
share|improve this answer
    
That worked! None of the examples say I can use an IP directly. –  Alex Erwin Apr 20 '13 at 21:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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