> For the complete documentation index, see [llms.txt](https://docs.soda.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.soda.io/soda-documentation/reference/data-source-reference-for-soda-core/sql-server.md).

# SQL Server

Access configuration details to connect Soda to a Microsoft SQL Server data source.

### Requirements

* Install the [Microsoft ODBC driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver17) before proceeding with the connection configuration.

### Connection configuration reference

Install the following package:

```bash
pip install soda-sqlserver
```

#### Data source YAML

**Create the config file:**

```shellscript
soda data-source create -f ds_config.yml
```

The data source configuration YAML should look like the following:

{% code title="ds\_config.yml" %}

```yaml
type: sqlserver
name: my_sqlserver
connection:
  host: ${env.SQLSERVER_HOST}
  port: 1433
  database: ${env.SQLSERVER_DB}
  user: ${env.SQLSERVER_USER}                            # SEE NOTE
  password: ${env.SQLSERVER_PW}                          # SEE NOTE
  authentication: sql  # activedirectoryserviceprincipal | activedirectoryinteractive | activedirectorypassword 
  # optional
  client_id: ${env.SQLSERVER_SERVICE_CLIENT_ID}          # SEE NOTE
  client_secret: ${env.SQLSERVER_SERVICE_CLIENT_SECRET}  # SEE NOTE
  driver: ODBC Driver 18 for SQL Server
  trusted_connection: false
  encrypt: false
  trust_server_certificate: false
  connection_parameters:
    application_intent: 'ReadOnly'
```

{% endcode %}

{% hint style="info" %}
**Note:** depending on the authentication method that is used, `user` and `password` may not be required (e.g. `activedirectoryserviceprincipal` requires `client_id` and `client_secret`).
{% endhint %}

#### Connection test

Test the data source connection:

```bash
soda data-source test -ds ds_config.yml
```

***

## Required permissions

Create a dedicated login and user for Soda:

```sql
-- Create login and user
CREATE LOGIN soda_login WITH PASSWORD = '...';
USE <database>;
CREATE USER soda_user FOR LOGIN soda_login;

-- Grant read access via the built-in role (simplest approach)
EXEC sp_addrolemember 'db_datareader', 'soda_user';

-- Grant schema metadata access (required for schema inspection)
GRANT VIEW DEFINITION ON SCHEMA :: <schema> TO soda_user;

-- Alternatively, grant at schema level instead of using the role
GRANT SELECT ON SCHEMA :: <schema> TO soda_user;
```

{% hint style="info" %}
**`db_datareader` role:** This built-in role grants `SELECT` on all tables and views in the database, which is the simplest way to grant Soda read access. Combine it with `VIEW DEFINITION` on schemas for full schema introspection.
{% endhint %}

{% hint style="warning" %}

#### **Row-level security**

If your data source uses row-level security policies (e.g. Row Access Policies in Snowflake, Row-Level Security in PostgreSQL or SQL Server, Row Filters in Databricks Unity Catalog, or equivalent mechanisms in other data sources), **ensure that the Soda connection** user or role **has been granted the appropriate policy exemptions or access**.

Row-level security policies do not cause connection errors or scan failures, but **they silently filter the rows visible to Soda**. This can produce unexpected results. For example, `dataset_rows_tested: 0` even when the dataset clearly contains rows.
{% endhint %}

***

## Regex patterns in SQL Server

SQL Server and related databases **do not offer universal regex support**. When using regex-based checks in these environments, Soda adapts pattern-matching queries.

Instead of regex functions, **Soda translates patterns into SQL queries using the `PATINDEX` function**.

#### How Soda translates regex patterns

If the user specifies a regex `pattern` which matches a `column expression`, it will be expanded in SQL as:

{% code overflow="wrap" %}

```sql
PATINDEX('%{pattern}%', {column expression} COLLATE SQL_Latin1_General_Cp1_CS_AS) > 0
```

{% endcode %}

**Key considerations**

* **Collation**

A collation (a rule set used for determining string matches) must be specified.

Soda uses the `SQL_Latin1_General_Cp1_CS_AS`, which is **case-sensitive** and **accent-sensitive**, so `a` and `A` are not equal, and accented characters (such as `é`) are treated distinctly.

* **Pattern handling**

Regex patterns are wrapped in `%…%`, so any substring match within the column value will return `true`.

Example: `^abc` becomes `%abc%`, which will match anywhere in the string.

* **Character ranges**\
  In SQL Server and related databases, character range expansion for alpha characters is handled differently than in most regex engines.
  * Soda **auto-expands** the most common ranges for you:
    * `[a-z]` → `[abcdefghijklmnopqrstuvwxyz]`
    * `[A-Z]` → `[ABCDEFGHIJKLMNOPQRSTUVWXYZ]`
  * **Other ranges are not expanded automatically** and you may need to manually expand the full set when defining your pattern.
    * If you need `[0-9]`, `[α-ω]`, or similar, you may have to manually expand them.
    * Example: instead of `[0-9]`, write `[0123456789]`.
* **`connection_parameters`**

You can define key-value pairs that will be passed directly to the connection string. For example:

`application_intent: 'ReadOnly'` → `application_intent=ReadOnly` in the final connection string.

### Troubleshoot

<i class="fa-square-xmark">:square-xmark:</i> **Problem:** Metrics Monitoring Scan fails with error:

{% code overflow="wrap" %}

```
Query execution error in ...: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'. (300) (SQLExecDirectW)")
                SELECT
                    SCHEMA_NAME(O.schema_id),
                    O.name,
                    SUM(P.rows),
                    MAX(O.modify_date)
                    , MAX(S.last_user_update)
                FROM sys.objects AS O
                INNER JOIN sys.partitions P ON
                    P.object_id = O.object_id
                LEFT JOIN sys.dm_db_index_usage_stats S ON S.object_id = O.object_id
                WHERE type='U' AND ((SCHEMA_NAME(O.schema_id) = 'tab' AND O.name IN ('<table_name>')))
                GROUP BY O.schema_id, O.name | ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'. (300) (SQLExecDirectW)")
```

{% endcode %}

<i class="fa-square-check">:square-check:</i> **Solution:** Ensure the user login name used to connect to Soda (e.g. `soda_user` ) has VIEW permissions on `sys.dm_db_index_usage_stats` (which is one of the built-in SQL Server Dynamic Management Views DMVs, prefixed with sys.dm\_\*).

For example, run this SQL query (e.g. as sysadmin) to `GRANT` the necessary permissions to the user:

```sql
USE master;
GO 
GRANT VIEW SERVER PERFORMANCE STATE TO soda_user;  --- alternatively, VIEW SERVER STATE
GO
```

<br>

***

{% hint style="info" %}
You are **not logged in to Soda** and are viewing the default public documentation. Learn more about [Licensing & documentation access](/soda-documentation/reference/documentation-access-and-licensing.md).
{% endhint %}


---

# 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://docs.soda.io/soda-documentation/reference/data-source-reference-for-soda-core/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.