search

microsoft / dbt-fabric

MIT License
75 stars 26 forks source link

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

Open jeremyyeo opened 6 months ago

jeremyyeo commented 6 months ago

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 commented 6 months ago

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 commented 6 months ago

Above examples you specified are in the bucket of image

prdpsvs commented 6 months ago

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?