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.

Overview of dbt-sqlserver

• 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

pip is the easiest way to install the adapter:

pip install dbt-sqlserver

Installing dbt-sqlserver will also install dbt-core and any other dependencies.

Configuring dbt-sqlserver

For SQL Server-specifc configuration please refer to SQL Server Configuration

For further info, refer to the GitHub repository: dbt-msft/dbt-sqlserver

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.

The adapter is tested with SQL Server 2017, SQL Server 2019, SQL Server 2022 and Azure SQL Database. These versions are tested with Microsoft ODBC Driver 17 and Microsoft ODBC Driver 18.

Authentication methods & profile configuration

Common configuration

For all the authentication methods below, the following configuration options can be set in your profiles.yml file:

• driver: The ODBC driver to use. E.g. ODBC Driver 18 for SQL Server
• server: The server hostname. E.g. localhost
• port: The server port. E.g. 1433
• database: The database name.
• schema: The schema name. E.g. dbo
• retries: The number of automatic times to retry a query before failing. Defaults to 1. Note that queries with syntax errors will not be retried. This setting can be used to overcome intermittent network issues.
• encrypt: Whether to encrypt the connection to the server. Defaults to true. Read more about encryption below.
• trust_cert: Whether to trust the server certificate. Defaults to false. Read more about encryption below.

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 vallues 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.

SQL Server credentials

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

Windows credentials

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
      windows_login: True

Azure Active Directory Authentication (AAD)

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:

• AAD username and password
• Service principal (a.k.a. AAD Application)
• 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.

AAD username & password

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: ActiveDirectoryPassword
      user: bill.gates@microsoft.com
      password: iheartopensource

Service principal

Client ID is often also referred to as Application ID.

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: ServicePrincipal
      tenant_id: 00000000-0000-0000-0000-000000001234
      client_id: 00000000-0000-0000-0000-000000001234
      client_secret: S3cret!

Managed Identity

Both system-assigned and user-assigned managed identities will work.

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: MSI

Environment-based

This authentication option allows you to dynamically select an authentication method depending on the available environment variables.

The Microsoft docs on EnvironmentCredential explain the available combinations of environment variables you can use.

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: environment

Azure CLI

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

Automatic

This authentication option will automatically try to use all available authentication methods.

The following methods are tried in order:

1. Environment-based authentication
2. Managed Identity authentication
3. Visual Studio authentication (Windows only, ignored on other operating systems)
4. Visual Studio Code authentication
5. Azure CLI authentication
6. Azure PowerShell module authentication

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: auto

Additional options for AAD on Windows

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

• AAD interactive
• AAD integrated
• Visual Studio authentication (available through the automatic option above)

AAD interactive

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

AAD integrated

This uses the credentials you're logged in with on the current machine.

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: ActiveDirectoryIntegrated

Automatic AAD 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 Azure Active Directory (AAD) principals (users or groups) that don't exist yet.

In Azure SQL, you can sign in using AAD authentication, but to be able to grant an AAD 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.

Reference of all connection options

configuration option	description	required	default value
driver	The ODBC driver to use.	✅
host	The hostname of the database server.	✅
port	The port of the database server.		1433
database	The name of the database to connect to.	✅
schema	The schema to use.	✅
authentication	The authentication method to use. This is not required for Windows authentication.		'sql'
UID	Username used to authenticate. This can be left out depending on the authentication method.
PWD	Password used to authenticate. This can be left out depending on the authentication method.
windows_login	Set this to true to use Windows authentication. This is only available for SQL Server.
tenant_id	The tenant ID of the Azure Active Directory instance. This is only used when connecting to Azure SQL with a service principal.
client_id	The client ID of the Azure Active Directory service principal. This is only used when connecting to Azure SQL with an AAD service principal.
client_secret	The client secret of the Azure Active Directory service principal. This is only used when connecting to Azure SQL with an AAD service principal.
encrypt	Set this to false to disable the use of encryption. See above.		true
trust_cert	Set this to true to trust the server certificate. See above.		false
retries	The number of times to retry a failed connection.		1

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: Azure Active Directory authentication using a service principal
• CLI: Azure Active Directory authentication using the account you're logged in with in the Azure CLI
• MSI: Azure Active Directory authentication using a managed identity available on the system
• environment: Azure Active Directory authentication using environment variables as documented here
• auto: Azure Active Directory authentication trying the previous authentication methods until it finds one that works