> 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/concepts/resources/nilus/destinations/databases/ms-sql-server.md). # MS SQL Server [Microsoft SQL Server](https://learn.microsoft.com/sql/sql-server/) is a relational database engine. Nilus writes into SQL Server tables for both batch and CDC pipelines through the ODBC-backed SQL path. The connector supports both standard SQL Authentication (username + password) and Azure AD token-based authentication. ## 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. * 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 | Parameter | Required | Default | Description | | ------------------------ | ----------- | ------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------- | | `host` | Yes | - | SQL Server host name. | | `port` | No | `1433` | SQL Server port. | | `database` | Yes | - | Target database. | | `username` | Conditional | - | SQL login for password authentication. Omit when using Azure AD token authentication. | | `password` | Conditional | - | Password for password authentication. When using Azure AD, this carries the access token (the driver receives it via `MSSQL_COPT_SS_ACCESS_TOKEN`). | | `driver` | No | `ODBC Driver 18 for SQL Server` | ODBC driver name. Must match a driver actually installed in the runtime image. | | `TrustServerCertificate` | No | driver default | `yes` to skip TLS certificate validation. Required when the server uses a self-signed certificate (common in dev environments). | | `Authentication` | Conditional | - | Set to `ActiveDirectoryAccessToken` for Azure AD token-based authentication. | ### Permissions The configured login or AAD principal needs: * `CONNECT` on the target database. * `CREATE TABLE` on the target schema if Nilus is expected to auto-create tables. * `INSERT`, `UPDATE`, `DELETE`, `SELECT` on the target tables according to the configured `incremental_strategy`. ### URI format ``` mssql://:@:/?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes ``` For Azure AD token authentication: ``` mssql://:@:/?driver=ODBC+Driver+18+for+SQL+Server&Authentication=ActiveDirectoryAccessToken ``` ## Sink options | Option | Required | Description | | ---------------------- | -------- | ------------------------------------------------------------------ | | `dest_table` | Yes | Target table in `.` form (typically `dbo.
`). | | `incremental_strategy` | Yes | One of `replace`, `append`, or `merge`. | ## Sample Nilus configs Each example below is self-contained and uses the current Nilus pipeline shape. ### Batch ingestion ```yaml name: nilus-mssql-batch version: v1alpha type: nilus spec: type: batch compute: universe-compute source: address: dataos://postgres-source options: source_table: public.orders sink: address: dataos://mssql-destination options: dest_table: analytics.orders incremental_strategy: replace ``` ### CDC ingestion ```yaml spec: type: cdc compute: universe-compute source: address: dataos://postgres-cdc cdc: table.include.list: "public.orders" topic.prefix: "orders_cdc" sink: address: dataos://mssql-destination options: dest_table: analytics.orders_cdc incremental_strategy: merge ``` ## Behavior and capabilities * **Compute model**: Nilus connects through ODBC and submits parameterized SQL writes; the SQL Server engine executes the load. * **Object model**: standard SQL Server tables addressed as `.
`. The default schema in most installations is `dbo`. * **Supported pipeline modes**: `batch` and `cdc`. * **Authentication modes**: SQL Authentication (default) and Azure AD token-based authentication. For Azure AD, the destination 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 `TrustServerCertificate` unset (or `no`). ## 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` | `dest_table` is missing the schema prefix and the user's default schema is not `dbo`. | Use the explicit `.
` form (typically `dbo.
`). | | 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. | ## Related docs * [MS SQL Server (Batch)](/concepts/resources/nilus/batch/batch-sources/ms-sql-server.md): companion batch source connector. * [MS SQL Server (CDC)](/concepts/resources/nilus/cdc/cdc-sources/ms-sql-server.md): companion CDC source connector. * [Optimize Sink Datasets](/concepts/resources/nilus/pipeline-optimization/optimize-sink-datasets.md): guidance on `incremental_strategy` and dataset-shape tuning. --- # 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/concepts/resources/nilus/destinations/databases/ms-sql-server.md?ask=&goal= ``` `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.