> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/build/v1/productize/connect-engine/sql-server.md).

# SQL Server

Microsoft SQL Server suits existing SQL Server estates, Windows-centric environments, or projects that need a governed RDBMS with full T-SQL surface area. Use the `mssql` adapter. This page covers the connection. For grants, materialization internals, indexing, and the full reference, see the [SQL Server engine manual](https://v2.dataos.info/references/engine-guide/sql-server) in References.

## Before you start

* A SQL Server instance (2019–2022) or an Azure SQL Database, with `connection.database` already created: Vulcan has no `CREATE SCHEMA IF NOT EXISTS`-style database bootstrap.
* A login for Vulcan, plus a separate read-only login for metadata scanning.
* DataOS access: a `roles:id:data-dev`-equivalent role tag, `depot:rw:<mssql-depot-name>` (or `depot:r` for read-only), **Can Use** on Compute/Depots/Secrets, and a git-sync Secret.

## Permissions

Three roles, each with a distinct scope: an admin role (sysadmin/DBA) that creates logins and grants privileges, the Vulcan service login that runs models, and a read-only consumer login.

| Grant                                                 | Required for                          |
| ----------------------------------------------------- | ------------------------------------- |
| `db_ddladmin`, `db_datareader`, `db_datawriter` roles | Running models and managing tables    |
| `CREATE SCHEMA`                                       | Creating schemas referenced by models |
| `db_datareader` (consumer login)                      | Read-only access for BI and endpoints |

## Connection options

Connect directly with credentials, or through a [DataOS Depot](#using-a-depot) (recommended for production).

| Option             | Required | Description                                                                               |
| ------------------ | :------: | ----------------------------------------------------------------------------------------- |
| `type`             |    Yes   | Must be `mssql`                                                                           |
| `host`             |    Yes   | SQL Server instance hostname                                                              |
| `database`         |    No    | Target database. Must already exist                                                       |
| `user`, `password` |    No    | Username/password authentication                                                          |
| `port`             |    No    | Defaults to `1433`                                                                        |
| `driver`           |    No    | `pymssql` (default) or `pyodbc`                                                           |
| `driver_name`      |    No    | ODBC driver name, only used with `driver: pyodbc`                                         |
| `odbc_properties`  |    No    | ODBC connection properties, for example `authentication: ActiveDirectoryServicePrincipal` |

### Authentication methods

* **Username and password** (default): set `user` and `password` with `driver: pymssql`.
* **Microsoft Entra ID / Azure AD**: install the `mssql-odbc` extra, set `driver: pyodbc`, `driver_name`, and `odbc_properties`.
* Always inject the password from an environment variable: `password: "{{ env_var('MSSQL_PASSWORD') }}"`.

## Example

```yaml
gateways:
  local:
    connection:
      type: mssql
      host: localhost
      user: sa
      password: "{{ env_var('MSSQL_PASSWORD') }}"
      port: 1433
      database: warehouse
```

### Using a Depot

```yaml
gateways:
  default:
    connection:
      type: depot
      address: dataos://<mssql-depot-name>?purpose=rw
```

## Supported model kinds

`FULL`, `SEED`, `VIEW`, `INCREMENTAL_BY_TIME_RANGE`, `INCREMENTAL_BY_UNIQUE_KEY`, `INCREMENTAL_BY_PARTITION`, `INCREMENTAL_UNMANAGED`, `SCD_TYPE_2`, `SCD_TYPE_2_BY_TIME`, `SCD_TYPE_2_BY_COLUMN`, and `EMBEDDED`. Table cloning aren't supported in SQL Server. For how each kind materializes, see the [SQL Server engine manual](https://v2.dataos.info/references/engine-guide/sql-server).

> Azure SQL Database reuses this same adapter (`azuresql` connection-config type): the only functional difference is that Azure SQL is single-catalog, while on-premises SQL Server supports cross-database `USE`. SQL Server also defaults a bare `VARCHAR` (no length) to `VARCHAR(1)`, silently truncating data. Always declare an explicit length.

Once `vulcan info` succeeds, continue to [Assets](/build/v1/productize/assets.md).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://v2.dataos.info/build/v1/productize/connect-engine/sql-server.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.