Access connection from Host to SQL on VM

[M1K3W]

Trying to connect from Windows 10 HOST to a Windows 7 VM.

On the virtual machine I have an SQL 2008 DB I am trying to connect to with access.

The HOST IP DATA is:

Ethernet adapter VMware Network Adapter VMnet1:

Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : VMware Virtual Ethernet Adapter for VMnet1
Physical Address. . . . . . . . . : 00-50-56-C0-00-01
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
Link-local IPv6 Address . . . . . : fe80::719f:d140:5d18:164a%16(Preferred)
IPv4 Address. . . . . . . . . . . : 192.168.216.1(Preferred)
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Lease Obtained. . . . . . . . . . : January 31, 2022 7:59:44 AM
Lease Expires . . . . . . . . . . : February 3, 2022 10:14:43 AM
Default Gateway . . . . . . . . . :
DHCP Server . . . . . . . . . . . : 192.168.216.254
DHCPv6 IAID . . . . . . . . . . . : 201347158
DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-24-08-EA-7A-B0-0C-D1-64-9B-95
DNS Servers . . . . . . . . . . . : fec0:0:0:ffff::1%1
fec0:0:0:ffff::2%1
fec0:0:0:ffff::3%1
NetBIOS over Tcpip. . . . . . . . : Enabled

I can ping the HOST win 10 from inside VM, I can ping the VM from the HOST.

I have not been able to set up an ODBC data connection from HOST to VM at all.

In VM. I have set the protocols for the SQLEXPRESS DB (shared memory, named pipes, tcp/ip and via all to enabled)

In VM. I have changed the firewall to allow port 1433 access.

In VM. I have changed the firewall to allow SQL Browser

I am not sure where I am going wrong but I cannot connect through ODBC into the VM from HOST.

[CarltonR]

As the Win 7 VM is setup as Host Only (VMNet1) VMware network connection, and therefore would suggest looking to the Win 10 Hosts firewall as the relevant ports my be being blocked.

As an aside, you might also like to first test and validate the connection to the Win 7 VM using a NAT VMware Network connection (under, Virtual Machine Settings - Hardware - Network Adapter).

[M1K3W]

C:\PortQryV2>portqry.exe -n 192.168.216.128 -e 1433 -p UDP

Querying target system called:

192.168.216.128

Attempting to resolve IP address to a name...

Failed to resolve IP address to name

querying...

UDP port 1433 (ms-sql-s service): LISTENING or FILTERED

C:\PortQryV2>ping 192.168.216.128

Pinging 192.168.216.128 with 32 bytes of data:
Reply from 192.168.216.128: bytes=32 time<1ms TTL=128
Reply from 192.168.216.128: bytes=32 time<1ms TTL=128
Reply from 192.168.216.128: bytes=32 time<1ms TTL=128
Reply from 192.168.216.128: bytes=32 time<1ms TTL=128

Ping statistics for 192.168.216.128:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

[wila]

MSSQL uses 1433 TCP, not UDP.

Note also that nowadays MSSQL might default to a random high port number instead of TCP 1433, so make sure that SQL server actually listens on that address.
For example by telnetting to port 1433.

FWIW, port 1434 UDP tends to be used for the SQL Browsing service (which is disabled by default) and is most of the times not used as it is more for discovery purposes than anything else.

--
Wil

[CarltonR]

From Microsoft:

Ports Used By the Database Engine

By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434. The table below explains these ports in greater detail. A named instance uses dynamic ports.

[https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-...]

might also be worth reviewing . . .

Configure a Server to Listen on a Specific TCP Port
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-...

[M1K3W]

Thanks for the tip on my host firewall. I tried making the correct changes. I just turned it off (and the VM as well) to remove this for the moment and still no connection

Attempting connection
[Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Connection string is not valid [87].
[Microsoft][SQL Server Native Client 11.0]Login timeout expired
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

This is from the ODBC Management system. I am now trying to figure out what the issue is I noticed it is saying

"connection string is not valid [87]"

which is odd as I did the same thing inside the VM and it works!!.

[wila]

Not odd at all as inside the VM it will use named pipes...

Check that it really uses port 1433

--
Wil

[M1K3W]

from inside SQL Server Config. Mngr.

Shared Memory | Enabled

Named Pipes | Enabled

TCP/IP | Enabled

VIA | Enabled

-----------------------------

TCP/IP properties

IP2

Active | Yes

Enabled | No ?? maybe yes??

IP Address | 192.168.216.128

TCP Dynamic Ports | [empty]

TCP Port | 1433

That's what I set up?? maybe wrong ? I tried enabled [YES] with no difference.

Pardon for this, I am baffled. I know its one small thing I have overlooked...

[wila]

Hi,

Don't feel bad. I've been in databases for over 20 years and still can get a bit baffled at times.

Your settings look OK, the enabled "no" is correct if you have set it for "IPAll", otherwise you probably have to enable it. Note that you have to restart the database engine after making changes here in order for them to be picked up.

Yes it is going to be something small. That's why I suggested to use telnet as that gives you a bit more visual feedback. eg. as long as it doesn't come back with a "could not open connection" error, you're set. Just tried here and there's no banner from any of my MSSQL servers, so not that visual anymore.

Once you rule out that there is an IP service listening at the server, you can move up and look at the other level(s).

1) Check if SQL Server allows for both SQL and Windows Authentication. (Unless you use AD to login)
2) Add the user to the Server Security
3) Map the user to the Database you want.

--
Wil

[M1K3W]

Hello Wila,

HMMM interesting getting a connection error in telnet:

Could not open connection to the host, on port 23: Connect Failed.

Telnet is installed in both VM win 7 and on Host win 10. I am wondering if Win 10 doing something now with the ports. I have firewall turned off on both. I am connected to our "office network". Not sure if this would make a difference as I am only working within my laptop.

[wila]

Hi,

The default port for telnet is 23
You have to pass the mssql port.. eg. 1433

telnet 10.10.10.12 1433

if your mssql is running at ip 10.10.10.12

If the screen goes black... you're fine..
if it shows a banner.. you're fine

if it shows connect failed or time out or something along those lines then you can't connect to the SQL server.

btw, when telnet goes black, type "Ctrl+]" and you'll get a prompt where you can type "quit" to exit telnet.

--
Wil

[M1K3W]

Thank you,

I understand the reference now to port. I did the port 1433 and now it says could not open connection to host on port 1433.

thanks

[M1K3W]

I heard there maybe a "MS Feature" that even though it shows enabled the TCP/IP could still be closed and to review the FACETS inside SQL Management Studio for the Database. Any help here would be appreciated. There was mention of: "Surface Area Configuration" settings to possibly change.

[M1K3W]

I got the SQL 2008 as a redistribution, and having issues with this install I see I cannot get profiler or any other "tools" to trouble shoot this. Any suggestions where I may obtain?

[wila]

Hi,

The last version that had Surface Area Configuration was SQL Server 2005.

See also:

https://www.mssqltips.com/sqlservertip/1673/where-is-the-surface-area-configuration-tool-in-sql-serv...

The steps for configuring are basically this:
https://www.linglom.com/it-support/enable-remote-connection-on-sql-server-2008-express/

If that doesn't work.. something else is going on.
Either a firewall or some sort of misconfiguration.

You mentioned earlier that you couldn't connect over port 1433 remotely.
Does it connect on port 1433 locally?

Try first via localhost.

eg.

telnet 127.0.0.1 1433

Then if that works, try on the SQL Server itself, but now with the actual IP address of the SQL Server.

telnet 192.168.216.128 1433

You should be able to connect, if not then the server is not listening at that IP address yet.

Sorry if that doesn't get you going, it certainly is not a virtualisation issue.

--
Wil

[M1K3W]

Hi,

I actually went to inside the VM and did the same telnet, wouldn't open. I am still hunting down but getting closer. I found that I can locally (on VM) open the 1433 when I added this inside the TCP/IP properties IP4 and IPALL settings then this worked. I was also able to telnet in from the HOST machine into the VM. Still not able to open an ODBC connection though.

However I also noticed on the IPALL settings also had a dynamic port of 60583. I am now thinking I have to open this port up. I will look into this. I am not sure if this works with ODBC connection but will try.

I am not sure why an open firewall doesn't work. I would of assumed so. Now I am thinking its something with the SQL configurations and not sure where or how to get this to work properly. Very frustrating. Where in the SQL SERVER CONFIG or the SQL SERVER MANAGE the issue is however its in those areas.

in grace,

Mike

[M1K3W]

Hi,

HMMM now maybe it is something on the local machine and not the VM. I cannot do:

telnet 127.0.0.1 1433 ... which surprised me I do not have SQL on this machine just the ODBC from microsoft and access. I am just wanting to test to see if the connection is available and works before I go forward with installing SQL serv xx on my laptop. Not sure what this means on my laptop.

The telnet 192.168.216.128 1433 is working now (after I updated the IP4 and IPALL in the sql server config manager originally only told to do ip2 but I decided to try this) There is no DB connection yet as mentioned above.

[M1K3W]

Took this from the errorlog

Spoiler

2022-02-10 11:11:23.56 Server Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)
Mar 26 2015 21:18:04
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

2022-02-10 11:11:23.56 Server (c) Microsoft Corporation.
2022-02-10 11:11:23.56 Server All rights reserved.
2022-02-10 11:11:23.56 Server Server process ID is 3088.
2022-02-10 11:11:23.56 Server System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2022-02-10 11:11:23.56 Server Authentication mode is MIXED.
2022-02-10 11:11:23.56 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
2022-02-10 11:11:23.56 Server This instance of SQL Server last reported using a process ID of 12628 at 2/10/2022 11:11:08 AM (local) 2/10/2022 7:11:08 PM (UTC). This is an informational message only; no user action is required.
2022-02-10 11:11:23.56 Server Registry startup parameters:
-d c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf
-e c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG
-l c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
2022-02-10 11:11:23.56 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2022-02-10 11:11:23.56 Server Detected 4 CPUs. This is an informational message; no user action is required.
2022-02-10 11:11:23.59 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2022-02-10 11:11:23.61 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2022-02-10 11:11:23.63 spid7s Starting up database 'master'.
2022-02-10 11:11:23.65 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2022-02-10 11:11:23.67 spid7s FILESTREAM: effective level = 0, configured level = 3, file system access share name = 'SQLEXPRESS'.
2022-02-10 11:11:23.68 spid7s SQL Trace ID 1 was started by login "sa".
2022-02-10 11:11:23.68 spid7s Starting up database 'mssqlsystemresource'.
2022-02-10 11:11:23.69 spid7s The resource database build version is 10.50.4042. This is an informational message only. No user action is required.
2022-02-10 11:11:23.71 spid7s Server name is 'SPECBREW-VM-DBT\SQLEXPRESS'. This is an informational message only. No user action is required.
2022-02-10 11:11:23.71 spid10s Starting up database 'model'.
2022-02-10 11:11:23.71 Server Warning: Support for the VIA protocol is deprecated and will be removed in a future version of Microsoft SQL Server. If possible, use a different network protocol and disable VIA.
2022-02-10 11:11:23.71 Server Virtual Interface Architecture protocol is not supported for this particular edition of SQL Server.
2022-02-10 11:11:23.73 spid10s Clearing tempdb database.
2022-02-10 11:11:23.82 spid10s Starting up database 'tempdb'.
2022-02-10 11:11:23.85 spid14s The Service Broker protocol transport is disabled or not configured.
2022-02-10 11:11:23.85 spid14s The Database Mirroring protocol transport is disabled or not configured.
2022-02-10 11:11:23.86 spid13s A new instance of the full-text filter daemon host process has been successfully started.
2022-02-10 11:11:23.86 spid14s Service Broker manager has started.
2022-02-10 11:11:23.86 Server A self-generated certificate was successfully loaded for encryption.
2022-02-10 11:11:23.86 Server Server is listening on [ 'any' <ipv6> 1433].
2022-02-10 11:11:23.86 Server Server is listening on [ 'any' <ipv4> 1433].
2022-02-10 11:11:23.86 Server Server is listening on [ 'any' <ipv6> 60583].
2022-02-10 11:11:23.86 Server Server is listening on [ 'any' <ipv4> 60583].
2022-02-10 11:11:23.87 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2022-02-10 11:11:23.87 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2022-02-10 11:11:23.87 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2022-02-10 11:11:23.87 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2022-02-10 11:11:23.87 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2022-02-10 11:11:23.88 spid7s Starting up database 'msdb'.
2022-02-10 11:11:23.91 spid7s Recovery is complete. This is an informational message only. No user action is required.
2022-02-10 11:11:30.13 Logon Error: 17836, Severity: 20, State: 17.
2022-02-10 11:11:30.13 Logon Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 192.168.10.254]
2022-02-10 11:49:48.60 Logon Error: 17836, Severity: 20, State: 17.
2022-02-10 11:49:48.60 Logon Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 192.168.10.254]
2022-02-10 11:59:59.23 spid51 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2022-02-10 11:59:59.23 spid51 Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2022-02-10 12:00:26.25 spid52 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2022-02-10 12:00:26.26 spid52 Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2022-02-10 12:05:32.71 spid52 Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework64\v2.0.50727\.
2022-02-10 12:05:33.40 spid24s AppDomain 2 (mssqlsystemresource.dbo[ddl].1) unloaded.
2022-02-10 12:05:33.95 spid13s AppDomain 3 (mssqlsystemresource.dbo[ddl].2) unloaded.
2022-02-10 12:05:34.52 spid52 AppDomain 4 (mssqlsystemresource.dbo[runtime].3) created.
2022-02-10 12:05:35.07 spid17s AppDomain 5 (mssqlsystemresource.dbo[ddl].4) unloaded.
2022-02-10 12:05:35.59 spid26s AppDomain 6 (mssqlsystemresource.dbo[ddl].5) unloaded.
2022-02-10 12:05:35.65 spid52 Unsafe assembly 'microsoft.sqlserver.mpusqlclrwrapper, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 4 (mssqlsystemresource.dbo[runtime].3).
2022-02-10 12:05:36.25 spid19s AppDomain 7 (mssqlsystemresource.dbo[ddl].6) unloaded.

not sure if this helps? will go through line by line

moderator edit by wila: moved log snippet to spoiler

[M1K3W]

Logon Error: 17836, Severity: 20, State: 17.

Never mind this is the Telnet login from the local machine. Still wondering why I cant get in on the ODBC sql odd.

[wila]

Hi,

---

@M1K3W wrote:

The telnet 192.168.216.128 1433 is working now (after I updated the IP4 and IPALL in the sql server config manager originally only told to do ip2 but I decided to try this) There is no DB connection yet as mentioned above.

---

OK.. without that you could never connect from a remote host.
Try telnet again from the other machine. Does it connect now?

--
Wil