Linked server problem

  • 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

  • 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.

  • 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[/url]

  • 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.

  • 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[/url]

  • 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

  • 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[/url]

  • 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

  • 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[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply