Hi all, day 2 of trialing Meltano and I've stalled out trying to connect to local SQL Server. I'm able to connect in a python script with the following:

engine = create_engine(
    f"mssql+pyodbc://{SQLSERVER}:{DB_PORT}/{DATABASE}?"
    "driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes"
    "&authentication=ActiveDirectoryIntegrated")

$meltano invoke tap-mssql returns timeout error: sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)') I don't see windows auth referneced in the docs. I've tried running with and without "authentication: ActiveDirectoryIntegrated" in the yml.

# meltano.yml:
  extractors:
  - name: tap-mssql
    variant: buzzcutnorman
    pip_url: git+<https://github.com/BuzzCutNorman/tap-mssql.git>
    config:
      dialect: mssql
      driver_type: pyodbc
      host: <host>
      port: <portnum>
      database: <dbname>
      sqlalchemy_url_query:
        driver: ODBC Driver 18 for SQL Server
        TrustServerCertificate: yes
        authentication: ActiveDirectoryIntegrated

Any guidance is much appreciated.

Chris, Please give this a try and see if it resolves your issue. https://github.com/BuzzCutNorman/tap-mssql#login-timeout-expired

Really appreciate the quick response. I ran meltano config tap-mssql set sqlalchemy_url_query.MultiSubnetFailover yes & still receive the timeout error.

Updated yml
  - name: tap-mssql
    variant: buzzcutnorman
    pip_url: git+<https://github.com/BuzzCutNorman/tap-mssql.git>
    config:
      dialect: mssql
      driver_type: pyodbc
      host: <host>
      port: <portnum>
      database: <dbname>
      sqlalchemy_url_query:
        driver: ODBC Driver 18 for SQL Server
        TrustServerCertificate: yes
        authentication: ActiveDirectoryIntegrated
        MultiSubnetFailover: yes

What version of SQL Server are you connecting to ? Is the SQL Server in a Availability Group ? Would it be possible to supply the traceback from the command line ?

Version: Microsoft SQL Server 2022 No availability group - running locally on PC for proof of concept traceback:

Catalog discovery failed: command ['/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/bin/tap-mssql', '--config', '/home/cjones/blackbox/meltano_demo/.meltano/run/tap-mssql/tap.73b6ea84-733c-4fc7-a6ac-ddbdf093badc.config.json', '--discover'] returned 1 with stderr:
 Traceback (most recent call last):
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3282, in raw_connection
    return self.pool.connect()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
    with util.safe_reraise():
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
    return self._create_connection()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
    with util.safe_reraise():
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

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

Traceback (most recent call last):
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/bin/tap-mssql", line 8, in <module>
    sys.exit(Tapmssql.cli())
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 1077, in main
    with self.make_context(prog_name, args, **extra) as ctx:
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 943, in make_context
    self.parse_args(ctx, args)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 1408, in parse_args
    value, args = param.handle_parse_result(ctx, opts, args)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 2400, in handle_parse_result
    value = self.process_value(ctx, value)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/click/core.py", line 2362, in process_value
    value = self.callback(ctx, self, value)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/tap_base.py", line 529, in cb_discover
    tap.run_discovery()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/tap_base.py", line 289, in run_discovery
    catalog_text = self.catalog_json_text
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/tap_base.py", line 309, in catalog_json_text
    return json.dumps(self.catalog_dict, indent=2)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/tap_base.py", line 660, in catalog_dict
    result["streams"].extend(connector.discover_catalog_entries())
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/singer_sdk/connectors/sql.py", line 511, in discover_catalog_entries
    inspected = sqlalchemy.inspect(engine)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/inspection.py", line 140, in inspect
    ret = reg(subject)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 303, in _engine_insp
    return Inspector._construct(Inspector._init_engine, bind)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 236, in _construct
    init(self, bind)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 247, in _init_engine
    engine.connect().close()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3258, in connect
    return self._connection_cls(self)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2422, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3282, in raw_connection
    return self.pool.connect()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 168, in _do_get
    with util.safe_reraise():
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
    return self._create_connection()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
    with util.safe_reraise():
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/cjones/blackbox/meltano_demo/.meltano/extractors/tap-mssql/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
(Background on this error at: <https://sqlalche.me/e/20/e3q8>)

Thanks for that information. I am not seeing anything obvious popping out at me. Looking at the pyodbc documentation you might try adding

Encrypt: optional

to see if it resolves your issue.

- name: tap-mssql
    variant: buzzcutnorman
    pip_url: git+<https://github.com/BuzzCutNorman/tap-mssql.git>
    config:
      dialect: mssql
      driver_type: pyodbc
      host: <host>
      port: <portnum>
      database: <dbname>
      sqlalchemy_url_query:
        driver: ODBC Driver 18 for SQL Server
        TrustServerCertificate: yes
        authentication: ActiveDirectoryIntegrated
        MultiSubnetFailover: yes
		Encrypt: optional

https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows#encrypt https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16#encrypt

Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 The only difference I can think of between connecting via python script is I'm running Meltano cli on WSL2.

Hmm I usually just run Meltano directly in Windows and install it via pipx you do need git installed as well. That is beside the point I believe this should still work via WSL2

I'll try that route. Thanks

Do you have TCP/IP enabled in the SQL Server Configuration. I know that on the newer installs that is sometime disabled by default

Thanks. I'll try that path and then a new install from windows/pipx if that doesn't work.