# Microsoft SQL Server Reference

> All SQL Server authentication options and data type mappings.

#  Microsoft SQL Server Reference

Authentication options and data type handling for [SQL Server connections](../connect/sqlserver.md).

## 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.

## 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.

{/* AUTOGENERATED TYPE MAPPING: do not edit by hand; regenerate with update_reference_types.py */}

### 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)* |

{/* END AUTOGENERATED TYPE MAPPING */}