Using `localhost` in database connection string results in timeout #1125

svintuss · 2022-11-17T15:45:34Z

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)')

The text was updated successfully, but these errors were encountered:

gordthompson · 2022-11-17T17:39:32Z

What do you see when you ping localhost? Do you see

64 bytes from localhost (127.0.0.1)

or

64 bytes from localhost (::1)

v-chojas · 2022-11-17T17:40:58Z

pyODBC just passes the connection string through, so this shouldn't be related to pyODBC at all... but

OS: macOS 12.6.1 Apple Silicon
DB: Microsoft SQL Server 2019

localhost? How are you running SQL Server on macOS?

svintuss · 2022-11-17T17:44:19Z

@gordthompson

PING localhost (127.0.0.1): 56 data bytes
...

@v-chojas I use ssh port forwarding to a remote machine. BTW other tools (e. g. Azure Data Studio) have no issues connecting to localhost.

gordthompson · 2022-11-17T19:12:38Z

Try testing with just the isql utility. This is what I get on Ubuntu 22.04:

gord@xubu-22-04:~$ cat <<EOT >> ~/.odbc.ini
> [mssql_localhost]
> Driver=ODBC Driver 17 for SQL Server
> Server=localhost
> Database=master
> 
> [mssql_127]
> Driver=ODBC Driver 17 for SQL Server
> Server=127.0.0.1
> Database=master
> EOT
gord@xubu-22-04:~$ cat ~/.odbc.ini
[mssql_localhost]
Driver=ODBC Driver 17 for SQL Server
Server=localhost
Database=master

[mssql_127]
Driver=ODBC Driver 17 for SQL Server
Server=127.0.0.1
Database=master
gord@xubu-22-04:~$ 
gord@xubu-22-04:~$ isql mssql_127 scott tiger^5HHH -v
debug1: Connection to port 1433 forwarding to 192.168.0.199 port 1433 requested.
debug1: channel 3: new [direct-tcpip]
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
gord@xubu-22-04:~$ 
gord@xubu-22-04:~$ isql mssql_localhost scott tiger^5HHH -v
[S1T00][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2749
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]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.
[ISQL]ERROR: Could not SQLConnect

svintuss · 2022-11-17T19:32:08Z

@gordthompson I get the same results: no connection with localhost.
Do you mean that unixODBC is the culprit?

% isql mssql_127 myName myPassword -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
% isql mssql_localhost myName myPassword -v
[S1T00][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2726
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]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.
[ISQL]ERROR: Could not SQLConnect

v-chojas · 2022-11-17T19:40:26Z

@gordthompson could you try strace'ing and see what IP it's actually trying to connect to?

0x2749 = 10057 (WSAENOTCONN)
0x2726 = 10022 (WSAEINVAL)

gordthompson · 2022-11-17T20:02:39Z

@v-chojas - Looks like it might have something to do with Ubuntu's trick of having separate entries in /etc/hosts for ip4

gord@xubu-22-04:~$ cat /etc/hosts
127.0.0.1	localhost
127.0.1.1	xubu-22-04

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

but that's just a guess.

strace_localhost.txt

v-chojas · 2022-11-17T20:54:09Z

connect(3, {sa_family=AF_INET, sin_port=htons(1433), sin_addr=inet_addr("127.0.1.1")}, 16)

If your service was listening on 127.0.0.1 then that would certainly not work.

I don't know why the version of pyODBC could have anything to do with this. @svintuss could you get a strace with an older version of pyODBC (where you claim it works) and compare?

gordthompson · 2022-11-17T21:23:30Z

re: "No such issue in version 4.0.34."

FWIW, I get the same errors using 4.0.34:

gord@xubu-22-04:~$ pip3 install --user pyodbc==4.0.34
Collecting pyodbc==4.0.34
  Downloading pyodbc-4.0.34-cp310-cp310-manylinux_2_24_x86_64.whl (474 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 474.6/474.6 KB 902.2 kB/s eta 0:00:00
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.34
gord@xubu-22-04:~$ python3
Python 3.10.6 (main, Nov  2 2022, 18:53:38) [GCC 11.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> cnxn = pyodbc.connect("DSN=mssql_localhost;UID=scott;PWD=tiger^5HHH")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.OperationalError: ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
>>> cnxn = pyodbc.connect("DSN=mssql_127;UID=scott;PWD=tiger^5HHH")
debug1: Connection to port 1433 forwarding to 192.168.0.199 port 1433 requested.
debug1: channel 3: new [direct-tcpip]
>>> cnxn.execute("SELECT 'Connected.' AS foo").fetchval()
'Connected.'
>>>

svintuss · 2022-11-18T09:04:50Z

@gordthompson, @v-chojas sorry, my initial statement that ver. 4.0.34 works fine is misleading. I can't reproduce a successful connection on it either. I might have messed something up while switching versions in the first place.

@v-chojas sorry, strace requires Linux, I have macOS. Is there any other way to check where it is trying to connect to?

gordthompson · 2022-11-18T14:17:52Z

Do you mean that unixODBC is the culprit?

It does look like unixODBC and the ssh tunnelling are not playing nicely together. Server=localhost works fine when the SQL Server instance really is local (and no tunnelling is required).

gord@xubu-20-04-vm1:~$ isql mssql_localhost scott tiger^5HHH -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

In any case this does not appear to be a pyodbc issue.

v-chojas · 2022-11-18T15:47:22Z

That is extremely odd. unixODBC has literally zero networking-related code. @gordthompson in that case, does it try to connect to 127.0.0.1 or 127.0.1.1, and is the SQL Server listening on 0.0.0.0 (all IPs)? In the case of ssh tunnel, is it only listening on a specific destination IP like 127.0.0.1 and not 127.0.1.1 ?

@svintuss netstat should show the attempts at connecting. Something like netstat -an | grep 127

gordthompson · 2022-11-18T16:35:37Z

in that case, does it try to connect to 127.0.0.1 or 127.0.1.1,

connect(3, {sa_family=AF_INET, sin_port=htons(1433), sin_addr=inet_addr("127.0.1.1")}, 16) = -1 EINPROGRESS (Operation now in progress)

and is the SQL Server listening on 0.0.0.0 (all IPs)?

Not sure. I just did a vanilla install of SQL Server on Linux. (I've never used it before.)

v-chojas · 2022-11-18T16:59:22Z

Yes, the default should be to listen on all IPs. netstat -an | grep 1433 will show what it's listening on.

gordthompson · 2022-11-18T17:08:17Z

Yes, it's listening on 0.0.0.0

gordthompson · 2022-11-18T18:55:46Z

Okay, so if I do

sudo /opt/mssql/bin/mssql-conf set network.ipaddress 127.0.0.1
sudo systemctl restart mssql-server.service

then trying to connect to localhost with isql fails. Then if I do

sudo /opt/mssql/bin/mssql-conf set network.ipaddress 127.0.1.1
sudo systemctl restart mssql-server.service

then trying to connect to localhost with isql succeeds.

svintuss · 2022-11-18T20:55:55Z

@v-chojas after getting up an ssh tunnel netstat gives the following

% netstat -an | grep 1433
tcp4       0      0  127.0.0.1.1433         *.*                    LISTEN     
tcp6       0      0  ::1.1433               *.*                    LISTEN

My /etc/hosts on macOS has only 127.0.0.1:

% cat /etc/hosts
##
# Host Database
#
# localhost is used to configure the loopback interface
# when the system is booting.  Do not change this entry.
##
127.0.0.1	localhost
255.255.255.255	broadcasthost
::1             localhost

gordthompson · 2022-11-19T15:59:21Z

@svintuss - Something you might try:

When I commented out the 127.0.1.1 line in my /etc/hosts file …

127.0.0.1	localhost
#127.0.1.1	xubu-20-04-vm1

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

… I found that isql still couldn't connect to localhost. However, when I added my machine name to the 127.0.0.1 line

127.0.0.1	localhost xubu-20-04-vm1

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

then it started working.

gordthompson · 2022-11-19T18:15:40Z

@svintuss - You might also try temporarily changing your

::1             localhost

to

::1             ip6-localhost

in case the two localhost entries are confusing things.

gordthompson · 2022-11-20T12:59:06Z

@v-chojas wrote:

unixODBC has literally zero networking-related code.

It appears that this is not a unixODBC issue after all. The following test works with MariaDB ODBC on Ubuntu 22.04:

$ sudo apt install mariadb-server odbc-mariadb unixodbc net-tools
$ sudo mysql
MariaDB [(none)]> CREATE USER scott@'%' IDENTIFIED BY 'tiger';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'scott'@'%';
MariaDB [(none)]> quit
$ cat <<EOT >> ~/.odbc.ini
> [mariadb_localhost]
> Driver=MariaDB Unicode
> Server=localhost
> Database=mysql
> EOT
$ grep bind-address /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address            = 127.0.0.1
$ netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN
$ strace -o strace_isql.txt isql mariadb_localhost scott tiger -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

strace_isql.txt

This is starting to look like an issue with the msodbcsql drivers.

v-chojas · 2022-11-21T18:46:50Z

What does this short test program output?

#include <stdio.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <netdb.h>

int main() {
 struct addrinfo hi = { 0 };
 struct addrinfo *res = 0;
 hi.ai_family = AF_INET;
 hi.ai_socktype= SOCK_STREAM;
 int ret = getaddrinfo("localhost","1433",&hi,&res);
 if(ret)
  printf("Error %d\n", ret);
 else {
  struct addrinfo *ai = res;
  while(ai) {
   printf("%s\n", inet_ntoa(((struct sockaddr_in*)ai->ai_addr)->sin_addr));
   ai = ai->next;
  }
  freeaddrinfo(res);
 }
 return ret;
}

(Written directly here; you may have to make changes to compile.)

gordthompson · 2022-11-21T19:58:05Z

On the machine with the active ssh tunnel and the original /etc/hosts entries, i.e.,

127.0.0.1	localhost
127.0.1.1	xubu-22-04

gcc suggested that I change line 20 from

ai = ai->next;

to

ai = ai->ai_next;

so it would compile. When I run it I get

gord@xubu-22-04:~$ ./vc
127.0.0.1

strace_vc.txt

v-chojas · 2022-11-23T16:45:12Z

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.

gordthompson · 2022-11-23T17:20:37Z

Thanks for the explanation!

svintuss · 2022-11-24T07:17:11Z

@v-chojas thank you for this clarification. I have successfully connected using foo host name but not localhost despite they both point to 127.0.0.1.
I remember being able to use localhost on a mac some time ago and it greatly simplified migrating scripts from one machine to another, but now both v17 and v18 MSSQL drivers won't connect.

gordthompson · 2022-11-24T15:24:04Z

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 added the Environment or Driver Issue label Nov 18, 2022

gordthompson changed the title ~~Using localhost in database connection string results in timeout in pyodbc 4.0.35~~ Using localhost in database connection string results in timeout Nov 23, 2022

gordthompson closed this as not planned Won't fix, can't repro, duplicate, stale Nov 23, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Using `localhost` in database connection string results in timeout #1125

Using `localhost` in database connection string results in timeout #1125

svintuss commented Nov 17, 2022 •

edited

gordthompson commented Nov 17, 2022

v-chojas commented Nov 17, 2022

svintuss commented Nov 17, 2022 •

edited

gordthompson commented Nov 17, 2022

svintuss commented Nov 17, 2022 •

edited

v-chojas commented Nov 17, 2022

gordthompson commented Nov 17, 2022

v-chojas commented Nov 17, 2022

gordthompson commented Nov 17, 2022

svintuss commented Nov 18, 2022 •

edited

gordthompson commented Nov 18, 2022

v-chojas commented Nov 18, 2022

gordthompson commented Nov 18, 2022

v-chojas commented Nov 18, 2022

gordthompson commented Nov 18, 2022

gordthompson commented Nov 18, 2022 •

edited

svintuss commented Nov 18, 2022

gordthompson commented Nov 19, 2022 •

edited

gordthompson commented Nov 19, 2022 •

edited

gordthompson commented Nov 20, 2022

v-chojas commented Nov 21, 2022

gordthompson commented Nov 21, 2022 •

edited

v-chojas commented Nov 23, 2022

gordthompson commented Nov 23, 2022

svintuss commented Nov 24, 2022

gordthompson commented Nov 24, 2022

Using localhost in database connection string results in timeout #1125

Using localhost in database connection string results in timeout #1125

Comments

svintuss commented Nov 17, 2022 • edited

Environment

Issue

gordthompson commented Nov 17, 2022

v-chojas commented Nov 17, 2022

svintuss commented Nov 17, 2022 • edited

gordthompson commented Nov 17, 2022

svintuss commented Nov 17, 2022 • edited

v-chojas commented Nov 17, 2022

gordthompson commented Nov 17, 2022

v-chojas commented Nov 17, 2022

gordthompson commented Nov 17, 2022

svintuss commented Nov 18, 2022 • edited

gordthompson commented Nov 18, 2022

v-chojas commented Nov 18, 2022

gordthompson commented Nov 18, 2022

v-chojas commented Nov 18, 2022

gordthompson commented Nov 18, 2022

gordthompson commented Nov 18, 2022 • edited

svintuss commented Nov 18, 2022

gordthompson commented Nov 19, 2022 • edited

gordthompson commented Nov 19, 2022 • edited

gordthompson commented Nov 20, 2022

v-chojas commented Nov 21, 2022

gordthompson commented Nov 21, 2022 • edited

v-chojas commented Nov 23, 2022

gordthompson commented Nov 23, 2022

svintuss commented Nov 24, 2022

gordthompson commented Nov 24, 2022

Using `localhost` in database connection string results in timeout #1125

Using `localhost` in database connection string results in timeout #1125

svintuss commented Nov 17, 2022 •

edited

svintuss commented Nov 17, 2022 •

edited

svintuss commented Nov 17, 2022 •

edited

svintuss commented Nov 18, 2022 •

edited

gordthompson commented Nov 18, 2022 •

edited

gordthompson commented Nov 19, 2022 •

edited

gordthompson commented Nov 19, 2022 •

edited

gordthompson commented Nov 21, 2022 •

edited