> ## Documentation Index
> Fetch the complete documentation index at: https://docs.validio.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Microsoft SQL Server

> Connect Validio to Microsoft SQL Server to monitor and validate your operational data.

SQL Server is a relational database management platform developed by Microsoft. Integrating Validio with your SQL Server allows you to monitor your data and tables, run SQL queries on your tables and views, and analyze and validate the data in your tables.

To integrate with Microsoft SQL Server,

1. Create a service account in SQL Server with the appropriate permissions for Validio. See [Prerequisites](#prerequisites).
2. Create a Validio credential to connect to the service account. See [Add a SQL Server Credential](#add-a-sql-server-credential).
3. Create a Validio source to monitor the tables in the SQL Server database. See [Add a SQL Server Source](#add-a-sql-server-source).

## Prerequisites

Create a service account in SQL Server with the appropriate permissions for Validio to read and validate your data. For more information about credentials in Validio, see [Credentials](https://docs.validio.io/v10.1/docs/credentials.md).

> [!WARNING]
>
> **Credential Permission Requirements**
>
> Validio Credentials **require** `VIEWER` access rights when connecting to sources to read and access data. Admins must ensure that they do not provide `EDITOR` access rights to their credentials.

The following SQL script provides steps for setting up a user with the necessary permissions for the source in Validio. Copy the script into a SQL worksheet and follow the steps.

### Setup for user and password-based authentication

```sql title="SQL" theme={null}
-- Add a new validio user/password account to the server
USE master;
CREATE LOGIN validio WITH PASSWORD = '<password>';

-- Add a validio user to the database, repeat for each database
USE <database>;
CREATE USER validio FOR LOGIN validio;
```

### Setup for Microsoft Entra ID-based authentication

```sql title="SQL" theme={null}
-- Add a validio Microsoft Entra ID application to the database, repeat for each database
USE <database>;
CREATE USER validio FROM EXTERNAL PROVIDER;
```

### Permissions

```sql title="SQL" theme={null}
-- Repeat these steps for each database
USE <database>;

-- Grant access to read all tables and views
ALTER ROLE db_datareader ADD MEMBER validio;

-- Grant access to view definitions (used for lineage)
GRANT VIEW DEFINITION TO validio;

-- Grant access to query logs (used for lineage)
-- For Azure SQL Database
GRANT VIEW DATABASE PERFORMANCE STATE TO validio;

-- For Azure SQL Managed Instance or SQL Server 2022 (16.x) and later
USE master;
GRANT VIEW SERVER PERFORMANCE STATE TO validio;

-- For SQL Server 2019 (15.x) and earlier
USE master;
GRANT VIEW SERVER STATE TO validio;
```

## Add a SQL Server credential

![The Microsoft SQL Server credential configuration form, showing host, port, default database, and authentication type fields](https://mintcdn.com/validio/h4NQPs1kJwlpB1Yj/images/docs/integrations/810-mssqlserver-credential-config.png?fit=max\&auto=format\&n=h4NQPs1kJwlpB1Yj\&q=85\&s=391e22a9aefca8098a91efa6a7258713)

Configuration for a Microsoft SQL Server credential

To add a credential for Microsoft SQL Server,

1. Navigate to **Credentials** and click **+ New credential**.
2. Under **Namespace**, select a namespace where the resources will be created.
3. For **Credential Type**, select **Microsoft SQL Server Credential**.
4. Fill in the credential parameter fields. Refer to the [SQL Server Credential Parameters](#sql-server-credential-parameters) table.
5. Check **Use for catalog and schema checks** to automatically discover credentials and add them to the catalog page.
6. Click **Create**.

Validio will validate the connection to the SQL Server account. If validation passes, Validio will automatically start fetching data. If validation fails, check that you provided the correct parameter values and try again.

Once the credential is created, you can add a [source](https://docs.validio.io/v10.1/docs/sources.md) to monitor SQL Server data.

### SQL Server credential parameters

| Parameter | Description |
| :- | :- |
| Name | Identifier for the credential, referenced when configuring a source. |
| Host | DNS hostname or IP address to the SQL server. |
| Port | Port number of the SQL server. |
| Default database | Name of the default SQL server database where the table to read is included. |
| Authentication type | Select **User and password** or **Entra ID**. |

#### User and password authentication

| Parameter | Description |
| :- | :- |
| Username | Username for the SQL Server account. |
| Password | Password for the SQL Server account. |

#### Entra ID authentication

| Parameter | Description |
| :- | :- |
| Client ID | Application (client) ID which you can find in your Azure Portal. |
| Client secret | Client secret value for your application, which you can find in your Azure Portal. |

## Add a SQL Server source

![The Microsoft SQL Server source configuration form, showing credential, database, schema, and table selection](https://mintcdn.com/validio/h4NQPs1kJwlpB1Yj/images/docs/integrations/810-mssqlserver-source-config.png?fit=max\&auto=format\&n=h4NQPs1kJwlpB1Yj\&q=85\&s=ac641c0d9537715f601fbb58847ed273)

Configuration for a Microsoft SQL Server source

To add a source for Microsoft SQL Server,

1. Navigate to **Sources** and click **+ New source**.
2. Under Source type, select **SQL Server**.
3. Under **Config**,
   1. Select the valid **Credential** or create a new credential to authenticate your connection to the data warehouse.
   2. You have two options for specifying the dataset and tables to monitor:
      1. **Select an existing table** -- Enter the **Database**, **Schema**, and **Table** to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the [SQL Server Source Configuration Parameters](#sql-server-source-parameters) table.
      2. **Use Custom SQL** -- Write a valid SQL query to specify the tables to monitor.
   3. Set the **Polling schedule**, which is how frequently the validators on the source will check for changes.
4. Under **Schema**, click **Continue** to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
5. Under **Source details**,
   1. Add **Tags** to help group related sources or to use for routing notifications.
   2. Add an **Owner** who will be the contact for incident notifications.
   3. Assign a **Priority**, which indicates the importance of incidents detected on this source.
6. Click **Continue** to create the source.
   Source names are generated automatically and  will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

### SQL Server source parameters

| Parameter | Description |
| :- | :- |
| Credential | Microsoft SQL Server Credential. |
| Database | Name of the SQL Server database. |
| Schema | Name of the schema that contains the table. |
| Table | Name of the table to read from. |
| Polling schedule (cron expression) | |