Synapse

Adding and configuring a Synapse connection within Qualytics empowers the platform to build a symbolic link with your schema to perform operations like data discovery, visualization, reporting, syncing, profiling, scanning, anomaly surveillance, and more.

This documentation provides a step-by-step guide on adding Synapse as both a source and enrichment datastore in Qualytics. It covers the entire process, from initial connection setup to testing and finalizing the configuration.

By following these instructions, enterprises can ensure their Synapse environment is properly connected with Qualytics, unlocking the platform's potential to help you proactively manage your full data quality lifecycle.

Let’s get started 🚀

Synapse Setup Guide

Qualytics connects to Azure Synapse Analytics through the Microsoft JDBC Driver for SQL Server, and Synapse follows the same permission model as Microsoft SQL Server. It queries sys.schemas joined with sys.database_principals to list the schemas, rather than the driver's generic metadata call, which can omit dbo depending on the driver version and the permissions in place. Tables, columns, and primary keys come from the standard JDBC metadata APIs.

Authentication Types

Qualytics supports two authentication types for Synapse:

Minimum Synapse Permissions (Source Datastore)

GRANT VIEW DEFINITION ON USER::<owner_name> TO qualytics_read;

ALTER ROLE <role_name> ADD MEMBER qualytics_read;

Additional Permissions for Enrichment Datastore

When using Synapse as an enrichment datastore, the following additional permissions are required for Qualytics to write metadata tables (e.g., _qualytics_*):

Example: Source Datastore User (Read-Only)

Replace <database_name>, <schema_name>, and <password> with your actual values.

-- Create a login at the server level
CREATE LOGIN qualytics_read WITH PASSWORD = '<password>';

-- Switch to the target database
USE <database_name>;

-- Create a user mapped to the login
CREATE USER qualytics_read FOR LOGIN qualytics_read;

-- Grant connection and read-only access
GRANT CONNECT TO qualytics_read;
GRANT SELECT ON SCHEMA::<schema_name> TO qualytics_read;
GRANT VIEW DEFINITION ON SCHEMA::<schema_name> TO qualytics_read;

Serverless SQL Pool: Additional Read Permissions

In a serverless SQL pool, data is usually exposed as views or external tables that read files from Azure Data Lake Storage. When Qualytics queries them, the files are read with the permissions of the Qualytics user, so two permissions are required in addition to the read-only set above:

USE <database_name>;

GRANT ADMINISTER DATABASE BULK OPERATIONS TO qualytics_read;
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::<credential_name> TO qualytics_read;

Repeat the REFERENCES grant for each credential used by the views the user should read. To list the credentials in a database, run SELECT name FROM sys.database_scoped_credentials.

Example: Enrichment Datastore User (Read-Write)

-- Create a login at the server level
CREATE LOGIN qualytics_readwrite WITH PASSWORD = '<password>';

-- Switch to the target database
USE <database_name>;

-- Create a user mapped to the login
CREATE USER qualytics_readwrite FOR LOGIN qualytics_readwrite;

-- Grant connection, read-write, and table management access
GRANT CONNECT TO qualytics_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::<schema_name> TO qualytics_readwrite;
GRANT CREATE TABLE TO qualytics_readwrite;
GRANT ALTER ON SCHEMA::<schema_name> TO qualytics_readwrite;
GRANT VIEW DEFINITION ON SCHEMA::<schema_name> TO qualytics_readwrite;

Setting up Service Principal Authentication

Service Principal authentication uses a Microsoft Entra ID application instead of a SQL login. Setup has two parts: registering the application in Azure and granting it access to your Synapse database.

Part 1: Register an application in Microsoft Entra ID

1. In the Azure Portal, go to Microsoft Entra ID and select App registrations, then New registration. Give the application a name and register it.
2. On the application's Overview page, copy the Application (client) ID and the Directory (tenant) ID. You will enter these as Client ID and Tenant ID in the Qualytics connection form.
3. Go to Certificates & secrets, create a New client secret, and copy the secret Value right away (it is only shown once). You will enter this as Client Secret in the Qualytics connection form.

Part 2: Grant the application access to your database

A Microsoft Entra administrator of the Synapse workspace must create a database user for the application and grant it the same permissions as a regular read-only user. Replace <app_display_name> and <schema_name> with your actual values.

-- Switch to the target database, then create a user for the application
CREATE USER [<app_display_name>] FROM EXTERNAL PROVIDER;

-- Grant connection and read-only access
GRANT CONNECT TO [<app_display_name>];
GRANT SELECT ON SCHEMA::<schema_name> TO [<app_display_name>];
GRANT VIEW DEFINITION ON SCHEMA::<schema_name> TO [<app_display_name>];

Troubleshooting Common Errors

Detailed Troubleshooting Notes

Authentication Errors

The error Login failed for user indicates that the credentials are incorrect or the login does not exist at the server level.

Common causes:

• Incorrect password: the password does not match the one set for the login.
• Login does not exist: the login was never created at the server level with CREATE LOGIN.
• User not mapped: the login exists but no user is mapped to it in the target database.

Permission Errors

The error The SELECT permission was denied on object means the user authenticated successfully but lacks the necessary grants on the target schema.

Common causes:

• Missing SELECT ON SCHEMA: the user does not have SELECT on the target schema.
• Wrong schema: the user has permissions on dbo but the target tables are in a different schema.
• Missing VIEW DEFINITION: the user cannot see object metadata needed for schema discovery.

Connection Errors

The error Cannot open database requested by the login means the user does not have access to the specified database.

Common causes:

• No user in database: the login exists but CREATE USER ... FOR LOGIN was not run in the target database.
• Database does not exist: the database name in the connection form is incorrect.
• Synapse pool paused: the dedicated SQL pool is paused and needs to be resumed.

Add a Source Datastore

A source datastore is a storage location Qualytics connects to so it can profile, scan, and monitor data. Adding Synapse as a source lets Qualytics query it through the Microsoft JDBC Driver for SQL Server and run quality operations on the tables it discovers.

Before you start, review the Minimum Synapse Permissions the connecting login needs.

Field reference

The Add Datastore page shows the sections below when Synapse is selected. When reusing an existing connection, the Connection Properties and Secrets Management sections come already filled in and read-only: Qualytics has already validated those credentials, so you fill in only the Datastores Extraction and the Datastore Properties. To change a saved connection's credentials, edit the connection through the Manage Connections page; edits there apply to every datastore that reuses the connection.

Connection Properties

These fields define the Synapse endpoint Qualytics connects to. They belong to the connection: when reusing an existing connection, they come already filled in and read-only.

Authentication

Choose how Qualytics authenticates to Synapse. Setting Type changes the credential fields shown below it, so pick the tab that matches your choice. These fields also belong to the connection: already filled in and read-only when reusing one. See Authentication Types for when to use each one.

Secrets Management

This group is optional: use it only if you want Qualytics to pull credentials from a secrets manager instead of typing them into the form. Turn on HashiCorp Vault to show the fields below. Despite the label, any secrets manager that exposes a compatible REST API works, not only HashiCorp Vault; see Secrets Management. It also belongs to the connection: read-only when reusing an existing connection.

Datastores Extraction

Pick the database and the schema or schemas Qualytics should read from. You fill these in on both flows.

Datastore Properties

Common fields for every source datastore, shown below the Datastores Extraction section. You fill these in on both flows.

Steps

There are two ways to set up the connection: reuse a connection you already saved (Existing Connection) or create a new one from scratch (New Connection). The tabs below walk through each option; pick the one you want to follow. Each field is described in the Field reference above.

Add Enrichment Datastore

An enrichment datastore is where Qualytics writes what it finds: anomalies, remediation tables, and record enrichment. Synapse is supported for this role, so the same workspace can hold both the data you monitor and the results.

Field reference

The Enrichment Datastore step shows the sections below when Synapse is selected. When reusing an existing connection, the Connection Properties and Secrets Management sections come already filled in and read-only.

Connection Properties

These fields define the Synapse endpoint Qualytics connects to. They are the same fields as on the source datastore flow, repeated here so this section stands on its own.

Authentication

Choose how Qualytics authenticates to Synapse. Setting Type changes the credential fields shown below it, so pick the tab that matches your choice. These fields also belong to the connection: already filled in and read-only when reusing one. They are the same fields as on the source datastore flow, repeated here so this section stands on its own.

Secrets Management

This group is optional: use it only if you want Qualytics to pull credentials from a secrets manager instead of typing them into the form. Turn on HashiCorp Vault to show the fields below. Despite the label, any secrets manager that exposes a compatible REST API works, not only HashiCorp Vault; see Secrets Management. It also belongs to the connection: read-only when reusing an existing connection.

Enrichment Extraction

Where Qualytics writes the enrichment tables.

Enrichment Properties

Advanced Options

Collapsed by default. Expand it to change how anomalous source records are replicated.

Steps

A Synapse enrichment datastore can be created from two places: as the second step of creating a source datastore, or on its own from the Enrichment Datastores page. Either way you choose between creating a connection from scratch (New Connection) or reusing a saved one (Existing Connection). The tabs below cover both entry points for each option; each field is described in the Field reference above.

API Payload Examples

Creating a Datastore

This section provides a sample payload for creating a datastore. Replace the placeholder values with actual data relevant to your setup.

Endpoint (Post)

/api/datastores (post)

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrichment_only": false,
        "trigger_sync": true,
        "connection": {
            "name": "your_connection_name",
            "type": "synapse",
            "host": "synapse_host",
            "port": 1433,
            "username": "synapse_username",
            "password": "synapse_password"
        }
    }

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrichment_only": false,
        "trigger_sync": true,
        "connection": {
            "name": "your_connection_name",
            "type": "synapse",
            "host": "synapse_host",
            "port": 1433,
            "username": "application_client_id",
            "password": "client_secret_value",
            "parameters": {
                "authentication_type": "SERVICE_PRINCIPAL",
                "tenant_id": "directory_tenant_id"
            }
        }
    }

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrichment_only": false,
        "trigger_sync": true,
        "connection_id": 123
    }

# Step 1: Create a Connection
qualytics connections create \
    --type synapse \
    --name "your_connection_name" \
    --host ${SYNAPSE_HOST} \
    --port 1433 \
    --username ${SYNAPSE_USER} \
    --password ${SYNAPSE_PASSWORD}

# Step 2: Create a Source Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema dbo

# Step 1: Create a Connection
# Pass the Client ID as the username and the client secret Value as the password.
qualytics connections create \
    --type synapse \
    --name "your_connection_name" \
    --host ${SYNAPSE_HOST} \
    --port 1433 \
    --username ${APPLICATION_CLIENT_ID} \
    --password ${CLIENT_SECRET} \
    --parameters '{"authentication_type": "SERVICE_PRINCIPAL", "tenant_id": "directory_tenant_id"}'

# Step 2: Create a Source Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema dbo

Creating an Enrichment Datastore

Endpoint (Post)

/api/datastores (post)

This section provides a sample payload for creating an enrichment datastore. Replace the placeholder values with actual data relevant to your setup.

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrichment_only": true,
        "connection": {
            "name": "your_connection_name",
            "type": "synapse",
            "host": "synapse_host",
            "port": 1433,
            "username": "synapse_username",
            "password": "synapse_password"
        }
    }

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrichment_only": true,
        "connection": {
            "name": "your_connection_name",
            "type": "synapse",
            "host": "synapse_host",
            "port": 1433,
            "username": "application_client_id",
            "password": "client_secret_value",
            "parameters": {
                "authentication_type": "SERVICE_PRINCIPAL",
                "tenant_id": "directory_tenant_id"
            }
        }
    }

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrichment_only": true,
        "connection_id": 123
    }

# Step 1: Create a Connection
qualytics connections create \
    --type synapse \
    --name "your_connection_name" \
    --host ${SYNAPSE_HOST} \
    --port 1433 \
    --username ${SYNAPSE_USER} \
    --password ${SYNAPSE_PASSWORD}

# Step 2: Create an Enrichment Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema your_enrichment_schema \
    --enrichment-only

# Step 1: Create a Connection
qualytics connections create \
    --type synapse \
    --name "your_connection_name" \
    --host ${SYNAPSE_HOST} \
    --port 1433 \
    --username ${APPLICATION_CLIENT_ID} \
    --password ${CLIENT_SECRET} \
    --parameters '{"authentication_type": "SERVICE_PRINCIPAL", "tenant_id": "directory_tenant_id"}'

# Step 2: Create an Enrichment Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema your_enrichment_schema \
    --enrichment-only

Linking Datastore to an Enrichment Datastore through API

Endpoint (Patch)

/api/datastores/{datastore-id}/enrichment/{enrichment-id} (patch)