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

StackExchange Database Administrators Q#40500, answer score: 10