Skip to main content

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

dbt-sqlserver stars latest version on PyPI

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.

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

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.

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

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)

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