Skip to main content
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:
ViewDescription
sys.objectsList of user tables and views
sys.schemasSchema metadata
sys.columnsColumn metadata and types
sys.extended_propertiesTable and column comments (MS_Description)
sys.partitionsRow counts per table
sys.allocation_unitsTable size in bytes
sys.indexesIndex and constraint metadata
sys.index_columnsIndex column details
sys.check_constraintsCHECK constraint definitions
sys.views / sys.sql_modulesView definitions
sys.databasesDatabase listing (server-level)
For Custom SQL monitors, we need SELECT access to your actual data tables.
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.

​
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
Azure AD authentication is the recommended method for Azure SQL. It eliminates password management and integrates with Azure RBAC.

​
Create a dedicated user

The setup below uses SQL Server Authentication. If you’re using Azure AD, see Azure AD setup further down. 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, 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, 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.
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.

​
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: 
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 (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 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

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

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