Microsoft SQL Server - Coalesce Quality Documentation

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:
→ Access to modify your SQL Server configuration (sysadmin or equivalent)⏱️ Estimated time to finish: 10 minutes.

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, views, procedures and functions
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.foreign_keys / sys.foreign_key_columns	Foreign keys and the columns they reference
sys.views / sys.sql_modules	View, procedure and function definitions
sys.parameters	Procedure and function arguments
sys.databases	Database listing (server-level)
sys.query_store_*	Query logs (opt-in), from Query Store
sys.server_principals / sys.database_principals	Logins and database users (opt-in)

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

Each integration connects to one database. To monitor several databases, create one integration for each.

Authentication

An integration created in the Coalesce Quality UI signs in with SQL Server Authentication: a dedicated login with a username and password. This works with SQL Server, Azure SQL Database and Azure SQL Managed Instance. The DWH Agent and synq-recon also support:

Microsoft Entra ID (Azure AD) authentication for Azure SQL: the default credential chain, a managed identity, a service principal, or a pre-acquired access token. See Azure AD setup.
Windows Authentication, when they run on a machine signed in to your domain, the same as sqlcmd -E. See Windows Authentication.

Create a dedicated user

The setup below uses SQL Server Authentication. If you’re using Azure AD, see Azure AD setup further down, and for a domain account, Windows Authentication. Connect as sysadmin and execute the following:

-- 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.

-- 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 and views with their columns, and all stored procedures and functions
Collect row counts, table sizes, and last-updated timestamps
Read view, stored procedure and function definitions

Step 2: Enable Query Logs (optional)

Coalesce Quality can collect query logs to provide SQL lineage analysis. This uses Query Store, which records query text and execution statistics. VIEW DATABASE STATE from Step 1 is enough to read Query Store data. Query Store must be on for the database.

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

If Query Store is not already enabled:

ALTER DATABASE [your_database] SET QUERY_STORE = ON;

To verify Query Store is active:

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. With Fetch users and service accounts turned on (it shows once query logs are on), Coalesce Quality lists the server’s logins and the database’s users once a day, so query history shows whether each login is a person or a service. SQL Server shows a login every other login only with VIEW ANY DEFINITION (or ALTER ANY LOGIN) on the server. Without it the listing holds only the connecting login and sa.

USE [master];
GRANT VIEW ANY DEFINITION TO synq;

The roles of the database’s users come from VIEW DEFINITION on the database, which Step 1 already grants.

An Azure SQL Database has no server-level permissions: the logins live in the logical server’s master database. There the listing holds the users of the monitored database, including Microsoft Entra users created FROM EXTERNAL PROVIDER, and there is nothing more to grant. Azure SQL Managed Instance works like SQL Server.

Step 4: 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:

GRANT SELECT ON SCHEMA::<schema_name> TO synq;

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

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 for Azure SQL

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

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 4: Custom SQL monitors (optional)
-- GRANT SELECT ON SCHEMA::<schema_name> TO [synq-app];

Query Store access (Step 2), the logins listing (Step 3) and Custom SQL grants (Step 4) work the same way as described above. Then set the Entra ID method on the agent connection. For a service principal, username is the application (client) ID and password is the client secret:

connections:
  azure-sql:
    mssql:
      host: yourserver.database.windows.net
      database: your_database
      fed_auth: ActiveDirectoryServicePrincipal
      username: <application-client-id>
      password: <client-secret>

Other fed_auth values are ActiveDirectoryDefault (the default credential chain), ActiveDirectoryMSI (a managed identity) and ActiveDirectoryAzCli. Leave username and password out for those. To use a pre-acquired token, set access_token instead of fed_auth.

Windows Authentication

The agent or CLI signs in as the Windows or Kerberos identity it runs under, so grant that identity access instead of creating a SQL login. For a service account or a person’s domain account:

-- Create a server login for the domain account
CREATE LOGIN [YOURDOMAIN\quality-svc] FROM WINDOWS;

USE [your_database];
CREATE USER [YOURDOMAIN\quality-svc] FOR LOGIN [YOURDOMAIN\quality-svc];

-- Step 1: Metadata access (required)
GRANT VIEW DATABASE STATE TO [YOURDOMAIN\quality-svc];
GRANT VIEW DEFINITION TO [YOURDOMAIN\quality-svc];

Then set integrated_auth: true on the connection and leave username and password out:

connections:
  sqlserver:
    mssql:
      host: sqlserver.corp.example.com
      database: your_database
      integrated_auth: true

Where the identity comes from depends on the machine:

Windows: the user the agent or synq-recon runs as. Run it from a session signed in to the domain, or run the agent service as the domain service account.
macOS and Linux: a Kerberos ticket. Get one with kinit -c FILE:/tmp/krb5cc_$(id -u) <user>@<REALM> and export KRB5CCNAME=/tmp/krb5cc_$(id -u). The ticket is read from a file credential cache only, and the Kerberos configuration from KRB5_CONFIG or /etc/krb5.conf.

synq-recon check-config --db confirms the sign-in works before you run a suite.

Network Configuration

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 for the complete list of IP addresses by region.

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

Username and password

The SQL Server login you created above.

Encryption

Required (default): encrypts the connection. Azure SQL needs this.
Optional: encryption is negotiated with the server. The login packet is always encrypted.
Disabled: no TLS. Use it only if your SQL Server does not support TLS.

Trust server certificate

Skips TLS certificate verification. Use it for self-signed certificates. Not recommended for production Azure SQL connections.

Audit table FQN (optional)

Fully qualified name of the table for SQL test audit logs, e.g. database.schema.quality_sql_test__audit. Coalesce Quality creates the table if it does not exist. Leave empty if you don’t run SQL tests.

Fetch query logs

Collects query history from Query Store. See Step 2 above.

Fetch users and service accounts (optional)

Shows once query logs are on. Lists the logins and database users once a day. See Step 3 above.

Fetch table metrics

On by default. Reads the row count, size and last change time of each table every 30 minutes. The automatic volume and freshness monitors need these metrics.

Catalog scope and Metrics scope (optional)

Restrict which schemas and tables are ingested into the catalog, and which of those are scanned for metrics. Leave both empty to ingest everything the user can see.

​Data we collect

​Authentication

​Create a dedicated user

​Step 1: Grant metadata access (required)

​Step 2: Enable Query Logs (optional)

​Step 3: Let the user list every login (optional)

​Step 4: Grant access for Custom SQL monitors (optional)

​Azure AD authentication for Azure SQL

​Windows Authentication

​Network Configuration

​Input data in the UI

​Integration name

​Database

​Host

​Port

​Username and password

​Encryption

​Trust server certificate

​Audit table FQN (optional)

​Fetch query logs

​Fetch users and service accounts (optional)

​Fetch table metrics

​Catalog scope and Metrics scope (optional)

Data we collect

Authentication

Create a dedicated user

Step 1: Grant metadata access (required)

Step 2: Enable Query Logs (optional)

Step 3: Let the user list every login (optional)

Step 4: Grant access for Custom SQL monitors (optional)

Azure AD authentication for Azure SQL

Windows Authentication

Network Configuration

Input data in the UI

Integration name

Database

Host

Port

Username and password

Encryption

Trust server certificate

Audit table FQN (optional)

Fetch query logs

Fetch users and service accounts (optional)

Fetch table metrics

Catalog scope and Metrics scope (optional)