aioodbc fails to connect to Microsoft SQL Server 2012 via SQLAlchemy async: pyodbc.OperationalError 08001 / TCP Provider: Error code 0x2746 (10054)

[maretodoric]

Summary

When using SQLAlchemy async with the mssql+aioodbc dialect against SQL Server 2012, establishing a connection fails immediately with

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

The same connection details work against newer SQL Server versions. The failure happens before executing any statement (during connect).

Minimal Reproducible Example

import asyncio

from sqlalchemy import select, text
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

url = "mssql+aioodbc:///?odbc_connect=DRIVER%3D%7BODBC+Driver+17+for+SQL+Server%7D%3BSERVER%3Dtcp%3A<IP>%2C<PORT>%3BDATABASE%3D<DB>%3BUID%3D<USER>%3BPWD%3D%7B<PASS>%7D%3B"

async def run():
    engine = create_async_engine(url, pool_pre_ping=True)
    async_session = async_sessionmaker(engine, expire_on_commit=False, autoflush=False)
    async with async_session() as db:
        # Fails on initial connect, before this actually runs.
        await db.execute(select(text("SELECT 1")))

asyncio.run(run())

Actual Behavior

An exception is raised while creating the connection:

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Full traceback:

Traceback (most recent call last):
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 3298, in raw_connection
    return self.pool.connect()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 713, in checkout
    rec = pool._do_get()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/impl.py", line 180, in _do_get
    self._dec_overflow()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    return self._create_connection()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 675, in __init__
    self.__connect()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 897, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/create.py", line 646, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 625, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)  # type: ignore[no-any-return]  # NOQA: E501
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/connectors/aioodbc.py", line 141, in connect
    await_only(creator_fn(*arg, **kw)),
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/aioodbc/connection.py", line 277, in _connect
    await conn._connect()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/aioodbc/connection.py", line 81, in _connect
    self._conn = await f
  File "/usr/lib64/python3.9/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib64/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/lib64/python3.9/asyncio/base_events.py", line 647, in run_until_complete
    return future.result()
  File "<stdin>", line 5, in run
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/ext/asyncio/session.py", line 463, in execute
    result = await greenlet_spawn(
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 201, in greenlet_spawn
    result = context.throw(*sys.exc_info())
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 2365, in execute
    return self._execute_internal(
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 2241, in _execute_internal
    conn = self._connection_for_bind(bind)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 2110, in _connection_for_bind
    return trans._connection_for_bind(engine, execution_options)
  File "<string>", line 2, in _connection_for_bind
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1189, in _connection_for_bind
    conn = bind.connect()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 3274, in connect
    return self._connection_cls(self)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 148, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2439, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 3298, in raw_connection
    return self.pool.connect()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 1264, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 713, in checkout
    rec = pool._do_get()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/impl.py", line 180, in _do_get
    self._dec_overflow()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    return self._create_connection()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 675, in __init__
    self.__connect()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/pool/base.py", line 897, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/create.py", line 646, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 625, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)  # type: ignore[no-any-return]  # NOQA: E501
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/connectors/aioodbc.py", line 141, in connect
    await_only(creator_fn(*arg, **kw)),
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/aioodbc/connection.py", line 277, in _connect
    await conn._connect()
  File "/home/wildfly/.local/opt/dcclinuxwsbridge/.venv/lib64/python3.9/site-packages/aioodbc/connection.py", line 81, in _connect
    self._conn = await f
  File "/usr/lib64/python3.9/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Newer SQL Server versions work just fine, and I've also attempted to use ODBC Driver 18.0 (since versions later than 18.0 seems to not support SQL Server 2012). In fact, I've started with version 18.5, then downgraded to 18.0 then to 17.10.
Additionally, plain, sync connection works fine with, somewhat similar URL

mssql+pymssql://<user>:<pass>@<ip>:<port>/<db_name>

I'm connecting via Linux so i have to use TCP with providing the port.