Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Connection errors should be more helpful - many scenarios return the same error message #149

Open
jeremyyeo opened this issue Apr 2, 2024 · 3 comments
Open

Connection errors should be more helpful - many scenarios return the same error message #149

jeremyyeo opened this issue Apr 2, 2024 · 3 comments

Comments

@jeremyyeo
Copy link

jeremyyeo commented Apr 2, 2024 •
edited
Loading

Many scenarios that result in connection errors / issues appear to return the same exact error message to the user:

02:22:32  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)')

If we could return something more descriptive, it would help the user debug what the actual issue is.

First setup a baseline working profile:

# ~/.dbt/profiles.yml
fabric:
  target: default
  outputs:
    default: 
      type: fabric
      driver: 'ODBC Driver 18 for SQL Server'
      server: lorem-ipsum.datawarehouse.pbidedicated.windows.net
      port: 1433
      database: fabric_test_instance
      schema: dbt_jyeo
      authentication: ServicePrincipal
      tenant_id: tenant
      client_id: client
      client_secret: secret

Note: I have not used real values for server, tenant_id, client_id and client_secret here but just image those were valid and were true values.

$ dbt debug

02:11:47  Running with dbt=1.7.11
02:11:47  dbt version: 1.7.11
02:11:47  python version: 3.11.2
02:11:47  python path: /Users/jeremy/src/dbt-basic/venv_dbt_1.7.latest/bin/python
02:11:47  os info: macOS-13.4.1-x86_64-i386-64bit
02:11:47  Using profiles dir at /Users/jeremy/.dbt
02:11:47  Using profiles.yml file at /Users/jeremy/.dbt/profiles.yml
02:11:47  Using dbt_project.yml file at /Users/jeremy/src/dbt-basic/dbt_project.yml
02:11:47  adapter type: fabric
02:11:47  adapter version: 1.7.4
02:11:47  Configuration:
02:11:47    profiles.yml file [OK found and valid]
02:11:47    dbt_project.yml file [OK found and valid]
02:11:47  Required dependencies:
02:11:47   - git [OK found]

02:11:47  Connection:
02:11:47    server: lorem-ipsum.datawarehouse.pbidedicated.windows.net
02:11:47    database: fabric_test_instance
02:11:47    schema: dbt_jyeo
02:11:47    UID: None
02:11:47    client_id: client
02:11:47    authentication: ActiveDirectoryServicePrincipal
02:11:47    encrypt: True
02:11:47    trust_cert: False
02:11:47    retries: 1
02:11:47    login_timeout: 0
02:11:47    query_timeout: 0
02:11:47  Registered adapter: fabric=1.7.4
02:11:59    Connection test: [OK connection ok]

02:11:59  All checks passed!

(1) Invalid secret

Remove a single character from a valid secret:

# ~/.dbt/profiles.yml
fabric:
  target: default
  outputs:
    default: 
      type: fabric
      driver: 'ODBC Driver 18 for SQL Server'
      server: lorem-ipsum.datawarehouse.pbidedicated.windows.net
      port: 1433
      database: fabric_test_instance
      schema: dbt_jyeo
      authentication: ServicePrincipal
      tenant_id: tenant
      client_id: client
      client_secret: secre
$ dbt debug

02:14:50  Running with dbt=1.7.11
02:14:50  dbt version: 1.7.11
02:14:50  python version: 3.11.2
02:14:50  python path: /Users/jeremy/src/dbt-basic/venv_dbt_1.7.latest/bin/python
02:14:50  os info: macOS-13.4.1-x86_64-i386-64bit
02:14:50  Using profiles dir at /Users/jeremy/.dbt
02:14:50  Using profiles.yml file at /Users/jeremy/.dbt/profiles.yml
02:14:50  Using dbt_project.yml file at /Users/jeremy/src/dbt-basic/dbt_project.yml
02:14:50  adapter type: fabric
02:14:50  adapter version: 1.7.4
02:14:50  Configuration:
02:14:50    profiles.yml file [OK found and valid]
02:14:50    dbt_project.yml file [OK found and valid]
02:14:50  Required dependencies:
02:14:50   - git [OK found]

02:14:50  Connection:
02:14:50    server: lorem-ipsum.datawarehouse.pbidedicated.windows.net
02:14:50    database: fabric_test_instance
02:14:50    schema: dbt_jyeo
02:14:50    UID: None
02:14:50    client_id: client
02:14:50    authentication: ActiveDirectoryServicePrincipal
02:14:50    encrypt: True
02:14:50    trust_cert: False
02:14:50    retries: 1
02:14:50    login_timeout: 0
02:14:50    query_timeout: 0
02:14:50  Registered adapter: fabric=1.7.4
02:15:26    Connection test: [ERROR]

02:15:26  1 check failed:
02:15:26  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)')

Check your database credentials and try again. For more information, visit:
https://docs.getdbt.com/docs/configure-your-profile

(2) Invalid client

Remove a single character from a valid client_id:

# ~/.dbt/profiles.yml
fabric:
  target: default
  outputs:
    default: 
      type: fabric
      driver: 'ODBC Driver 18 for SQL Server'
      server: lorem-ipsum.datawarehouse.pbidedicated.windows.net
      port: 1433
      database: fabric_test_instance
      schema: dbt_jyeo
      authentication: ServicePrincipal
      tenant_id: tenant
      client_id: clien
      client_secret: secret
$ dbt debug

02:16:29  Running with dbt=1.7.11
02:16:29  dbt version: 1.7.11
02:16:29  python version: 3.11.2
02:16:29  python path: /Users/jeremy/src/dbt-basic/venv_dbt_1.7.latest/bin/python
02:16:29  os info: macOS-13.4.1-x86_64-i386-64bit
02:16:29  Using profiles dir at /Users/jeremy/.dbt
02:16:29  Using profiles.yml file at /Users/jeremy/.dbt/profiles.yml
02:16:29  Using dbt_project.yml file at /Users/jeremy/src/dbt-basic/dbt_project.yml
02:16:29  adapter type: fabric
02:16:29  adapter version: 1.7.4
02:16:29  Configuration:
02:16:29    profiles.yml file [OK found and valid]
02:16:29    dbt_project.yml file [OK found and valid]
02:16:29  Required dependencies:
02:16:29   - git [OK found]

02:16:29  Connection:
02:16:29    server: 5xxoty5si6telax6vdkh6id534-fmmrkvhkp7pu7edhh7uldtp2my.datawarehouse.pbidedicated.windows.net
02:16:29    database: fabric_test_instance
02:16:29    schema: dbt_jyeo
02:16:29    UID: None
02:16:29    client_id: clien
02:16:29    authentication: ActiveDirectoryServicePrincipal
02:16:29    encrypt: True
02:16:29    trust_cert: False
02:16:29    retries: 1
02:16:29    login_timeout: 0
02:16:29    query_timeout: 0
02:16:29  Registered adapter: fabric=1.7.4
02:17:13    Connection test: [ERROR]

02:17:13  1 check failed:
02:17:13  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)')

Check your database credentials and try again. For more information, visit:
https://docs.getdbt.com/docs/configure-your-profile

(3) Invalid username / password

Switch to a different authentication method and use an invalid user/pass (p.s. I actually don't know if my fabric server has got user/pass enabled or not and if that is even a thing but I simply swapped to authentication: ActiveDirectoryPassword and made up some user/password combination).

# ~/.dbt/profiles.yml
fabric:
  target: default
  outputs:
    default: 
      type: fabric
      driver: 'ODBC Driver 18 for SQL Server'
      server: lorem-ipsum.datawarehouse.pbidedicated.windows.net
      port: 1433
      database: fabric_test_instance
      schema: dbt_jyeo
      authentication: ActiveDirectoryPassword
      user: bill
      password: pass
$ dbt debug

02:21:56  Running with dbt=1.7.11
02:21:56  dbt version: 1.7.11
02:21:56  python version: 3.11.2
02:21:56  python path: /Users/jeremy/src/dbt-basic/venv_dbt_1.7.latest/bin/python
02:21:56  os info: macOS-13.4.1-x86_64-i386-64bit
02:21:56  Using profiles dir at /Users/jeremy/.dbt
02:21:56  Using profiles.yml file at /Users/jeremy/.dbt/profiles.yml
02:21:56  Using dbt_project.yml file at /Users/jeremy/src/dbt-basic/dbt_project.yml
02:21:56  adapter type: fabric
02:21:56  adapter version: 1.7.4
02:21:56  Configuration:
02:21:56    profiles.yml file [OK found and valid]
02:21:56    dbt_project.yml file [OK found and valid]
02:21:56  Required dependencies:
02:21:56   - git [OK found]

02:21:56  Connection:
02:21:56    server: lorem-ipsum.datawarehouse.pbidedicated.windows.net
02:21:56    database: fabric_test_instance
02:21:56    schema: dbt_jyeo
02:21:56    UID: bill
02:21:56    client_id: None
02:21:56    authentication: ActiveDirectoryPassword
02:21:56    encrypt: True
02:21:56    trust_cert: False
02:21:56    retries: 1
02:21:56    login_timeout: 0
02:21:56    query_timeout: 0
02:21:56  Registered adapter: fabric=1.7.4
02:22:32    Connection test: [ERROR]

02:22:32  1 check failed:
02:22:32  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)')

Check your database credentials and try again. For more information, visit:
https://docs.getdbt.com/docs/configure-your-profile
@prdpsvs
Copy link
Collaborator

prdpsvs commented Apr 11, 2024

PYODBC allows to capture exceptions listed in here - https://github.com/mkleehammer/pyodbc/wiki/Exceptions
Note that HYT00 captures a range of errors including
image

@prdpsvs
Copy link
Collaborator

prdpsvs commented Apr 11, 2024

Above examples you specified are in the bucket of
image

@prdpsvs
Copy link
Collaborator

prdpsvs commented Apr 11, 2024

PYODBC does not provide any other information except the code and error bucket. It does not provide more than above messages you are seeing as far as I know.

Any thoughts?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jeremyyeo @prdpsvs