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

# Microsoft SQL Server

> Integrating Microsoft SQL Server with Coalesce Quality

<Note>
  This guide explains how to connect Coalesce Quality to Microsoft SQL Server securely.

  We need this information so we can collect metadata about your tables.

  To be able to finish this guide, you'll need the following:
  <br /> → Access to modify your SQL Server configuration (sysadmin or equivalent)

  ⏱️ Estimated time to finish: 10 minutes.
</Note>

## Data we collect

For the automated data anomaly testing we collect the following:

* Number of rows in every table in a given database
* Timestamp of the last statistics update for tables
* Size of tables in bytes

**To provide out-of-the-box monitors for data volume and freshness, we don't require access to your actual data.**

We access metadata from the following system views:

| View                         | Description                                 |
| ---------------------------- | ------------------------------------------- |
| sys.objects                  | List of user tables and views               |
| sys.schemas                  | Schema metadata                             |
| sys.columns                  | Column metadata and types                   |
| sys.extended\_properties     | Table and column comments (MS\_Description) |
| sys.partitions               | Row counts per table                        |
| sys.allocation\_units        | Table size in bytes                         |
| sys.indexes                  | Index and constraint metadata               |
| sys.index\_columns           | Index column details                        |
| sys.check\_constraints       | CHECK constraint definitions                |
| sys.views / sys.sql\_modules | View definitions                            |
| sys.databases                | Database listing (server-level)             |

For Custom SQL monitors, we need `SELECT` access to your actual data tables.

<Note>
  SQL Server is database-scoped — the integration connects to a single database at a time.
  If you need to monitor multiple databases, create a separate integration for each.
</Note>

## Authentication

Coalesce Quality supports multiple authentication methods for SQL Server:

### Option 1: SQL Server Authentication (username/password)

Standard SQL authentication with a dedicated login. Works with all SQL Server deployments.

### Option 2: Azure AD Authentication (Azure SQL)

For Azure SQL Database and Azure SQL Managed Instance, we support Azure AD federated authentication:

* **Azure AD Default** — uses the default credential chain (Managed Identity → environment → CLI)
* **Azure AD Managed Identity** — for workloads running in Azure (VMs, AKS, App Service)
* **Azure AD Service Principal** — authenticate with a client ID and secret
* **Access Token** — provide a pre-acquired OAuth access token

<Note>
  Azure AD authentication is the recommended method for Azure SQL. It eliminates password management and integrates with Azure RBAC.
</Note>

## Create a dedicated user

The setup below uses **SQL Server Authentication**. If you're using Azure AD, see [Azure AD setup](#azure-ad-authentication-azure-sql) further down.

Connect as `sysadmin` and execute the following:

```sql theme={null}
-- Create a server login
CREATE LOGIN synq WITH PASSWORD = '<password>';

-- Switch to the database to monitor
USE [your_database];

-- Create a database user
CREATE USER synq FOR LOGIN synq;
```

### Step 1: Grant metadata access (required)

These grants give Coalesce Quality read-only access to catalog views, table statistics, and SQL definitions. This is all that's needed for out-of-the-box volume and freshness monitors.

```sql theme={null}
-- Catalog views: tables, columns, indexes, row counts, sizes
GRANT VIEW DATABASE STATE TO synq;

-- SQL definitions for views and stored procedures
GRANT VIEW DEFINITION TO synq;
```

With just these two grants, Coalesce Quality can:

* Discover all tables, views, and their columns
* Collect row counts, table sizes, and last-updated timestamps
* Read view and stored procedure definitions

### Step 2: Enable Query Logs (optional)

Coalesce Quality can collect query logs to provide SQL lineage analysis. This uses [Query Store](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store), which records query text and execution statistics.

No additional grants are needed — `VIEW DATABASE STATE` (already granted above) is sufficient to read Query Store data. However, Query Store must be enabled on the database.

<Note>
  Azure SQL Database has Query Store enabled by default. For on-premises SQL Server 2016+, Query Store must be enabled manually.
</Note>

If Query Store is not already enabled:

```sql theme={null}
ALTER DATABASE [your_database] SET QUERY_STORE = ON;
```

To verify Query Store is active:

```sql theme={null}
SELECT actual_state_desc FROM sys.database_query_store_options;
-- Should return 'READ_WRITE'
```

Enable **Fetch query logs** in the Coalesce Quality integration settings to start collecting query history.

### Step 3: Grant access for Custom SQL monitors (optional)

If you want Coalesce Quality to run Custom SQL monitors that query your actual data, grant `SELECT` on the relevant schemas:

```sql theme={null}
GRANT SELECT ON SCHEMA::<schema_name> TO synq;
```

Repeat for each schema you want to monitor. To grant access to all user schemas:

```sql theme={null}
DECLARE @schema NVARCHAR(128);
DECLARE schema_cursor CURSOR FOR
    SELECT name FROM sys.schemas
    WHERE name NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest');
OPEN schema_cursor;
FETCH NEXT FROM schema_cursor INTO @schema;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('GRANT SELECT ON SCHEMA::' + @schema + ' TO synq');
    FETCH NEXT FROM schema_cursor INTO @schema;
END;
CLOSE schema_cursor;
DEALLOCATE schema_cursor;
```

### Azure AD Authentication (Azure SQL)

For Azure SQL with Azure AD, create the user directly from the Azure AD identity instead of a SQL login:

```sql theme={null}
USE [your_database];

-- For a service principal or managed identity
CREATE USER [synq-app] FROM EXTERNAL PROVIDER;

-- Step 1: Metadata access (required)
GRANT VIEW DATABASE STATE TO [synq-app];
GRANT VIEW DEFINITION TO [synq-app];

-- Step 3: Custom SQL monitors (optional)
-- GRANT SELECT ON SCHEMA::<schema_name> TO [synq-app];
```

Query Store access (Step 2) and Custom SQL grants (Step 3) work the same way as described above.

## Network Configuration

<Note>
  If your SQL Server instance is behind a firewall or has network access controls, you may need to whitelist Coalesce Quality's IP addresses. See [IP Whitelist](/security/ip) for the complete list of IP addresses by region.
</Note>

## Input data in the UI

### Integration name

E.g., `azure-sql-production`

### Database

The name of the database you want to monitor

### Host

The hostname of your SQL Server instance, e.g., `yourserver.database.windows.net` for Azure SQL

### Port

The default is `1433`

### Authentication method

Choose one of:

* **SQL Server Authentication**: provide username and password
* **Azure AD Service Principal**: provide Application (Client) ID and Client Secret
* **Azure AD Managed Identity**: no credentials needed when running in Azure
* **Access Token**: provide a pre-acquired OAuth access token

### Encryption

Connection encryption mode. Set to `true` (default) for encrypted connections. Use `false` or `disable` for unencrypted connections (e.g., on-premises without TLS).

### Trust server certificate

Enable to skip TLS certificate verification. Useful for self-signed certificates or development environments.