# Connect Microsoft SQL Server to dbt Core Local development Community plugin Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below. * **Maintained by**: Community * **Authors**: Mikael Ene & dbt-msft community (https\://github.com/dbt-msft) * **GitHub repo**: [dbt-msft/dbt-sqlserver](https://github.com/dbt-msft/dbt-sqlserver) [![](https://img.shields.io/github/stars/dbt-msft/dbt-sqlserver?style=for-the-badge)](https://github.com/dbt-msft/dbt-sqlserver) * **PyPI package**: `dbt-sqlserver` [![](https://badge.fury.io/py/dbt-sqlserver.svg)](https://badge.fury.io/py/dbt-sqlserver) * **Slack channel**: [#db-sqlserver](https://getdbt.slack.com/archives/CMRMDDQ9W) * **Supported dbt Core version**: v0.14.0 and newer * **dbt support**: Not Supported * **Minimum data platform version**: SQL Server 2016 ## Installing dbt-sqlserver Use `pip` to install the adapter. Use the following command for installation: `python -m pip install dbt-sqlserver` ## Configuring dbt-sqlserver For SQL Server-specific configuration, please refer to [SQL Server configs.](../../../reference/resource-configs/mssql-configs.md) 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](#connection-encryption). ### Prerequisites On Debian/Ubuntu make sure you have the ODBC header files before installing ```bash sudo apt install unixodbc-dev ``` Download and install the [Microsoft ODBC Driver 18 for SQL Server](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15). If you already have ODBC Driver 17 installed, then that one will work as well. #### Supported configurations * The adapter is tested with SQL Server 2017, SQL Server 2019, SQL Server 2022 and Azure SQL Database. * We test all combinations with Microsoft ODBC Driver 17 and Microsoft ODBC Driver 18. * The collations we run our tests on are `SQL_Latin1_General_CP1_CI_AS` and `SQL_Latin1_General_CP1_CS_AS`. The adapter support is not limited to the matrix of the above configurations. If you notice an issue with any other configuration, let us know by opening an issue on [GitHub](https://github.com/dbt-msft/dbt-sqlserver). ## Authentication methods & profile configuration ### Common configuration For all the authentication methods, refer to the following configuration options that can be set in your `profiles.yml` file. A complete reference of all options can be found [at the end of this page](#reference-of-all-connection-options). | Configuration option | Description | Type | Example | | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- | ------------------------------- | | `driver` | The ODBC driver to use | Required | `ODBC Driver 18 for SQL Server` | | `server` | The server hostname | Required | `localhost` | | `port` | The server port | Required | `1433` | | `database` | The database name | Required | Not applicable | | `schema` | The schema name | Required | `dbo` | | `retries` | The number of automatic times to retry a query before failing. Defaults to `1`. Queries with syntax errors will not be retried. This setting can be used to overcome intermittent network issues. | Optional | Not applicable | | `login_timeout` | The number of seconds used to establish a connection before failing. Defaults to `0`, which means that the timeout is disabled or uses the default system settings. | Optional | Not applicable | | `query_timeout` | The number of seconds used to wait for a query before failing. Defaults to `0`, which means that the timeout is disabled or uses the default system settings. | Optional | Not applicable | | `schema_authorization` | Optionally set this to the principal who should own the schemas created by dbt. [Read more about schema authorization](#schema-authorization). | Optional | Not applicable | | `encrypt` | Whether to encrypt the connection to the server. Defaults to `true`. Read more about [connection encryption](#connection-encryption). | Optional | Not applicable | | `trust_cert` | Whether to trust the server certificate. Defaults to `false`. Read more about [connection encryption](#connection-encryption). | Optional | Not applicable | ### 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 values 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](https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16#encrypt). ### 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 ```yaml 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 ```yaml 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 ``` ### Microsoft Entra ID authentication 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: * Microsoft Entra ID (formerly Azure AD) username and password * Service principal * 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. ### Microsoft Entra ID username & password profiles.yml ```yaml 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 ```yaml 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 ```yaml 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: ActiveDirectoryMsi ``` ### Environment-based This authentication option allows you to dynamically select an authentication method depending on the available environment variables. [The Microsoft docs on EnvironmentCredential](https://docs.microsoft.com/en-us/python/api/azure-identity/azure.identity.environmentcredential?view=azure-python) explain the available combinations of environment variables you can use. profiles.yml ```yaml 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](https://docs.microsoft.com/en-us/cli/azure/install-azure-cli), then, log in: `az login` profiles.yml ```yaml 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 ```yaml 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 Microsoft Entra ID on Windows On Windows systems, the following additional authentication methods are also available for Azure SQL: * Microsoft Entra ID interactive * Microsoft Entra ID integrated * Visual Studio authentication (available through the automatic option above) ### Microsoft Entra ID interactive This setting can optionally show Multi-Factor Authentication prompts. profiles.yml ```yaml 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 ``` ### Microsoft Entra ID integrated This uses the credentials you're logged in with on the current machine. profiles.yml ```yaml 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 Microsoft Entra ID principal provisioning for grants In dbt 1.2 or newer you can use the [grants](../../../reference/resource-configs/grants.md) 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 Microsoft Entra ID principals (users or groups) that don't exist yet. In Azure SQL, you can sign in using Microsoft Entra ID authentication, but to be able to grant a Microsoft Entra ID principal certain permissions, it needs to be linked in the database first. ([Microsoft documentation](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql)) Note that principals will not be deleted automatically when they are removed from the `grants` block. ### Schema authorization You can optionally set the principal who should own all schemas created by dbt. This is then used in the `CREATE SCHEMA` statement like so: ```sql CREATE SCHEMA [schema_name] AUTHORIZATION [schema_authorization] ``` A common use case is to use this when you are authenticating with a principal who has permissions based on a group, such as a Microsoft Entra ID group. When that principal creates a schema, the server will first try to create an individual login for this principal and then link the schema to that principal. If you would be using Microsoft Entra ID in this case, then this would fail since Azure SQL can't create logins for individuals part of an AD group automatically. ### 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 Microsoft Entra ID instance. This is only used when connecting to Azure SQL with a service principal. | | | | `client_id` | The client ID of the Microsoft Entra service principal. This is only used when connecting to Azure SQL with a Microsoft Entra service principal. | | | | `client_secret` | The client secret of the Microsoft Entra service principal. This is only used when connecting to Azure SQL with a Microsoft Entra service principal. | | | | `encrypt` | Set this to `false` to disable the use of encryption. See [above](#connection-encryption). | | `true` | | `trust_cert` | Set this to `true` to trust the server certificate. See [above](#connection-encryption). | | `false` | | `retries` | The number of times to retry a failed connection. | | `1` | | `schema_authorization` | Optionally set this to the principal who should own the schemas created by dbt. [Details above](#schema-authorization). | | | | `login_timeout` | The amount of seconds to wait until a response from the server is received when establishing a connection. `0` means that the timeout is disabled. | | `0` | | `query_timeout` | The amount of seconds to wait until a response from the server is received when executing a query. `0` means that the timeout is disabled. | | `0` | 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`: Microsoft Entra ID authentication using a service principal * `CLI`: Microsoft Entra ID authentication using the account you're logged in with in the Azure CLI * `ActiveDirectoryMsi`: Microsoft Entra ID authentication using a managed identity available on the system * `environment`: Microsoft Entra ID authentication using environment variables as documented [here](https://learn.microsoft.com/en-us/python/api/azure-identity/azure.identity.environmentcredential?view=azure-python) * `auto`: Microsoft Entra ID authentication trying the previous authentication methods until it finds one that works ## Was this page helpful? YesNo [Privacy policy](https://www.getdbt.com/cloud/privacy-policy)[Create a GitHub issue](https://github.com/dbt-labs/docs.getdbt.com/issues) This site is protected by reCAPTCHA and the Google [Privacy Policy](https://policies.google.com/privacy) and [Terms of Service](https://policies.google.com/terms) apply.