Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server problem Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 7:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 31, Visits: 4,670
Hi guys,

I'm trying to set up a linked server on a SQL 2005 server (on say domain A) to a SQL 2008 server on a different domain (domain B). I've done this before on the same 2005 server to a different server on domain B without problems. However for this particular server I get the following error:

"The linked server has been updated but failed a connection test.
SQL network interfaces error: Error locating server\instance specified [xFFFFFFFF]. (Microsoft SQL server, Error: 65535)"

The only difference between this server and the one that worked is that this one uses a named instance. The server actually has 2 instances (I can't link to either).
I have created on login on the 2008 server and am using those credentials in the security section.

btw I can connect no problem via SSMS.

I have tried various things,such as using an alias to no avail. Also I can create a linked server to this instance from a different server (also on domain B) and it works fine.

Any help gratefully received. I've spent ages on this!

Kind Regards
Andrew
Post #1397788
Posted Tuesday, December 18, 2012 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
Do you have a firewall in place between domain A and domain B for the Server VLAN? If so you will need to ensure that the correct ports are open between the two.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397822
Posted Tuesday, December 18, 2012 8:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:24 AM
Points: 1,399, Visits: 2,636
You might not be able to use SERVERNAME\INSTANCE when creating the linked server. try SERVERNAME,PORT. This may be because the SQL browser service is unable to cross your domains. I had this exact situation previously and just used the servername,port notation with the linked server.

By default the ports for named instances are dynamic and can change after every restart so if you haven't configured static ports for your named instance you would either need to get SQL Browser working across the domains or configure a static port and use the servername,port notation.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1397827
Posted Tuesday, December 18, 2012 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
You can reference the named instance in the linked server configuration


On the General page, in the Linked server box, type the name of the instance of SQL Server that you area linking to.

Note

If the instance of SQL Server is the default instance, enter the name of the computer that hosts the instance of SQL Server. If the SQL Server is a named instance, enter the name of the computer and the name of the instance, such as Accounting\SQLExpress.


http://msdn.microsoft.com/en-us/library/ff772782%28v=sql.105%29.aspx

But it does sound like some sort of port opening issue between the two servers.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397831
Posted Tuesday, December 18, 2012 8:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:24 AM
Points: 1,399, Visits: 2,636
anthony.green (12/18/2012)
But it does sound like some sort of port opening issue between the two servers.


Agreed, most likely, the SQL Browser port. you can test if it is open by start a command prompt window and typing
telnet servername 1434


you can also check if it is trying to use SQL Browser by running folling in command prompt window whil trying to connect with your linked server
netstat -an | find "1434"


If there is no communication between the you on port 1434 it will not return results on that port. If there is a row with a status of SYN_SENT then it will be networking (firewall/router ACL) that is the issue.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1397844
Posted Tuesday, December 18, 2012 8:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 31, Visits: 4,670
Hi,

Thanks for the info.

I'm a little bit unsure on some of these questions such as the ports and firewall. As far as I know there are no restrictions in the firewall as I can connect to all the other servers in domain B although that is out of my area/control! As for the port I've RDP'd onto the server and in sql configuration manager I've had a look in the IP address tab in the TCP/IP section and for one instance (in the IP1 section) the port is 1433 but for the other it is blank, however for that instance in the IPAll section the TCP dynamics port is 3249?

How do I use the port number when setting up the linked server? I tried servername,1433 but it said no such host is known.

Andrew
Post #1397854
Posted Tuesday, December 18, 2012 8:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:24 AM
Points: 1,399, Visits: 2,636
it looks like your port for the named instance is dynamic so without making any configuration changes you will have to get SQL Browser working.

First, is SQL Browser running? next can you telnet to the SQL Browser port (1434) on the Target server from the source server and vice versa, I am not sure which way the communication works with SQL browser. Also, do a telnet on the source server to port 3249 on the target server to check if that communication is open.

telnet targetserver 3249


If the network path is open the command prompt window will go blank with a blinking cursor.

if you don't want to try telnet you can try creating the linked server by using the name "servername,3249" although this may break when the target server is restarted because the named instance may choose a new port.

EXEC master.dbo.sp_addlinkedserver @server = N'SERVERNAME,3249', @srvproduct=N'SQL Server'



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1397867
Posted Tuesday, December 18, 2012 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 31, Visits: 4,670
Hi Bob,

I think I'm getting somewhere.....

I tried both of what you suggested and both failed. Telnet said 'could not open connection to host' and the linked server using the port said 'no such host found'.
I then tried telnet again but used the FQDN and it connected! I then put that in the linked server and that works too. I did try the FQDN yesterday but didn't know how the syntax worked when using a named instance (I still don't) but it works with the port.

I now know, as you suggest, there will be an issue with this if that instance is restarted due to it being dynamic. How do we overcome that?

Andrew
Post #1397874
Posted Tuesday, December 18, 2012 9:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:24 AM
Points: 1,399, Visits: 2,636
My preferred way would be to make the port static on the server with the named instance.

You would do this by choosing a port not in use on the server as well as a port that is not a standard port for any other service or program. You would not want to use 3389(rdp), 443(ssl), 3306(MySQL). Here is a list of standard ports (http://en.wikipedia.org/wiki/List_of_TCP_and_UDP_port_numbers)

Enter whatever port you choose into the TCP Port field for the IP of the server.(http://support.microsoft.com/kb/823938) you will need to restart SQL Server for the change to take effect.

If you do this you can then create an alias in "SQL Native Client Configuration" in configuration manager on the source server point to the servername and port and create your linked server with just the alias name.

other option is to get SQL Browser working between the servers and then you don't have to give the named instance a static IP and you could use SERVERNAME/INSTANCE for your linked server.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1397884
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse