pyodbc: Using `localhost` in database connection string results in timeout

Hi.

Version 4.0.35 of pyodbc can’t connect to Microsoft SQL Server if localhost is specified in the connection string. Using 127.0.0.1 works fine. I haven’t been able to check whether it won’t accept any domain name or just localhost. ~~No such issue in version 4.0.34.~~ - that is misleading, I can’t reproduce successful connection on ver. 4.0.34 either.

Environment

Python: Checked with both 3.10.6, 3.11.0
pyodbc: 4.0.35
OS: macOS 12.6.1 Apple Silicon
DB: Microsoft SQL Server 2019
driver: Checked with both ODBC Driver 17 and 18 for SQL Server

Issue

import pyodbc 
server = 'localhost' 
database = 'myDB' 
username = 'myName' 
password = 'myPassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;TrustServerCertificate=yes;UID='+username+';PWD='+ password)
cursor = cnxn.cursor()


>> OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 26 (7 by maintainers)

Most upvoted comments

Off the top of my head, the only reason I can see to prefer localhost over 127.0.0.1 is if you were deploying scripts to some machines that only supported IPV6 so localhost would be ::1 instead of 127.0.0.1.

However, I can think of two possible workarounds:

(1) Get Python to do the lookup for you. Instead of hard-coding localhost in your connection string you could use

import socket

connection_string = (
    "Driver=ODBC Driver 17 for SQL Server;"
    f"Server={socket.gethostbyname('localhost')};"
)
print(connection_string)
# Driver=ODBC Driver 17 for SQL Server;Server=127.0.0.1;

(2) Get the ssh tunnel to listen on 0.0.0.0, perhaps using something like one of the techniques described here

https://stackoverflow.com/q/23781488/2144390

gordthompson on Nov 24, 2022

The ODBC Driver for SQL Server uses the actual hostname of the current machine if you specify “localhost” because that is necessary for things like server certificate verification and Kerberos authentication. Thus if your machine is named “foo” and you specify “localhost”, it will connect using “foo” instead of “localhost”. If they resolve to the same IP, or different IPs of the same machine where the service is listening, there is no problem. This is the normal case. However, if localhost and the machine’s hostname resolve to different IPs, and the service is listening only on one of them, then you will encounter this.

v-chojas on Nov 23, 2022