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: Mikael Ene
Source: Github
Core version: v0.14.0 and newer
dbt Cloud: Not Supported
dbt Slack channel Link to channel

The package can be installed from PyPI with:

pip install dbt-sqlserver

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

sudo apt install unixodbc-dev

Connecting to SQL Server with dbt-sqlserver

standard SQL Server authentication

SQL Server credentials are supported for on-prem as well as cloud, and it is the default authentication method for dbt-sqlsever

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: server-host-name or ip
      port: 1433
      schema: schemaname
      user: username
      password: password

Active Directory Authentication

The following pyodbc-supported ActiveDirectory methods are available to authenticate to Azure SQL products:

• ActiveDirectory Password
• Azure CLI
• ActiveDirectory Interactive (Windows only)
• ActiveDirectory Integrated (Windows only)
• Service Principal (a.k.a. AAD Application)
• ActiveDirectory MSI (not implemented)

Password

Definitely not ideal, but available

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: server-host-name or ip
      port: 1433
      schema: schemaname
      authentication: ActiveDirectoryPassword
      user: bill.gates@microsoft.com
      password: iheartopensource

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: server-host-name or ip
      port: 1433
      schema: schemaname
      authentication: CLI

This is also the preferred route for using a service principal:

az login --service-principal --username $CLIENTID --password $SECRET --tenant $TENANTID

Interactive

Windows Only brings up the Azure AD prompt so you can MFA if need be.

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: server-host-name or ip
      port: 1433
      schema: schemaname
      authentication: ActiveDirectoryInteractive
      user: bill.gates@microsoft.com

Integrated

Windows Only uses your machine's credentials (might be disabled by your AAD admins)

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: server-host-name or ip
      port: 1433
      schema: schemaname
      authentication: ActiveDirectoryIntegrated

Service Principal

client_* and app_* can be used interchangeably

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: server-host-name or ip
      port: 1433
      schema: schemaname
      authentication: ServicePrincipal
      tenant_id: tenant_id
      client_id: clientid
      client_secret: clientsecret