Connecting to SQL server instance on VM via host-only network

Solution 1:

Resolution

Ok I've managed to resolve this problem by configuring virtual network and configuring SQL Server.

Virtual network configuration

My Host-only configuration was set to automatic but using VMWare this network doesn't have any gateway applied to it, so I added a default Gateway to be the same as DNS (in my case this was 192.168.100.254).

This made it possible for Windows networking to recognise the network as not public but rather private. This means that certain security levels are applied to it differently.

I can now ping either machine one way or the other (from host to VM guest and vice versa). Both pings have a response which they previously didn't since network was labelled as public.

SQL Server configuration

Configuring protocols for SQL Express instance went and configured TCP/IP settings and on the first tab (Protocol) set Listen All to No which means that I have to configure individual IPs on the second tba (IP Addresses) where I've Enabled both localhost IPs (v4 and v6) and also changed the v4 IP address so it reflected virtual host-only network's IP address:

• set IP Address to host-only network address
• set Enabled to Yes

Then cleared all these three IP address TCP Dynamic ports and set static one TCP Port to 1433 that I want to use.

Voila, it works

Now I went back to host and tried connecting SSMS to my VM using the bridged IP address first. It fails. Then as a second try I used host-only IP address and in did I was able to connect to my SQL Server instance. Success.

This completely locked down my SQL Server instance to local machine and my development host.