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
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
- CLI
- Interactive
- Integrated
- Service Principal
Definitely not ideal, but available
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
First, install the Azure CLI, then, log in:
az login
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
Windows Only brings up the Azure AD prompt so you can MFA if need be.
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
Windows Only uses your machine's credentials (might be disabled by your AAD admins)
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
client_*
and app_*
can be used interchangeably
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