Microsoft SQL Server Profile

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
Author: dbt-msft community
Source: GitHub
Core version: v0.14.0 and newer
dbt Cloud: Not Supported
dbt Slack channel: Link to channel Supported Version: SQL Server 2016 and later

The package can be installed from PyPI with:

pip install dbt-sqlserver

Prerequisites

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

sudo apt install unixodbc-dev

Download and install the Microsoft ODBC Driver 17 for SQL Server

Authentication methods

Standard SQL Server authentication

SQL Server credentials are supported for on-prem 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 17 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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      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 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: hostname or IP of your server
      port: 1433
      schema: schema_name
      authentication: ActiveDirectoryIntegrated