Connect to SQL Server running on Windows host from a WSL 2/Ubuntu sqlcmd

Question

I have a host running Windows 10, WSL 2. My guest is Ubuntu.

I'm trying to use sqlcmd to connect to the SQL Server running on my host machine, but I'm not sure what IP to use in the connection?

I've exposed/enabled basically everything from the SQL Configuration Manager on the host Windows SQL Server, and am using commands like this to try to connect:

sqlcmd -S 127.0.0.1 -U sa -P pass

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

Geranial

Answer 1

1. Make sure SQL Server TCP/IP is enabled (under "SQL Server Configuration Manager" under "protocols")
2. Find out the host machine's IP address as seen from WSL2. Run ip route show | grep -i default | awk '{ print $3}' in WSL2. In your connection string, use the IP address from the command above.
3. open the 1433 port in Windows Firewall. Open 'Windows Defender Firewall with Advanced Security' - Right click on 'Inbound Rules'. Go to New rule - Port - Next - Specific ports: 1433 - Next - Allow - Next - Next - Name "WSL2 sql"
4. Disconnect all VPN software in Windows (if any) - it might break WSL2/Windows network connectivity

---

NOTE: You just opened the port to the entire world, so either enable-disable this rule only when needed, or limit this rule to your WSL2 IP address only (double click the rule - Scope - Remote IP ads - Add your WSL2 address mask, it's usually 172.*.*.*. To find your wsl host address type wsl hostname -I in windows cmd shell). Use mask, not the exact address, because the IP changes every time you reboot (?)

Tobe

You can open windows ports pour WSL with this command: New-NetFirewallRule -DisplayName "WSL" -Direction Inbound -InterfaceAlias "vEthernet (WSL)" -Action Allow – Jimmyjimsonweed

Hi Alex, I didn't understand, how to add the new rule, please help me on this? – Conyers

For any who is wondering about the 2nd step: - Open 'Windows Defender Firewall with Advanced Security' - Right click on 'Inbound Rules' which is located on the left hand side. - Follow the post.... – Deva

The Windows Firewall in Windows 11 does not support adding an address mask with wildcards or asterisks, as the answer suggests, so instead I used the IP address subnet 172.16.0.0/12 which covers all the private IP address space for IP addresses beginning with 172 which are the addresses between 172.16.0.0 to 172.31.255.255. – Sherburn

The recommended way to get windows ip address is: ip route show | grep -i default | awk '{ print $3}' (see learn.microsoft.com/en-us/windows/wsl/…) – Jimmyjimsonweed

Answer 2

Have you try looking on the IP on /etc/resolv.conf?

Try connecting using that one, example: sqlcmd -S 192.168.202.65 -U sa -P pass

This is because of this

Cockeyed

Answer 3

For Windows 11 users, running WSL2 Ubuntu 22.04.3, do the following to connect to SQL Server Express running on the host.

1. Make sure TCP/IP is enabled in the SQL Server Configuration Manager.

2. Setup the firewall on windows to allow inbound connections.

   • Named instances of sql server use dynamic ports on Database engine startup. For WSL2 to know what port the server is running on, you have to run the SQL Server Browser service. This can be done from the SQL Server configuration Manager.You also have to allow connections to sqlserv.exe by making a custom program rule in the firewall. Or,

   • If your sql server is using a static/fixed port, or Configure a fixed port yourself, you can make a rule for that specific port in the firewall. In this case, you don't need the SQL Server Browser.

Then, using sqlcmd, you command should look something like this

sqlcmd -C -S [default gateway ip]\\sqlexpress -U [username] -P [password]

Note: the double backslash is important

The -C flag is to avoid the following error

"Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]."

You can find your default gateway from the /etc/resolv.conf file, or run a command like netstat -rn on WSL2.

When using SQL authenctication, you should set the Server authentication mode to "SQL Server and Windows Authentication mode" in the Server properties.

Db

netstat -rn is the right solution if you have set generateResolvConf=false into /etc/wsl.conf to customize the domain name for example. – Jimmyjimsonweed

Answer 4

Enabling the "Virtual Machine Monitoring" worked for me. These four firewall rules are located in the inbound list, names starting with "Virtual Machine Monitoring" for:

• Echo Requests (ICMPv4/v6)
• RPC
• DCOM-In
• NB-Session-In

They appear to be disabled by default and once I enabled them, I was able to ping the host as well connect to my host sql server service using the IP address from the WSL generated resolv.conf file --

cat /etc/resolv.conf
# This file was automatically generated by WSL. To stop automatic generation of this file, add the following entry to /etc/wsl.conf:
# [network]
# generateResolvConf = false
nameserver 1xx.1xx.1xx.1xx

Devilry