Connect to SQL Server on Windows from WSL2 with pyodbc - Connection Timeout

Question

I am trying to connect to a SQL server that I have on Windows from WSL2 but I am getting a connection timeout error

I am using this piece of code:

import pyodbc
server = 'DESKTOP-LFOSSEF'
database = 'sportsanalytics'
username = 'user'
password = '{pass}'   
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

I get the following error:

Traceback (most recent call last): File "", line 1, in pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

Any idea If I need any extra configuration to connect to SQL Server installed on Windows?

Regards

Answer 1

You're close, I think. You have:

server = 'DESKTOP-LFOSSEF'

Most people have localhost at this point.

But does DESKTOP-LFOSSEF resolve correctly from within WSL2? My experience is that you usually need either:

• The IP of the primary Windows network adapter. It can be useful to temporarily hardcode this just to eliminate other problems.
• If the Windows host has a DNS name, you could use that. If your current server name isn't resolving, you may need to add the domain.
• The IP of the virtual router provided by the Windows host to WSL2 (ip route show default from within WSL)
• The mDNS name of the Windows host from within WSL2, which should resolve to the IP of the virtual router. WSL2 automatically assigns the Windows "Computer Name" + the ".local" domain as the mDNS name for this address.

Try this:

import pyodbc,socket
server = f'{socket.gethostname()}.local'

Answer 2

It's been a while since I set this up, but I'm pretty sure that the name of your local machine, in this case DESKTOP-LFOSSEF maps back to your (WSL) container, not the host machine; as the container is also given the same hostname. As a result, you could use the IP address for the host machine, which I'm pretty sure is always 172.19.208.1.

I can't recall if the hosts is given a different name in /etc/hosts or not (I've edited mine) so I would check that first. Otherwise you can add an entry for your localhost or edit the one pointing to the host with hosts name (which is what I did).

To edit it, edit /etc/hosts in your favourite command line text editor (I use nano so I would do sudo nano /etc/hosts) and then edit the relevant lines. So mine looks like this:

# This file was automatically generated by WSL. To stop automatic generation of this file, add the following entry to /etc/wsl.conf:
# [network]
# generateHosts = false
127.0.0.1       localhost
# 127.0.1.1     Imentet.localdomain     Imentet
172.19.208.1    imentet

Where imentet is the name of my machine.