> 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/references/resources/nilus/batch/batch-sources/ms-sql-server.md).

# MS SQL Server

MS SQL Server batch source. Reads tables or custom SQL via ODBC; supports SQL Authentication and Azure AD access-token authentication.

[Microsoft SQL Server](https://learn.microsoft.com/sql/sql-server/) is a relational database engine. Nilus reads from SQL Server as a **batch source** through the ODBC-backed SQL path. The connector supports both standard SQL Authentication (username + password) and Azure AD token-based authentication.

For row-level change capture from SQL Server, use [MS SQL Server](https://v2.dataos.info/references/resources/nilus/cdc/cdc-sources/ms-sql-server.md) instead.

## Requirements

Connectivity and credentials must both be in place before the pipeline can run.

### Connectivity

* The Nilus runtime must reach the SQL Server endpoint on the configured port (default `1433`).
* The runtime image must include a working ODBC driver. Nilus expects `ODBC Driver 18 for SQL Server` by default; older drivers can be selected via the `driver` URI parameter.
* For Azure SQL Database / Managed Instance, allowlist the runtime egress IP on the server-level firewall and confirm the database is online (paused serverless tiers reject connections).

### Required parameters

<table data-search="false"><thead><tr><th>Parameter</th><th>Required</th><th>Default</th><th>Description</th></tr></thead><tbody><tr><td><code>host</code></td><td>Yes</td><td>-</td><td>SQL Server host name.</td></tr><tr><td><code>port</code></td><td>No</td><td><code>1433</code></td><td>SQL Server port.</td></tr><tr><td><code>database</code></td><td>Yes</td><td>-</td><td>Source database name.</td></tr><tr><td><code>username</code></td><td>Conditional</td><td>-</td><td>SQL login for password authentication. Omit when using Azure AD token authentication.</td></tr><tr><td><code>password</code></td><td>Conditional</td><td>-</td><td>Password for password authentication. When using Azure AD, this carries the access token (the driver receives it via <code>MSSQL_COPT_SS_ACCESS_TOKEN</code>).</td></tr><tr><td><code>driver</code></td><td>No</td><td><code>ODBC Driver 18 for SQL Server</code></td><td>ODBC driver name. Must match a driver actually installed in the runtime image.</td></tr><tr><td><code>Encrypt</code></td><td>No</td><td>driver default</td><td><code>yes</code> to require an encrypted connection (default for Driver 18).</td></tr><tr><td><code>TrustServerCertificate</code></td><td>No</td><td>driver default</td><td><code>yes</code> to skip TLS certificate validation. Required when the server uses a self-signed certificate (common in dev environments).</td></tr><tr><td><code>Authentication</code></td><td>Conditional</td><td>-</td><td>Set to <code>ActiveDirectoryAccessToken</code> for Azure AD token-based authentication.</td></tr><tr><td><code>connect_timeout</code></td><td>No</td><td><code>30</code></td><td>Driver connection timeout in seconds.</td></tr><tr><td><code>ignore_no_transaction_on_rollback</code></td><td>No</td><td><code>false</code></td><td>When <code>true</code>, Nilus ignores a rollback that happens with no active transaction. Required for some <a href="https://v2.dataos.info/references/resources/nilus/batch/batch-sources/azure-synapse.md">Azure Synapse</a> extracts. On a <strong>direct URI</strong>, add it as a query parameter. On a <strong>Depot</strong> source, set depot <code>params.ignore_no_transaction_on_rollback</code> or project <code>MSSQL_IGNORE_NO_TRANSACTION_ON_ROLLBACK=true</code>. See <a href="https://v2.dataos.info/references/resources/nilus/concepts/secrets-and-projections.md">Secrets and projections</a>. |</td></tr></tbody></table>

### Database-side permissions

```sql
GRANT CONNECT ON DATABASE :: [<database_name>] TO [<username>];
GRANT SELECT ON SCHEMA :: [<schema_name>] TO [<username>];
-- or, for table-scoped access:
GRANT SELECT ON [<schema_name>].[<table_name>] TO [<username>];
```

### URI formats

**Password authentication:**

```
mssql://<username>:<password>@<host>:<port>/<database>?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes
```

Synapse (and some SQL Server) extracts that fail on rollback can append `&ignore_no_transaction_on_rollback=true`.

**Azure AD token authentication:**

```
mssql://:<access-token>@<host>:<port>/<database>?driver=ODBC+Driver+18+for+SQL+Server&Authentication=ActiveDirectoryAccessToken
```

**Depot-backed equivalent (recommended for production):**

```
dataos://<mssql-depot>?purpose=ro
```

## Source options

| Option                 | Required    | Description                                                                                                                                                                                                                                                                                                   |
| ---------------------- | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `source_table`         | Yes         | Fully qualified table name in `<schema>.<table>` form. You can also use a custom query prefixed with `query:`.                                                                                                                                                                                                |
| `incremental_key`      | No          | Timestamp or numeric cursor column.                                                                                                                                                                                                                                                                           |
| `primary_key`          | Conditional | Column, or comma-separated columns, that identifies a row. Used by `incremental_strategy: merge` to match rows. Optional for a table with its own primary key. Required for `query:` sources and tables without one. See [Keys, incrementality, and output columns](#keys-incrementality-and-output-columns). |
| `interval_start`       | No          | Optional ISO-8601 lower bound for the extraction window.                                                                                                                                                                                                                                                      |
| `interval_end`         | No          | Optional ISO-8601 upper bound for the extraction window.                                                                                                                                                                                                                                                      |
| `page_size`            | No          | Rows per extraction batch. The default is `50000`.                                                                                                                                                                                                                                                            |
| `sql_reflection_level` | No          | `full` by default. Controls source-schema reflection.                                                                                                                                                                                                                                                         |
| `sql_limit`            | No          | Caps total rows extracted per run. Use for sampling and validation. Not supported with `query:` sources. Put a `TOP` clause in the query instead.                                                                                                                                                             |
| `sql_exclude_columns`  | No          | Comma-separated column names to skip during extraction.                                                                                                                                                                                                                                                       |
| `type_hints`           | No          | Object map of `column_name: <type>` that overrides inferred types.                                                                                                                                                                                                                                            |
| `max_table_nesting`    | No          | Use `"0"` for fully flattened analytics-friendly output. This is the default for SQL sources.                                                                                                                                                                                                                 |

> **Note** There is **no** separate `schema` option. The schema is inferred from the `<schema>.<table>` form of `source_table`.

## Sample Nilus configs

Each example below is self-contained and uses the current Nilus pipeline shape.

### Batch: incremental ingestion to Lakehouse

```yaml
name: nilus-mssql-batch
version: v1alpha
type: nilus
description: SQL Server → DataOS Lakehouse incremental snapshot
spec:
  type: batch
  compute: runnable-default
  source:
    address: dataos://mssqldepot?purpose=ro
    options:
      source_table: dbo.orders
      incremental_key: LastModifiedDate
      primary_key: OrderId
  sink:
    address: dataos://analyticslakehouse?purpose=rw
    options:
      dest_table: sales.orders_raw
      incremental_strategy: merge
      aws_region: us-west-2
```

### Batch: Azure AD token authentication

```yaml
spec:
  type: batch
  source:
    address: mssql://:{AAD_ACCESS_TOKEN}@sql.example.com:1433/sales?driver=ODBC+Driver+18+for+SQL+Server&Authentication=ActiveDirectoryAccessToken
    options:
      source_table: dbo.orders
      incremental_key: LastModifiedDate
      primary_key: OrderId
  sink:
    address: dataos://analyticslakehouse?purpose=rw
    options:
      dest_table: sales.orders_raw
      incremental_strategy: merge
```

### Batch: merge from a custom query

A `query:` source has no primary key to pick up. Set `primary_key` for `merge`.

```yaml
spec:
  type: batch
  source:
    address: dataos://mssqldepot?purpose=ro
    options:
      source_table: "query:SELECT OrderId, CustomerId, status, LastModifiedDate FROM dbo.orders WHERE status <> 'draft'"
      incremental_key: LastModifiedDate
      primary_key: OrderId
  sink:
    address: dataos://analyticslakehouse?purpose=rw
    options:
      dest_table: sales.orders_raw
      incremental_strategy: merge
```

## Keys, incrementality, and output columns

### `primary_key`

Nilus does not infer a primary key. It uses `primary_key` in `source.options`. The outcome depends on how Nilus reads the data:

| Read type                                     | `primary_key` unset                                                   | Needed for `merge`?                                          |
| --------------------------------------------- | --------------------------------------------------------------------- | ------------------------------------------------------------ |
| Table read (`source_table: <schema>.<table>`) | The source table's primary key is picked up during schema reflection. | No, when the table has a primary key. Yes, when it does not. |
| Custom query (`source_table: "query:..."`)    | No key is picked up.                                                  | Yes.                                                         |

{% hint style="warning" %}
`incremental_strategy: merge` without a primary key does not fail. Nilus logs a warning and falls back to `append`. Each run then adds rows again, which creates duplicates. Set `primary_key` whenever the source cannot supply one.
{% endhint %}

### `incremental_key` and the merge key

* **`incremental_key`** is the cursor column. It decides which rows each run reads. Nilus stores the last value in pipeline state.
* **`primary_key`** decides which destination rows are updated when `incremental_strategy: merge` is used.

For `type: nilus`, `sink.options.incremental_strategy` accepts `replace`, `append`, or `merge`. If omitted, Nilus uses `replace`.

### Column names

By default, destination column names become snake case. For example, `LastModifiedDate` becomes `last_modified_date`. Set `SCHEMA_NAMING=direct` to retain source names.

`primary_key` and `incremental_key` are not converted. Write them exactly as source column names, such as `OrderId` and `LastModifiedDate`.

* `Primary key column order_id was not found`
* `Cursor column last_modified_date does not exist`

See [Correctness knobs](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-correctness-knobs.md).

### Columns Nilus adds

The read backend determines whether Nilus adds columns:

| Read type                                                        | Backend             | Added columns                     |
| ---------------------------------------------------------------- | ------------------- | --------------------------------- |
| Table read                                                       | `pyarrow` (default) | None.                             |
| Custom query (`query:`), or a read with `SQL_BACKEND=sqlalchemy` | `sqlalchemy`        | `_nilus_load_id` and `_nilus_id`. |

| Column           | Type | Nullable | Meaning                            |
| ---------------- | ---- | -------- | ---------------------------------- |
| `_nilus_load_id` | text | No       | ID of the load that wrote the row. |
| `_nilus_id`      | text | No       | Unique row ID.                     |

These names are fixed. Nilus cannot rename or disable them. Drop them downstream if they are not needed.

{% hint style="info" %}
Switching an existing table-read pipeline to `query:`, or to `SQL_BACKEND=sqlalchemy`, against the same destination table can fail with `cannot add required column: _nilus_load_id`. Load into a new table, or recreate the existing one. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md).
{% endhint %}

### Decimal types on Lakehouse

This is the shared SQL batch extract path. PostgreSQL, MySQL, Snowflake, BigQuery, and other SQL sources use the same contract.

On a DataOS Lakehouse destination, a table read preserves source precision and scale. For example, `DECIMAL(20,4)` becomes `decimal(20,4)`. A `query:` extract stores decimals as `decimal(38,9)`. Switching these styles on the same `dest_table` is unsupported. It fails with `Cannot serialize value, scale of value does not match type decimal(38, 9)`. Keep one extract style per table, or use a new `dest_table`. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md) and [Custom Query (SQL Sources)](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md).

## Behavior and capabilities

* **Compute model**: Nilus connects through ODBC + the `pyodbc` driver and submits parameterized SQL reads.
* **Object model**: standard SQL Server tables and views addressed as `<schema>.<table>`. The default schema in most installations is `dbo`.
* **Pipeline mode**: `batch` only on this page. For row-level change capture, see [MS SQL Server](https://v2.dataos.info/references/resources/nilus/cdc/cdc-sources/ms-sql-server.md).
* **Authentication modes**: SQL Authentication (default) and Azure AD token-based authentication. For AAD, the source uses ODBC's `MSSQL_COPT_SS_ACCESS_TOKEN` connection attribute and skips the `UID` / `PWD` / `AUTHENTICATION` keywords from the credentials block.
* **TLS posture**: Driver 18 enforces encrypted connections by default. Set `TrustServerCertificate=yes` only for development scenarios; in production, install the server certificate's issuing CA into the runtime's trust store and leave the parameter unset (or `no`).
* **Custom queries**: supply `source_table: "query:SELECT ... FROM ..."` to extract from a hand-authored SQL query instead of a single table. The query must expose `incremental_key` under its exact configured name.

Nilus writes three bookkeeping tables to the destination dataset: `_nilus_loads`, `_nilus_version`, and `_nilus_pipeline_state`. Do not edit or drop them. The pipeline uses them to track loads and incremental state.

## Troubleshooting

| Symptom                                                                                    | Likely cause                                                                                                                                                                                | Resolution                                                                                                                                                                                                                                                                                                                 |
| ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `IM002 Data source name not found and no default driver specified`                         | The ODBC driver named in the URI is not installed in the runtime.                                                                                                                           | Install `ODBC Driver 18 for SQL Server` in the runtime image, or set `driver=` to a driver that is installed.                                                                                                                                                                                                              |
| `SSL Provider: error code 0xfff…` / certificate-trust failure                              | Driver 18 enforces TLS but cannot validate the server certificate.                                                                                                                          | Add `TrustServerCertificate=yes` to the URI for dev, or install the issuing CA into the runtime trust store for production.                                                                                                                                                                                                |
| `Login failed for user`                                                                    | Wrong credentials, expired AAD token, or the user lacks `CONNECT` on the database.                                                                                                          | Re-check credentials; for AAD, refresh the token; grant `CONNECT` if missing.                                                                                                                                                                                                                                              |
| `Cannot find the object … because it does not exist or you do not have permission`         | `source_table` is missing the schema prefix and the user's default schema is not `dbo`.                                                                                                     | Use the explicit `<schema>.<table>` form (typically `dbo.<table>`).                                                                                                                                                                                                                                                        |
| AAD token authentication unexpectedly silently fails                                       | `Authentication=ActiveDirectoryAccessToken` was not set, so the driver tried `UID`/`PWD` against an empty username.                                                                         | Add `Authentication=ActiveDirectoryAccessToken` to the URI query and pass the token as the URI password.                                                                                                                                                                                                                   |
| Run extracts the full table every time despite `incremental_key`                           | The column is not monotonically increasing or the pipeline state was reset between runs.                                                                                                    | Pick a column that is genuinely monotonic; `rowversion` is a strong choice on SQL Server.                                                                                                                                                                                                                                  |
| Connection drops on long extracts                                                          | `connect_timeout` and the driver's command timeout differ; long queries may exceed the latter.                                                                                              | Lower `page_size` so each batch query is shorter, or have your DBA raise the relevant server-side timeouts.                                                                                                                                                                                                                |
| Extract fails with a no-transaction / rollback error                                       | The server did not start a transaction the driver expected. Common on Azure Synapse dedicated SQL pools.                                                                                    | Direct URI: add `ignore_no_transaction_on_rollback=true`. Depot: project `MSSQL_IGNORE_NO_TRANSACTION_ON_ROLLBACK=true`, or set the matching depot `params` key.                                                                                                                                                           |
| `ValueError: Cannot serialize value, scale of value does not match type decimal(38, 9): 4` | A `query:` extract created the Lakehouse table with `decimal(38,9)`, then a table read attempted to write source-scale decimals such as `DECIMAL(20,4)`. The reverse transition also fails. | Keep one extract style per table. Use a new `dest_table`, or drop and reload. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md) and [Custom Query (SQL Sources)](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md). |

## Related docs

* [Batch sample configs](https://v2.dataos.info/references/resources/nilus/batch/sample-configs.md)
* [MS SQL Server](https://v2.dataos.info/references/resources/nilus/cdc/cdc-sources/ms-sql-server.md)
* [MS SQL Server](https://v2.dataos.info/references/resources/nilus/destinations/databases/ms-sql-server.md)
* [Azure Synapse](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/azure-synapse.md)
* [Custom Query (SQL Sources)](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md)
* [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md)
* [Secrets and projections](https://v2.dataos.info/references/resources/nilus/concepts/secrets-and-projections.md)
* [Optimize sink datasets](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets.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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/resources/nilus/batch/batch-sources/ms-sql-server.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.
