My credentials keep timing out on dbt

Question

I have Azure Synapse Studio connected to dbt. My credentials are all correct in my dbt file but the login keeps timing out.

I have all of my login information correct, and just changed the admin password on Azure to ensure that it is correct. Attached a screenshot of the database name and login.

The format of the dbt yml format: https://docs.getdbt.com/docs/core/connect-data-platform/profiles.yml

And this is what my yml looks like (without username and password):

dbt_setup:
  target: dev
  outputs:
    dev:
      type: synapse
      driver: 'ODBC Driver 18 for SQL Server' # (The ODBC Driver installed on your system)
      server: guikerapp.database.windows.net # (Dedicated SQL endpoint of your workspace here)
      port: 1433
      database: fivetrandata
      schema: default
      user: 
      password: 

This is the error when I run dbt models: Database Error

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

Can anyone help

Answer 1

@Jake Watson

Thanks for reaching out to Microsoft Q&A

The error message you're getting "Login timeout expired" usually indicates that dbt is unable to establish a connection to your Azure Synapse Workspace due to a timeout. The screenshot you sent confirms that the server name, guikerapp.database.windows.net, and location, East US, are configured correctly in your dbt profile.

Here are some possible reasons for the timeout and troubleshooting steps you can take:

• Firewall restrictions: Ensure that your Azure Synapse Workspace firewall allows inbound connections from the machine where you're running dbt. You can check and configure firewall rules in the Azure portal.
• ODBC driver version: Try downgrading your ODBC driver version to 17 or 13. There have been some reported compatibility issues with version 18. You can download the older version of the driver from the Microsoft website https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16.
• Connection timeout: The connection timeout in your dbt configuration might be set too low. Try increasing the timeout value in your dbt YAML file. Here's an example of how to set the connection timeout to 120 seconds:

dbt_setup:
  target: dev
  outputs:
    dev:
      type: synapse
      driver: 'ODBC Driver 18 for SQL Server'  # The ODBC Driver installed on your system
      server: guikerapp.database.windows.net  # Dedicated SQL endpoint of your workspace here
      port: 1433
      database: fivetrandata
      schema: default
      user: <your_username>
      password: <your_password>
      connection_timeout: 120  # Timeout in seconds

• Network connectivity issues: There might be temporary network connectivity issues between your machine and Azure Synapse Workspace. Try running your dbt models again after some time.

Hope this helps. Do let us know if you any further queries.