I was going to include a nice diagram of my situation, but, apparently I am not approved to include images yet, so, I will try to explain it verbally.
I have two SQL server machines (let's call them DEV and PRD), each with multiple instances. So, as far as instances go, we have something like...
- PRD (port 1433)
- PRD\NamedInstance1 (dynamic ports)
- PRD\NamedInstance2 (a different static port)
- DEV (port 1433)
- DEV\NamedInstance1 (dynamic ports)
- DEV\NamedINstance2 (a different static port)
I am trying to create some Linked Server connections between several of those instances. I am creating the Linked Servers using the SSMS GUI and am using option number one, Server Type = SQL Server (as opposed to using 'Other Data Source').
All of my connections work great with the exception of any attempt to connect to the Default instance on PRD from DEV (specifically from that server).
Whenever I try to create a Linked Server connection to the Default instance on PRD from DEV, I get "TCP Provider: no connection could be made because the target machine actively refused it....Microsoft SQL Server, Error 10061." Here is the weird
part though, if I try to make that same connection from DEV to PRD using the IP address and port, I can create it successfully. Even weirder, other servers can connect to the default instance on PRD using just the server name no problem; it is only my DEV
server that cannot, and only when I try to connect to the default instance on PRD.
Several things came to mind right away and, with the help of some Web searches, I fell like I have ruled a lot of the common Linked Server issues out. Firewalls (and hence blocked ports) are not an issue as they are Off on the servers involved. I can ping
the PRD server from DEV using its network name no problem. A port query tool we use reveals that the PRD machine is listening on 1433 (TCP) and 1434 (UDP). When you query the UDP port with this tool, part of the response of course (since SQL Browser is running
on PRD) is all of the instances available; the Default instance as well as the named instances are listed among those.
I ruled out any permissions issues because I can connect directly to the target instance through SSMS using the credentials I want to use for Linked Server connection.
I guess what I am saying is that I have been through many of the troubleshooting steps that people have recommended for resolving Linked Server issues, and, for the most part, I am having success with Linked Servers between these instances, it is just one
particular connection, from one particular server to another (DEV to PRD) that is not working.
Couple more things: there are no aliases on DEV or PRD, at least none listed in SQL Server Configuration Manager, named pipes protocol is Off everywhere (I should be able to do this with TCP/IP).
Oh yes, in case it seems relevant, these are all SQL Server 2012 SP2 (11.0.5343). The native client on each machine is at 11.2.5058. Servers are Windows Server 2012 R2.
Thanks for any help you can offer. Mainly I am looking for clues as to how to diagnose the issue. I have tried looking in the Application Log on the PRD machine but find nothing helpful about why the connection is being "actively refused".
