dbt-sqlserver Adapter

Common errors

• pyodbc.Error: ('01000', '[01000] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

  cause The Microsoft ODBC Driver for SQL Server is either not installed on the system, or the 'driver' name specified in `profiles.yml` does not match an installed driver.
  fix Install the appropriate Microsoft ODBC Driver for SQL Server (e.g., ODBC Driver 17 for SQL Server) for your operating system. Verify the exact name of the installed driver and update the 'driver' field in your `profiles.yml` accordingly.

• pyodbc.Error: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '...' (18456)")

  cause The username, password, or chosen authentication method in `profiles.yml` is incorrect, or the SQL Server user does not have sufficient permissions to connect.
  fix Double-check the `user`, `password`, and `authentication` fields in your `profiles.yml`. Ensure the specified user has login privileges to the SQL Server instance and the target database. For Windows or AAD authentication, verify your system's configuration.

• pyodbc.Error: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '...' (208)")

  cause dbt is attempting to access a database, schema, or table that does not exist or the connecting user lacks the necessary permissions for. This often happens if the `database` or `schema` in `profiles.yml` are incorrect or the user doesn't have `SELECT` on existing objects or `CREATE` on schemas/tables.
  fix Verify that the `database` and `schema` specified in your `profiles.yml` correctly point to existing resources. Ensure the SQL Server user configured in `profiles.yml` has `CREATE SCHEMA`, `CREATE TABLE`, `SELECT`, `INSERT`, `UPDATE`, and `DELETE` permissions on the target database and schema.

Warnings

• breaking dbt-sqlserver versions are tightly coupled with dbt-core versions. Ensure that your installed dbt-sqlserver adapter's major version matches your dbt-core installation's major version (e.g., dbt-sqlserver==1.9.x with dbt-core==1.9.x). Mismatched versions can lead to unexpected behavior or errors.
  Fix: Always install `dbt-sqlserver` with a version specifier that aligns with your `dbt-core` version: `pip install dbt-core~=1.9.0 dbt-sqlserver~=1.9.0`
• gotcha Connecting to SQL Server requires a pre-installed Microsoft ODBC Driver for SQL Server on the system where dbt is running. The `dbt-sqlserver` package only installs the `pyodbc` Python library, which acts as a wrapper for the native ODBC driver.
  Fix: Download and install the appropriate Microsoft ODBC Driver for SQL Server (e.g., version 17 or 18) for your operating system. Ensure the `driver` field in your `profiles.yml` matches the installed driver name exactly (e.g., 'ODBC Driver 17 for SQL Server').
• gotcha Different authentication methods (SQL, Windows, Azure AD) for SQL Server have distinct configuration requirements in `profiles.yml`. Incorrectly configured authentication details are a common source of connection failures.
  Fix: Carefully review the `authentication` field in your `profiles.yml`. For 'sql' authentication, ensure `user` and `password` are correct. For 'windows' authentication, ensure the dbt process runs under a Windows-authenticated user. For 'aad' (Azure AD) authentication, provide `client_id`, `client_secret`, and `tenant_id` if using service principal.
• gotcha SQL Server's default transaction isolation levels and locking behaviors can lead to deadlocks or concurrency issues in busy environments, especially during dbt runs involving large transformations. Versions 1.8.2 and later re-introduced `NOLOCK` behavior in some queries to mitigate this.
  Fix: Monitor SQL Server performance and locks during dbt runs. Consider using `NOLOCK` where appropriate (if it aligns with your data consistency requirements) via dbt macros, or consult SQL Server documentation for tuning transaction isolation levels and concurrency control for your specific use case. The adapter's `trust_cert` setting can also impact connection stability.

Install

• pip install dbt-sqlserver Install latest version

Imports

• dbt-sqlserver

  # dbt-sqlserver is a dbt adapter plugin.
  # Users typically do not import symbols directly from this library in Python code.
  # Interaction is primarily via the dbt CLI and configuration files (profiles.yml, dbt_project.yml).

  dbt adapters are loaded by dbt-core at runtime based on configuration, not through explicit Python imports in user scripts.

Quickstart

The primary interaction with dbt-sqlserver is through the dbt CLI and configuration files. This Python snippet demonstrates how to construct the `profiles.yml` content required to connect dbt to a SQL Server database, using environment variables for sensitive or variable settings. After setting up the `profiles.yml` and a dbt project, `dbt debug` is used to verify the connection.

import os

# Configure your SQL Server connection details using environment variables or replace placeholders.
profiles_yml_content = f"""
sqlserver_quickstart:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: {os.environ.get('SQLSERVER_ODBC_DRIVER', 'ODBC Driver 17 for SQL Server')} # e.g., 'ODBC Driver 17 for SQL Server' or 'ODBC Driver 18 for SQL Server'
      server: {os.environ.get('SQLSERVER_SERVER', 'localhost')} # e.g., 'your_server_name.database.windows.net' or 'localhost\\SQLEXPRESS'
      port: {os.environ.get('SQLSERVER_PORT', '1433')}
      database: {os.environ.get('SQLSERVER_DATABASE', 'dbt_test_db')}
      schema: {os.environ.get('SQLSERVER_SCHEMA', 'dbt_sqlserver_qs')}
      authentication: {os.environ.get('SQLSERVER_AUTH_METHOD', 'sql')} # 'sql', 'windows', or 'aad'
      # For 'sql' authentication:
      user: {os.environ.get('SQLSERVER_USER', 'your_user_name_here')}
      password: {os.environ.get('SQLSERVER_PASSWORD', 'your_password_here')}
      # For 'aad' or 'windows' authentication, consult dbt-sqlserver documentation.
      # trust_cert: true # Optional: Use with caution if not validating certs
"""

print("To quickly get started with dbt-sqlserver:")
print("1. Ensure you have the appropriate Microsoft ODBC Driver for SQL Server installed on your system.")
print("2. Create or update a file named `profiles.yml` in your `~/.dbt/` directory (or specify with --profiles-dir). ")
print("3. Populate it with content similar to the following (replace placeholders or set environment variables):")
print("\n" + profiles_yml_content)
print("\n4. Initialize a dbt project: `dbt init my_sqlserver_project`")
print("5. In `my_sqlserver_project/dbt_project.yml`, set `profile: sqlserver_quickstart`")
print("6. Navigate into your project directory: `cd my_sqlserver_project`")
print("7. Test your connection: `dbt debug`")