Connect: MSSQL

Command

skippr-dbt connect source mssql \
  --connection-string '${MSSQL_CONNECTION_STRING}'

Or run without flags to be prompted interactively.

Flags

Flag	Description
--connection-string	ADO.NET connection string. Use ${ENV_VAR} to reference an environment variable.

Using ${MSSQL_CONNECTION_STRING} keeps the actual credentials out of the config file and reads the value from your environment at runtime.

Config output

Running connect source mssql writes the following to skippr-dbt.yaml:

source:
  kind: mssql
  connection_string: ${MSSQL_CONNECTION_STRING}

Authentication

Variable	Description
MSSQL_CONNECTION_STRING	ADO.NET connection string for SQL Server

Connection string format

export MSSQL_CONNECTION_STRING="server=tcp:127.0.0.1,1433;database=testdb;user id=sa;password=YourPass;TrustServerCertificate=true"

Common parameters:

Parameter	Description
server	Hostname and port (e.g. tcp:myserver.database.windows.net,1433)
database	Database name
user id	SQL Server login
password	Login password
TrustServerCertificate	Set to true for self-signed certs (dev/test)
Encrypt	Set to true for Azure SQL or production (default varies by driver)

Azure SQL

For Azure SQL Database, use the fully qualified server name:

export MSSQL_CONNECTION_STRING="server=tcp:myserver.database.windows.net,1433;database=mydb;user id=myuser@myserver;password=MyPass;Encrypt=true;TrustServerCertificate=false"

Local dev with Docker

Spin up a local MSSQL instance for testing:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourStrong!Passw0rd' \
  -p 1433:1433 --name mssql-dev \
  -d mcr.microsoft.com/mssql/server:2022-latest

export MSSQL_CONNECTION_STRING="server=tcp:127.0.0.1,1433;database=master;user id=sa;password=YourStrong!Passw0rd;TrustServerCertificate=true"

Troubleshooting

Symptom	Fix
Login failed for user	Verify username and password in the connection string
Cannot open server	Check the server hostname, port, and network access
SSL Provider: certificate verify failed	Add TrustServerCertificate=true for self-signed certs, or install the server's CA cert