# <img className="page-icon" src="/img/icons/sqlserver.svg" alt="" /> Microsoft SQL Server Reference

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

## Server

```yaml
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.

{/* 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)* |
| `map` | `nvarchar(max)` |
| `vector` | — |

{/* END AUTOGENERATED TYPE MAPPING */}