Reopen Issue #491: SSH tunneling fails using paramiko+sshtunnel

[jschuba]

Describe the bug

mssql_python.connect() hangs when connecting using an SSH Tunnel created with paramiko + sshtunnel. This same setup works fine with pymssql or pyodbc.

In issue 491, using mssql-python 1.4.0, the connection would time out after 15 seconds. Using mssql-python 1.6.0, the connection simply hangs indefinitely. The fix reported in PR 541 does not appear to have been tested for this scenario.

To reproduce

See also issue 491

import sshtunnel

import mssql_python
import pymssql
import pyodbc


# Fill in with your credentials
ssh_host = {"ssh_address_or_host": "host",
 "ssh_username": "",
 "ssh_password": "",
 "ssh_port": 22,
 "local_bind_address": ('127.0.0.1', 0),
 }

sql_server = {"host": "DB_HOST_IP",
 "port": 1433,
 "database": "my_database",
 "user": "",
 "password": "",
 }

tunnel = sshtunnel.open_tunnel(
 (ssh_host['ssh_address_or_host'], ssh_host["ssh_port"]),
 ssh_username=ssh_host["ssh_username"],
 ssh_password=ssh_host["ssh_password"],
 local_bind_address=ssh_host["local_bind_address"],
 remote_bind_address=(sql_server['host'], sql_server['port']))

try:
 tunnel.start()

 ### Connect with pyodbc
 pyodbc_conn_str = (
 f"Driver={{ODBC Driver 17 for SQL Server}};"
 f"Server={tunnel.local_bind_host},{tunnel.local_bind_port};"
 f"DATABASE={sql_server['database']};"
 f"UID={sql_server['user']};"
 f"PWD={sql_server['password']};"
 "Encrypt=no;TrustServerCertificate=yes")
 conn = pyodbc.connect(pyodbc_conn_str)
 curs = conn.cursor()
 curs.execute("SELECT 'hello from pyodbc'")
 print(curs.fetchall()) ### This works

 ### Connect with mssql_python
 CONNECTION_STRING = (
 f"Server={tunnel.local_bind_host},{tunnel.local_bind_port};"
 f"DATABASE={sql_server['database']};"
 f"UID={sql_server['user']};"
 f"PWD={sql_server['password']};"
 "Encrypt=no;TrustServerCertificate=yes")

 conn = mssql_python.connect(CONNECTION_STRING) ### This hangs.
 print(conn) ### We never get to here
 res = conn.execute("SELECT 'hello from mssql_python'")
 print(res.fetchall())

finally:
 tunnel.stop()

Expected behavior

mssql_python.connect() should connect via the open SSH Tunnel and should not hang. All expected behavior of mssql_python should work through an SSH Tunnel that is opened by the same python interpreter.

Further technical details

Python version: 3.12 (Windows)
Operating system: Windows 11
sshtunnel version: 0.4.0
paramiko version: 3.5.1
mssql_python version: 1.6.0

[github-actions]

Hi Jonathan Schuba (@jschuba), thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[saurabh500]

Hello Jonathan Schuba (@jschuba). I can reproduce the issue. I am working on a fix (this time I will make sure to add a representative test, however without the SSH dependency).

Essentially there are more GIL contentions to deal with.