How do I specify a linked server to a remote database over tcp/ip? 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.


Went to Linked Servers and Create New.
Specified a name "MAIN"
Selected SQL Server Native Client 10 in the combo box
Entered SQL Server in Product Name Field 
Entered my DSN name in Data Source Field 
Entered SQLNCLI10 in Provider Name Field 
Entered my database name in the Catalog Field
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?
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