Search code examples
sql-serverlinked-server

SQL Linked server configuration between VM and Host

I am trying to set up a link between two SQL servers, one on my laptop and the second running on a VM on that same laptop. However I am experiencing some frustrating behavior.

The setup is:

  1. Host: W10 x64 enterprise, SQL Server 2019, Developer. Windows Firewall turned off

  2. VM: W7 Pro x64, SQL Server 2014, Express. Windows Firewall turned off

With this setup I can:

  1. Ping the VM from the Host
  2. Ping the Host from the VM
  3. From the Host, use SQL management studio to connect to the SQL instance on the VM
  4. From the VM, use SQL management studio to connect to the SQL instance on the Host
  5. From the Host create a Linked Server to the VM (using the New Linked Server ... wizard)

But what I can't do is from the VM create a Linked Server to the Host using the same linked server wizard. What happens is that I get a timeout error in the login process.

I am using the same credentials that I use to connect via the management studio. I have tried creating the linked server as both an "SQL Server" and "Other data source" (using SQLNCLI provider) and neither method works.

What could be causing this timeout:

Could it be:

  1. The different SQL versions?

  2. The difference between Express and Developer versions? (I know I can make server links between 2 different Express 2012 instances)

  3. Something that I am completely missing?

Solution

  • It turned out to be "3. Something that I am completely missing"

    A bit of background. In order to support/test various networking models on my desk, the hardware Ethernet ports of all of my computers are configured to respond to two different subnets: 192.168.0.xxx (used as the main transport between 5 various computers) and 192.168.100.xxx (used for testing various pieces of networked hardware).

    In the setup I described in my question I had the following IP address:

    Host: Primary: 192.168.0.99    Secondary: 192.168.100.80
VM:   Primary: 192.168.100.78

    Because the VM only needed to talk to the Host on the 192.168.100 subnet, I only gave it a single (Primary) IP Address. This gave enough networking connectivity that I could perform all the tasks mentioned in the question (and failed on the Linked Server task)

    The solution turned out to be simply changing the Primary and Secondary IP addresses of the VM so that they were on the matching subnets that were configured for the Host's Primary and Secondary IP Addresses. EG:

     Host: Primary: 192.168.0.99 Secondary: 192.168.100.80 
 VM:   Primary: 192.168.0.49 Secondary: 192.168.100.78

    Thus I gave the VM an IP address that it didn't really need. Once this was done I could instantly create a Linked Server from the VM to the Host.

    The requirement for the matching subnets for Primary and Secondary addresses seems to be some artifact of Windows networking combined with SQL Server. I discovered it when I was trying to set up a test with another Windows 10 box, and had the same symptoms as I originally had for the VM.