Can't connect to MSSQL database pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

[danmash]

Hi, I've trying to connect to my real MSSQL database in docker with the following fixtures. I omit the DB creation part for now just to simplify things. But I can't connect even to the existed database.

@pytest.fixture(scope="session")
def app():
    app = create_app(config_class=TestingConfig)
    return app


@pytest.fixture(scope="session")
def _db(app):
    """
    Provide the transactional fixtures with access to the database via
    a Flask-SQLAlchemy database connection.
    """
    db = SQLAlchemy(app=app)

    return db

I've tried to use different connection timeout settings but still have the Login timeout expired error
Full stacktrace:

```

ERROR
test setup failed
self = Engine(mssql+pyodbc:///?odbc_connect=Driver%3D%7BODBC+Driver+17+for+SQL+Server%7D%3BServer%3Dtcp%3Adb%2C1433%3BDatabase%3Dsqldb-web-prod-001%3BUid%3Dsa%3BPwd%3DCl1mat3m1nd%21%3BEncrypt%3Dno%3BTrustServerCertificate%3Dno%3B)
fn = <bound method Pool.unique_connection of <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>>
connection = None

def _wrap_pool_connect(self, fn, connection):
    dialect = self.dialect
    try:

      return fn()

/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2338:

---

self = <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>

def unique_connection(self):
    """Produce a DBAPI connection that is not referenced by any
    thread-local context.

    This method is equivalent to :meth:`_pool.Pool.connect` when the
    :paramref:`_pool.Pool.use_threadlocal` flag is not set to True.
    When :paramref:`_pool.Pool.use_threadlocal` is True, the
    :meth:`_pool.Pool.unique_connection`
    method provides a means of bypassing
    the threadlocal context.

    """

  return _ConnectionFairy._checkout(self)

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:304:

---

cls = <class 'sqlalchemy.pool.base._ConnectionFairy'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>
threadconns = None, fairy = None

@classmethod
def _checkout(cls, pool, threadconns=None, fairy=None):
    if not fairy:

      fairy = _ConnectionRecord.checkout(pool)

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:778:

---

cls = <class 'sqlalchemy.pool.base._ConnectionRecord'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>

@classmethod
def checkout(cls, pool):

  rec = pool._do_get()

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:495:

---

self = <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>

def _do_get(self):
    use_overflow = self._max_overflow > -1

    try:
        wait = use_overflow and self._overflow >= self._max_overflow
        return self._pool.get(wait, self._timeout)
    except sqla_queue.Empty:
        # don't do things inside of "except Empty", because when we say
        # we timed out or can't connect and raise, Python 3 tells
        # people the real error is queue.Empty which it isn't.
        pass
    if use_overflow and self._overflow >= self._max_overflow:
        if not wait:
            return self._do_get()
        else:
            raise exc.TimeoutError(
                "QueuePool limit of size %d overflow %d reached, "
                "connection timed out, timeout %d"
                % (self.size(), self.overflow(), self._timeout),
                code="3o7r",
            )

    if self._inc_overflow():
        try:
            return self._create_connection()
        except:
            with util.safe_reraise():

              self._dec_overflow()

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:140:

---

self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x405885f4c0>
type_ = None, value = None, traceback = None

def __exit__(self, type_, value, traceback):
    # see #2703 for notes
    if type_ is None:
        exc_type, exc_value, exc_tb = self._exc_info
        self._exc_info = None  # remove potential circular references
        if not self.warn_only:

          compat.raise_(

                exc_value, with_traceback=exc_tb,
            )

/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:68:

---

def raise_(
    exception, with_traceback=None, replace_context=None, from_=False
):
    r"""implement "raise" with cause support.

    :param exception: exception to raise
    :param with_traceback: will call exception.with_traceback()
    :param replace_context: an as-yet-unsupported feature.  This is
     an exception object which we are "replacing", e.g., it's our
     "cause" but we don't want it printed.    Basically just what
     ``__suppress_context__`` does but we don't want to suppress
     the enclosing context, if any.  So for now we make it the
     cause.
    :param from\_: the cause.  this actually sets the cause and doesn't
     hope to hide it someday.

    """
    if with_traceback is not None:
        exception = exception.with_traceback(with_traceback)

    if from_ is not False:
        exception.__cause__ = from_
    elif replace_context is not None:
        # no good solution here, we would like to have the exception
        # have only the context of replace_context.__context__ so that the
        # intermediary exception does not change, but we can't figure
        # that out.
        exception.__cause__ = replace_context

    try:

      raise exception

/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:182:

---

self = <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>

def _do_get(self):
    use_overflow = self._max_overflow > -1

    try:
        wait = use_overflow and self._overflow >= self._max_overflow
        return self._pool.get(wait, self._timeout)
    except sqla_queue.Empty:
        # don't do things inside of "except Empty", because when we say
        # we timed out or can't connect and raise, Python 3 tells
        # people the real error is queue.Empty which it isn't.
        pass
    if use_overflow and self._overflow >= self._max_overflow:
        if not wait:
            return self._do_get()
        else:
            raise exc.TimeoutError(
                "QueuePool limit of size %d overflow %d reached, "
                "connection timed out, timeout %d"
                % (self.size(), self.overflow(), self._timeout),
                code="3o7r",
            )

    if self._inc_overflow():
        try:

          return self._create_connection()

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:137:

---

self = <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>

def _create_connection(self):
    """Called by subclasses to create a new ConnectionRecord."""

  return _ConnectionRecord(self)

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:309:

---

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x405885f340>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x405884cf70>, connect = True

def __init__(self, pool, connect=True):
    self.__pool = pool
    if connect:

      self.__connect(first_connect_check=True)

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:440:

---

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x405885f340>
first_connect_check = True

def __connect(self, first_connect_check=False):
    pool = self.__pool

    # ensure any existing connection is removed, so that if
    # creator fails, this attribute stays None
    self.connection = None
    try:
        self.starttime = time.time()
        connection = pool._invoke_creator(self)
        pool.logger.debug("Created new connection %r", connection)
        self.connection = connection
    except Exception as e:
        with util.safe_reraise():

          pool.logger.debug("Error on connect(): %s", e)

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:661:

---

self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x405885f520>
type_ = None, value = None, traceback = None

def __exit__(self, type_, value, traceback):
    # see #2703 for notes
    if type_ is None:
        exc_type, exc_value, exc_tb = self._exc_info
        self._exc_info = None  # remove potential circular references
        if not self.warn_only:

          compat.raise_(

                exc_value, with_traceback=exc_tb,
            )

/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:68:

---

def raise_(
    exception, with_traceback=None, replace_context=None, from_=False
):
    r"""implement "raise" with cause support.

    :param exception: exception to raise
    :param with_traceback: will call exception.with_traceback()
    :param replace_context: an as-yet-unsupported feature.  This is
     an exception object which we are "replacing", e.g., it's our
     "cause" but we don't want it printed.    Basically just what
     ``__suppress_context__`` does but we don't want to suppress
     the enclosing context, if any.  So for now we make it the
     cause.
    :param from\_: the cause.  this actually sets the cause and doesn't
     hope to hide it someday.

    """
    if with_traceback is not None:
        exception = exception.with_traceback(with_traceback)

    if from_ is not False:
        exception.__cause__ = from_
    elif replace_context is not None:
        # no good solution here, we would like to have the exception
        # have only the context of replace_context.__context__ so that the
        # intermediary exception does not change, but we can't figure
        # that out.
        exception.__cause__ = replace_context

    try:

      raise exception

/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:182:

---

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x405885f340>
first_connect_check = True

def __connect(self, first_connect_check=False):
    pool = self.__pool

    # ensure any existing connection is removed, so that if
    # creator fails, this attribute stays None
    self.connection = None
    try:
        self.starttime = time.time()

      connection = pool._invoke_creator(self)

/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:656:

---

connection_record = <sqlalchemy.pool.base._ConnectionRecord object at 0x405885f340>

def connect(connection_record=None):
    if dialect._has_events:
        for fn in dialect.dispatch.do_connect:
            connection = fn(
                dialect, connection_record, cargs, cparams
            )
            if connection is not None:
                return connection

  return dialect.connect(*cargs, **cparams)

/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py:114:

---

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x405884cdc0>
cargs = ('Driver={ODBC Driver 17 for SQL Server};Server=tcp:db,1433;Database=sqldb-web-prod-001;Uid=sa;Pwd=Cl1mat3m1nd!;Encrypt=no;TrustServerCertificate=no;',)
cparams = {}

def connect(self, *cargs, **cparams):
    # inherits the docstring from interfaces.Dialect.connect

  return self.dbapi.connect(*cargs, **cparams)

E pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py:493: OperationalError

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

request = <FixtureRequest for >

def fill(request):
    item = request._pyfuncitem
    fixturenames = getattr(item, "fixturenames", None)
    if fixturenames is None:
        fixturenames = request.fixturenames

    if hasattr(item, 'callspec'):
        for param, val in sorted_by_dependency(item.callspec.params, fixturenames):
            if val is not None and is_lazy_fixture(val):
                item.callspec.params[param] = request.getfixturevalue(val.name)
            elif param not in item.funcargs:
                item.funcargs[param] = request.getfixturevalue(param)

  _fillfixtures()

/usr/local/lib/python3.8/site-packages/pytest_lazyfixture.py:39:

---

/usr/local/lib/python3.8/site-packages/pytest_flask_sqlalchemy/fixtures.py:31: in _transaction
connection = _db.engine.connect()
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2265: in connect
return self._connection_cls(self, **kwargs)
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:104: in init
else engine.raw_connection()
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2371: in raw_connection
return self._wrap_pool_connect(
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2341: in _wrap_pool_connect
Connection.handle_dbapi_exception_noconnection(
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1583: in handle_dbapi_exception_noconnection
util.raise(
/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:182: in raise
raise exception
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2338: in _wrap_pool_connect
return fn()
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:304: in unique_connection
return _ConnectionFairy._checkout(self)
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:778: in _checkout
fairy = _ConnectionRecord.checkout(pool)
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:495: in checkout
rec = pool._do_get()
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:140: in do_get
self.dec_overflow()
/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:68: in exit
compat.raise(
/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:182: in raise
raise exception
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:137: in _do_get
return self._create_connection()
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:309: in _create_connection
return _ConnectionRecord(self)
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:440: in init
self.__connect(first_connect_check=True)
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:661: in connect
pool.logger.debug("Error on connect(): %s", e)
/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:68: in exit
compat.raise(
/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:182: in raise
raise exception
/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py:656: in __connect
connection = pool._invoke_creator(self)
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py:114: in connect
return dialect.connect(*cargs, **cparams)

---

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x405884cdc0>
cargs = ('Driver={ODBC Driver 17 for SQL Server};Server=tcp:db,1433;Database=sqldb-web-prod-001;Uid=sa;Pwd=Cl1mat3m1nd!;Encrypt=no;TrustServerCertificate=no;',)
cparams = {}

def connect(self, *cargs, **cparams):
    # inherits the docstring from interfaces.Dialect.connect

  return self.dbapi.connect(*cargs, **cparams)

E sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
E (Background on this error at: http://sqlalche.me/e/13/e3q8)

/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py:493: OperationalError

</details>