{"id":8932,"library":"dbt-sqlserver","title":"dbt-sqlserver Adapter","description":"dbt-sqlserver is a dbt adapter plugin that enables dbt to connect to and transform data in Microsoft SQL Server databases. It allows users to leverage dbt's powerful data transformation capabilities, including models, tests, and documentation, against SQL Server instances. The library maintains close compatibility with dbt-core's release cycle, typically releasing updates in sync with dbt-core's major versions. The current version is 1.9.0.","status":"active","version":"1.9.0","language":"en","source_language":"en","source_url":"https://github.com/dbt-msft/dbt-sqlserver","tags":["dbt","sqlserver","database","data transformation","etl","elt"],"install":[{"cmd":"pip install dbt-sqlserver","lang":"bash","label":"Install latest version"}],"dependencies":[{"reason":"dbt-sqlserver is an adapter plugin for dbt-core, providing the core dbt functionality.","package":"dbt-core","optional":false},{"reason":"Used internally by dbt-sqlserver to connect to SQL Server databases.","package":"pyodbc","optional":false}],"imports":[{"note":"dbt adapters are loaded by dbt-core at runtime based on configuration, not through explicit Python imports in user scripts.","symbol":"dbt-sqlserver","correct":"# dbt-sqlserver is a dbt adapter plugin.\n# Users typically do not import symbols directly from this library in Python code.\n# Interaction is primarily via the dbt CLI and configuration files (profiles.yml, dbt_project.yml)."}],"quickstart":{"code":"import os\n\n# Configure your SQL Server connection details using environment variables or replace placeholders.\nprofiles_yml_content = f\"\"\"\nsqlserver_quickstart:\n  target: dev\n  outputs:\n    dev:\n      type: sqlserver\n      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'\n      server: {os.environ.get('SQLSERVER_SERVER', 'localhost')} # e.g., 'your_server_name.database.windows.net' or 'localhost\\\\SQLEXPRESS'\n      port: {os.environ.get('SQLSERVER_PORT', '1433')}\n      database: {os.environ.get('SQLSERVER_DATABASE', 'dbt_test_db')}\n      schema: {os.environ.get('SQLSERVER_SCHEMA', 'dbt_sqlserver_qs')}\n      authentication: {os.environ.get('SQLSERVER_AUTH_METHOD', 'sql')} # 'sql', 'windows', or 'aad'\n      # For 'sql' authentication:\n      user: {os.environ.get('SQLSERVER_USER', 'your_user_name_here')}\n      password: {os.environ.get('SQLSERVER_PASSWORD', 'your_password_here')}\n      # For 'aad' or 'windows' authentication, consult dbt-sqlserver documentation.\n      # trust_cert: true # Optional: Use with caution if not validating certs\n\"\"\"\n\nprint(\"To quickly get started with dbt-sqlserver:\")\nprint(\"1. Ensure you have the appropriate Microsoft ODBC Driver for SQL Server installed on your system.\")\nprint(\"2. Create or update a file named `profiles.yml` in your `~/.dbt/` directory (or specify with --profiles-dir). \")\nprint(\"3. Populate it with content similar to the following (replace placeholders or set environment variables):\")\nprint(\"\\n\" + profiles_yml_content)\nprint(\"\\n4. Initialize a dbt project: `dbt init my_sqlserver_project`\")\nprint(\"5. In `my_sqlserver_project/dbt_project.yml`, set `profile: sqlserver_quickstart`\")\nprint(\"6. Navigate into your project directory: `cd my_sqlserver_project`\")\nprint(\"7. Test your connection: `dbt debug`\")","lang":"python","description":"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."},"warnings":[{"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`","message":"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.","severity":"breaking","affected_versions":"All versions"},{"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').","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"Prior to 1.8.2, and current versions with specific workload patterns."}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"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.","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.","error":"pyodbc.Error: ('01000', '[01000] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')"},{"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.","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.","error":"pyodbc.Error: ('28000', \"[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '...' (18456)\")"},{"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.","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.","error":"pyodbc.Error: ('42S02', \"[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '...' (208)\")"}]}