Skip to main content

Microsoft SQL Server setup

Community plugin

Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below.

  • Maintained by: Community
  • Authors: dbt-msft community (https://github.com/dbt-msft)
  • GitHub repo: dbt-msft/dbt-sqlserver
  • PyPI package: dbt-sqlserver
  • Slack channel: #db-sqlserver
  • Supported dbt Core version: v0.14.0 and newer
  • dbt Cloud support: Not Supported
  • Minimum data platform version: SQL Server 2016

Installing dbt-sqlserver

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-sqlserver

For SQL Server-specific configuration, please refer to SQL Server configs.

Default settings change in dbt-sqlserver v1.2 / ODBC Driver 18

Microsoft made several changes related to connection encryption. Read more about the changes below.

Prerequisites​

On Debian/Ubuntu make sure you have the ODBC header files before installing

sudo apt install unixodbc-dev

Download and install the Microsoft ODBC Driver 18 for SQL Server. If you already have ODBC Driver 17 installed, then that one will work as well.

Supported configurations​

  • The adapter is tested with SQL Server 2017, SQL Server 2019, SQL Server 2022 and Azure SQL Database.
  • We test all combinations with Microsoft ODBC Driver 17 and Microsoft ODBC Driver 18.
  • The collations we run our tests on are SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS.

The adapter support is not limited to the matrix of the above configurations. If you notice an issue with any other configuration, let us know by opening an issue on GitHub.

Authentication methods & profile configuration​

Common configuration​

For all the authentication methods, refer to the following configuration options that can be set in your profiles.yml file. A complete reference of all options can be found at the end of this page.

Configuration optionDescriptionTypeExample
driverThe ODBC driver to useRequiredODBC Driver 18 for SQL Server
serverThe server hostnameRequiredlocalhost
portThe server portRequired1433
databaseThe database nameRequiredNot applicable
schemaThe schema nameRequireddbo
retriesThe number of automatic times to retry a query before failing. Defaults to 1. Queries with syntax errors will not be retried. This setting can be used to overcome intermittent network issues.OptionalNot applicable
login_timeoutThe number of seconds used to establish a connection before failing. Defaults to 0, which means that the timeout is disabled or uses the default system settings.OptionalNot applicable
query_timeoutThe number of seconds used to wait for a query before failing. Defaults to 0, which means that the timeout is disabled or uses the default system settings.OptionalNot applicable
schema_authorizationOptionally set this to the principal who should own the schemas created by dbt. Read more about schema authorization.OptionalNot applicable
encryptWhether to encrypt the connection to the server. Defaults to true. Read more about connection encryption.OptionalNot applicable
trust_certWhether to trust the server certificate. Defaults to false. Read more about connection encryption.OptionalNot applicable

Connection encryption​

Microsoft made several changes in the release of ODBC Driver 18 that affects how connection encryption is configured. To accommodate these changes, starting in dbt-sqlserver 1.2.0 or newer the default values of encrypt and trust_cert have changed. Both of these settings will now always be included in the connection string to the server, regardless if you've left them out of your profile configuration or not.

  • The default value of encrypt is true, meaning that connections are encrypted by default.
  • The default value of trust_cert is false, meaning that the server certificate will be validated. By setting this to true, a self-signed certificate will be accepted.

More details about how these values affect your connection and how they are used differently in versions of the ODBC driver can be found in the Microsoft documentation.

Standard SQL Server authentication​

SQL Server credentials are supported for on-premise servers as well as Azure, and it is the default authentication method for dbt-sqlserver.

When running on Windows, you can also use your Windows credentials to authenticate.

profiles.yml
your_profile_name:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 18 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      database: database
      schema: schema_name
      user: username
      password: password

Microsoft Entra ID authentication​

While you can use the SQL username and password authentication as mentioned above, you might opt to use one of the authentication methods below for Azure SQL.

The following additional methods are available to authenticate to Azure SQL products:

  • Microsoft Entra ID (formerly Azure AD) username and password
  • Service principal
  • Managed Identity
  • Environment-based authentication
  • Azure CLI authentication
  • VS Code authentication (available through the automatic option below)
  • Azure PowerShell module authentication (available through the automatic option below)
  • Automatic authentication

The automatic authentication setting is in most cases the easiest choice and works for all of the above.

First, install the Azure CLI, then, log in:

az login

profiles.yml
your_profile_name:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 18 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      database: exampledb
      schema: schema_name
      authentication: CLI

Additional options for Microsoft Entra ID on Windows​

On Windows systems, the following additional authentication methods are also available for Azure SQL:

  • Microsoft Entra ID interactive
  • Microsoft Entra ID integrated
  • Visual Studio authentication (available through the automatic option above)

This setting can optionally show Multi-Factor Authentication prompts.

profiles.yml
your_profile_name:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 18 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      database: exampledb
      schema: schema_name
      authentication: ActiveDirectoryInteractive
      user: bill.gates@microsoft.com

Automatic Microsoft Entra ID principal provisioning for grants​

In dbt 1.2 or newer you can use the grants config block to automatically grant/revoke permissions on your models to users or groups. This is fully supported in this adapter and comes with an additional feature.

By setting auto_provision_aad_principals to true in your model configuration, you can automatically provision Microsoft Entra ID principals (users or groups) that don't exist yet.

In Azure SQL, you can sign in using Microsoft Entra ID authentication, but to be able to grant a Microsoft Entra ID principal certain permissions, it needs to be linked in the database first. (Microsoft documentation)

Note that principals will not be deleted automatically when they are removed from the grants block.

Schema authorization​

You can optionally set the principal who should own all schemas created by dbt. This is then used in the CREATE SCHEMA statement like so:

CREATE SCHEMA [schema_name] AUTHORIZATION [schema_authorization]

A common use case is to use this when you are authenticating with a principal who has permissions based on a group, such as a Microsoft Entra ID group. When that principal creates a schema, the server will first try to create an individual login for this principal and then link the schema to that principal. If you would be using Microsoft Entra ID in this case, then this would fail since Azure SQL can't create logins for individuals part of an AD group automatically.

Reference of all connection options​

Configuration optionDescriptionRequiredDefault value
driverThe ODBC driver to use.✅
hostThe hostname of the database server.✅
portThe port of the database server.1433
databaseThe name of the database to connect to.✅
schemaThe schema to use.✅
authenticationThe authentication method to use. This is not required for Windows authentication.'sql'
UIDUsername used to authenticate. This can be left out depending on the authentication method.
PWDPassword used to authenticate. This can be left out depending on the authentication method.
windows_loginSet this to true to use Windows authentication. This is only available for SQL Server.
tenant_idThe tenant ID of the Microsoft Entra ID instance. This is only used when connecting to Azure SQL with a service principal.
client_idThe client ID of the Microsoft Entra service principal. This is only used when connecting to Azure SQL with a Microsoft Entra service principal.
client_secretThe client secret of the Microsoft Entra service principal. This is only used when connecting to Azure SQL with a Microsoft Entra service principal.
encryptSet this to false to disable the use of encryption. See above.true
trust_certSet this to true to trust the server certificate. See above.false
retriesThe number of times to retry a failed connection.1
schema_authorizationOptionally set this to the principal who should own the schemas created by dbt. Details above.
login_timeoutThe amount of seconds to wait until a response from the server is received when establishing a connection. 0 means that the timeout is disabled.0
query_timeoutThe amount of seconds to wait until a response from the server is received when executing a query. 0 means that the timeout is disabled.0

Valid values for authentication:

  • sql: SQL authentication using username and password
  • ActiveDirectoryPassword: Active Directory authentication using username and password
  • ActiveDirectoryInteractive: Active Directory authentication using a username and MFA prompts
  • ActiveDirectoryIntegrated: Active Directory authentication using the current user's credentials
  • ServicePrincipal: Microsoft Entra ID authentication using a service principal
  • CLI: Microsoft Entra ID authentication using the account you're logged in with in the Azure CLI
  • ActiveDirectoryMsi: Microsoft Entra ID authentication using a managed identity available on the system
  • environment: Microsoft Entra ID authentication using environment variables as documented here
  • auto: Microsoft Entra ID authentication trying the previous authentication methods until it finds one that works
0
Edit this page