Microsoft SQL Server Reference

Authentication options and data type handling for SQL Server connections.

Server

servers:
  - server: production
    type: sqlserver
    host: localhost
    port: 1433
    database: mydb
    schema: dbo
    driver: ODBC Driver 18 for SQL Server

Authentication

Variable	Example	Description
DATACONTRACT_SQLSERVER_AUTHENTICATION	sql	sql (default), cli (uses az login), windows, ActiveDirectoryPassword, ActiveDirectoryServicePrincipal, ActiveDirectoryInteractive
DATACONTRACT_SQLSERVER_USERNAME	root	Username (for sql, ActiveDirectoryPassword, ActiveDirectoryInteractive)
DATACONTRACT_SQLSERVER_PASSWORD	toor	Password (for sql and ActiveDirectoryPassword)
DATACONTRACT_SQLSERVER_CLIENT_ID	a3cf5d29-...	Client ID (for ActiveDirectoryServicePrincipal)
DATACONTRACT_SQLSERVER_CLIENT_SECRET	kX9~Qr2Lm...	Client secret (for ActiveDirectoryServicePrincipal)
DATACONTRACT_SQLSERVER_TRUST_SERVER_CERTIFICATE	True	Trust self-signed certificate
DATACONTRACT_SQLSERVER_ENCRYPTED_CONNECTION	True	Use SSL
DATACONTRACT_SQLSERVER_DRIVER	ODBC Driver 18 for SQL Server	ODBC driver name

The cli mode reuses an az login session through the Azure default credential chain and requires ODBC Driver 18.1 or newer. host, port, database, schema, and driver come from the contract's servers block; host, port, and database can be overridden with DATACONTRACT_SQLSERVER_HOST, DATACONTRACT_SQLSERVER_PORT, and DATACONTRACT_SQLSERVER_DATABASE.

Deprecated variables

Deprecated	Use instead
DATACONTRACT_SQLSERVER_TRUSTED_CONNECTION	DATACONTRACT_SQLSERVER_AUTHENTICATION=windows

DATACONTRACT_SQLSERVER_TRUSTED_CONNECTION=true predates DATACONTRACT_SQLSERVER_AUTHENTICATION and selects the same Windows integrated auth. It applies only when DATACONTRACT_SQLSERVER_AUTHENTICATION is unset; setting a mode explicitly always wins, and the ignored flag is logged as a warning.

Data types

Importing

datacontract import sql --dialect sqlserver maps DDL types as follows; the normalized SQL type is kept as physicalType.

SQL Server type	logicalType
VARCHAR, NVARCHAR, CHAR, NCHAR, TEXT, NTEXT	string (maxLength for varchar/char)
INT, INTEGER, BIGINT, SMALLINT, TINYINT	integer
NUMERIC, DECIMAL, FLOAT, REAL, MONEY	number
BIT	boolean
DATE	date
TIME	time
DATETIME, DATETIME2, DATETIMEOFFSET, SMALLDATETIME	timestamp
UNIQUEIDENTIFIER	string (format uuid)
BINARY, VARBINARY, IMAGE	string (format binary)
XML	string

Testing

SQL Server supports native type introspection: the declared physicalType is checked against information_schema.columns. varchar and nvarchar are distinct types, as are text and varchar — declare what the column actually is. Timezone/precision variants of the datetime family are interchangeable; length is only enforced when declared (varchar(max) is introspected as such). A physicalType that isn't valid T-SQL falls back to the logical type category comparison.

Logical type mapping

When no physicalType is declared, the CLI derives the native type from the logicalType — for example in datacontract export sql and the dbt exports. This table is generated from the converter in the CLI's code:

logicalType	SQL Server type
string	varchar
integer	INT
number	numeric
boolean	bit
date	date
timestamp	datetimeoffset
time	time
object	nvarchar(max)
array	(not supported)