Linked server over internet not working
Tuna Fly
dest sql server is a sql server 2008 instance - destserver\sql2008
Both TCP and named pipes are enabled, remote connections are enabled on both
src and dest servers.
I'm trying to setup a linked server to a network location in a different
city on a different domain through routers and firewalls. I'm able to ping
the dest server and do a tracert to it and the firewalls are configured to
allow the traffic from the src server on the port in use. I configured the
linked server with the following:
exec sp_addlinkedserver
@server='destserver',
@provider='SQLOLEDB',
@provstr='SERVER=[destserver_ipaddress],[port#];UID=tester;PWD=$testers;',
@datasrc='destserver\sql2008,
@catalog='PUBLIC_DATA_MART',
@srvproduct=''
It runs fine but when I try to test it by right clicking on the linked
server I get the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
OLE DB provider "SQLNCLI" for linked server "destserver" returned message
"Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "destserver" returned message
"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.".
(Microsoft SQL Server, Error: 65535)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3077&EvtSrc=MSSQLServer&EvtID=65535&LinkId=20476
-----------------------------------------
If I test with the stored procedure approach 'sp_testlinkedserver
destserver' I get the following error:
OLE DB provider "SQLNCLI" for linked server "destserver" returned message
"Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "destserver" returned message
"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.".
Msg 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
--------------------------------------------------------
When I try to sniff the traffic on the srcserver I don't see any traffic
when I do either of these tests. I can set up a linked server to a local
server and I see the traffic by doing the test or sending a query which is
successful. I see the traffic when I do a ping or tracert to the destserver.
The support staff at the dest server end took my code and setup a linked
server with the same parameters and it works on their local network so the
username and password do work as well as the other parameters. Any help or
advice is much appreciated... john
Sylvain Lafontaine
the default instance on [destserver_ipaddress] in your connection string for
the provider (@provstr) and the named instance SQL2008 on the 'destserver'
machine in the @datasrc parameter. This confuse the SP up to the point of
using the default SQL Native OLEDB Provider instead of the SQLOLEDB as
asked.
Suggestion: first, make up your mind for the server adress and the protocol
to be used and where to define them. If you are using @provstr, put the
name of the instance there (�
@provstr='SERVER=[destserver_ipaddress]\SQL2008,[port#];UID=tester;PWD=$testers'
� and drop @datasrc.
It's also not clear from your code what the value of 'destserver' is in the
parameter @datasrc='destserver\sql2008' . If this a TCP/IP address like for
@provstr or the name of a local machine on the LAN?
Second, make sure that you are using either SQLNCLI ( �
@provider='SQLNCLI' �) or SQLOLEDB but not both at the same time. The fact
that you ask about SQLOLEDB but get the default SQLNCLI instead probably
indicates that something is wrong somewhere. Maybe because you set
@srvproduct='' to an empty string instead of leaving to its default value of
NULL.
Finally, before setting up a linked server, did you try to directly connect
to the remote server using SSMS (or with sqlcmd, like in � sqlcmd -S
tcp:destserver\SQL2008,1433 �) in order to see that you can go through the
firewall(s)?
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
"Tuna Fly" <tun...@mahi.com> wrote in message
news:0054B0C9-6181-4C41...@microsoft.com...
Erland Sommarskog
> to be used and where to define them. If you are using @provstr, put the
Also try:
SERVER=[destserver_ipaddress],port
Instance name will not work if the Browser Service is not enabled.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Tuna Fly
Thanks, this did help me solve my problem. I had other network problems as
well, but this helped me get the stored procedure configured right. I also
had to go into the properties of the linked server and set the security
option to set the context to be to a sql server login at the destination
server. Just putting the login/password into the @provstr wasn't enough. Once
I got it working I figured out numerous ways to create the linked server....
john
"Erland Sommarskog" wrote:
> Sylvain Lafontaine (sylvainlaf...@yahoo.ca) writes:
> > to be used and where to define them. If you are using @provstr, put the
> > name of the instance there («
> > @provstr='SERVER=[destserver_ipaddress]\SQL2008,[port#];UID=tester;PWD=$testers'
>
> Also try:
>
> SERVER=[destserver_ipaddress],port
>
> Instance name will not work if the Browser Service is not enabled.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>