Unable to authenticate to a synapse intance with a dedicated pool

dataders commented 6 months ago

hey @noel -- you got a smidge more context to share?

noel commented 6 months ago

haha sorry, did not mean to submit that. I was trying to reproduce and get more info before submitting. Basically I did the following (on Mac)

HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18 also tried HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools

and then install dbt-synapse. This is my profiles.yml

default:
  target: dev
  outputs:
    dev:
      type: synapse
      driver: 'ODBC Driver 18 for SQL Server'
      server: xxx.azuresynapse.net
      port: 1433
      database: xxx_sql_pool_synapse
      schema: dbo
      user: sqladminuser
      password:

I am able to connect to that end point using Azure Data Studio, but dbt would just time out. ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

However, now I cant even get the dbt adapter to show up now. (I had deleted the venv to make sure there wasnt something else)

✗ ls /Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages | grep dbt
dbt
dbt-1.0.0.36.9.dist-info
dbt_core-1.7.9.dist-info
dbt_extractor
dbt_extractor-0.5.1.dist-info
dbt_fabric-1.7.4.dist-info
dbt_semantic_interfaces
dbt_semantic_interfaces-0.4.4.dist-info
dbt_synapse-1.7.0.dist-info
➜ [balboa]balboa (main) ✗ dbt --version
Core:
  - installed: 1.7.9
  - latest:    1.7.9 - Up to date!

Plugins:

dbt debug throws an error

✗ dbt debug
21:16:51  Running with dbt=1.7.9
21:16:51  dbt version: 1.7.9
21:16:51  python version: 3.8.18
21:16:51  python path: /Users/noel/.virtualenvs/balboa/bin/python
21:16:51  os info: macOS-14.3.1-arm64-arm-64bit
21:16:51  Encountered an error:
dlopen(/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/pyodbc.cpython-38-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'
21:16:51  Traceback (most recent call last):
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/cli/requires.py", line 91, in wrapper
    result, success = func(*args, **kwargs)
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/cli/requires.py", line 76, in wrapper
    return func(*args, **kwargs)
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/cli/main.py", line 444, in debug
    results = task.run()
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/task/debug.py", line 123, in run
    load_profile_status: SubtaskStatus = self._load_profile()
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/task/debug.py", line 212, in _load_profile
    profile: Profile = Profile.render(
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 436, in render
    return cls.from_raw_profiles(
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 401, in from_raw_profiles
    return cls.from_raw_profile_info(
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 355, in from_raw_profile_info
    credentials: Credentials = cls._credentials_from_profile(
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 165, in _credentials_from_profile
    cls = load_plugin(typename)
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/factory.py", line 212, in load_plugin
    return FACTORY.load_plugin(name)
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/factory.py", line 58, in load_plugin
    mod: Any = import_module("." + name, "dbt.adapters")
  File "/Users/noel/.asdf/installs/python/3.8.18/lib/python3.8/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/synapse/__init__.py", line 3, in <module>
    from dbt.adapters.synapse.synapse_adapter import SynapseAdapter
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/synapse/synapse_adapter.py", line 6, in <module>
    from dbt.adapters.fabric import FabricAdapter
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/fabric/__init__.py", line 3, in <module>
    from dbt.adapters.fabric.fabric_adapter import FabricAdapter
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/fabric/fabric_adapter.py", line 24, in <module>
    from dbt.adapters.fabric.fabric_connection_manager import FabricConnectionManager
  File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/fabric/fabric_connection_manager.py", line 10, in <module>
    import pyodbc
ImportError: dlopen(/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/pyodbc.cpython-38-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

noel commented 6 months ago

I got past that error by running

pip uninstall pyodbc
pip install --no-binary :all: pyodbc

now dbt debug shows

21:21:27  1 check failed:
21:21:27  dbt was unable to connect to the specified database.
The database returned the following error:

  >Database Error
  ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

deepskydata commented 5 months ago

We have a similar problem. For now, we have rolled back to 1.4.0, which still works.

arthurcht commented 5 months ago

hi @noel @deepskydata ! raised this Pull Request to fix this issue i.e. failing to connect to synapse using authentication: sql and user: sqladminuser.

Before merging PR, please help test if fix works:

in requirements.txt, specify dbt-synapse package as a Github commit, by adding this line: dbt-synapse @ git+https://github.com/microsoft/dbt-synapse@d26c475fb8101b62f456f40f20cd0235e3604aed (instead of using dbt-synapse v1.7.0 from PyPI)
in a fresh virtual environment, install requirements.txt (e.g. run pip install -r requirements.txt)

in profiles.yml, add authentication: sql. user: sqladminuser and everything else remains the same.

default:
target: dev
outputs:
dev:
  type: synapse
  driver: 'ODBC Driver 18 for SQL Server'
  server: xxx.azuresynapse.net
  port: 1433
  database: xxx_sql_pool_synapse
  schema: dbo
  authentication: sql
  user: sqladminuser
  password:

run dbt debug, got All checks passed! on my Windows

-----------optional explanation-------------------

dbt-synapse v1.4.1 onwards, dependency was switched from dbt-sqlserver to dbt-fabric, where synapse_connection_manager inherits fabric_connection_manager

problem: dbt-fabric does not support "sql" as authentication method, so fabric_connection_manager line 317 throws error when trying to authenticate using authentication: sql and user: sqladminuser in profiles.yml

fix: in synapse_connection_manager, overwrite the error-throwing method to accept authentication: sql, and handle it by adding specified user: sqladminuser and password to connection string, to successfully connect with synapse

authentication alternatives for dbt-sqlserver

noel commented 5 months ago

I can confirm this works. That being said, I am on a mac and I had to run this AFTER the pip install because the dependency is for a non-M1 mac

pip uninstall pyodbc
pip install --no-binary :all: pyodbc

I also had to use a different endpoint server: xxx.sql.azuresynapse.net vs server: xxx.azuresynapse.net

arthurcht commented 5 months ago

thanks @noel ! fix is released in Github v1.7.1, also released on PyPI cc @prdpsvs

microsoft / dbt-synapse

Unable to authenticate to a synapse intance with a dedicated pool #218