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 a source 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. Synapse follows the same permission model as Microsoft SQL Server. It queries system views (sys.schemas, sys.database_principals) to discover schemas and uses standard JDBC metadata APIs for tables, columns, and primary keys.

Authentication Types

Qualytics supports two authentication types for Synapse:

Minimum Synapse Permissions (Source Datastore)

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 catalog 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 the Source Datastore

A source datastore is a storage location used to connect to and access data from external sources. Synapse is an example of such a datastore, specifically a type of JDBC datastore that supports connectivity through the JDBC API. Configuring the Synapse datastore allows the Qualytics platform to access and perform operations on the data, thereby generating valuable insights.

Step 1: Log in to your Qualytics account and click on the Add Source Datastore button located at the top-right corner of the interface.

Step 2: A modal window - Add Datastore will appear, providing you with the options to connect a datastore.

Option I: Create a Datastore with a new Connection

If the toggle for Add New Connection is turned on, then this will prompt you to add and configure the source datastore from scratch without using existing connection details.

Step 1: Select the Synapse connector from the dropdown list and add connection details such as Secret Management, host, port, username, etc.

Secrets Management: This is an optional connection property that allows you to securely store and manage credentials by integrating with HashiCorp Vault and other secret management systems. Toggle it ON to enable Vault integration for managing secrets.

Step 2: The configuration form will expand, requesting credential details before establishing the connection.

Step 3: After adding the source datastore details, click on the Test Connection button to check and verify its connection.

If the credentials and provided details are verified, a success message will be displayed indicating that the connection has been verified.

Option II: Use an Existing Connection

If the toggle for Use an existing connection is turned off, then this will prompt you to configure the source datastore using the existing connection details.

Step 1: Select a connection to reuse existing credentials.

Step 2: Click on the Test Connection button to check and verify the source data connection. If connection details are verified, a success message will be displayed.

Add Enrichment Datastore

After successfully testing and verifying your source datastore connection, you have the option to add an enrichment datastore (recommended). This datastore is used to store analyzed results, including any anomalies and additional metadata in tables. This setup provides comprehensive visibility into your data quality, enabling you to manage and improve it effectively.

Step 1: Whether you have added a source datastore by creating a new datastore connection or using an existing connection, click on the Next button to start adding the Enrichment Datastore.

Step 2: A modal window - Link Enrichment Datastore will appear, providing you with the options to configure an enrichment datastore.

Option I: Create an Enrichment Datastore with a new Connection

If the toggle Add new connection is turned on, then this will prompt you to add and configure the enrichment datastore from scratch without using an existing enrichment datastore and its connection details.

Step 1: Click on the caret button and select Add Enrichment Datastore.

A modal window Link Enrichment Datastore will appear. Enter the following details to create an enrichment datastore with a new connection.

Step 2: Add connection details for your selected enrichment datastore connector.

Step 3: Click on the Test Connection button to verify the selected enrichment datastore connection. If the connection is verified, a flash message will indicate that the connection with the datastore has been successfully verified.

If the connection is verified, a flash message will indicate that the connection with the datastore has been successfully verified.

Step 4: Click on the Finish button to complete the configuration process.

When the configuration process is finished, a modal will display a success message indicating that your datastore has been successfully added.

Step 5: Close the Success dialogue and the page will automatically redirect you to the Source Datastore Details page where you can perform data operations on your configured source datastore.

Option II: Use an Existing Connection

If the Use enrichment datastore option is selected from the caret button, you will be prompted to configure the datastore using existing connection details.

Step 1: Click on the caret button and select Use Enrichment Datastore.

Step 2: A modal window Link Enrichment Datastore will appear. Add a prefix name and select an existing enrichment datastore from the dropdown list.

Step 3: After selecting an existing enrichment datastore connection, you will view the following details related to the selected enrichment:

• Teams: The team associated with managing the enrichment datastore is based on the role of public or private. Example - Marked as Public means that this datastore is accessible to all the users.
• Host: This is the server address where the enrichment datastore instance is hosted. It is the endpoint used to connect to the enrichment datastore environment.
• Database: Refers to the specific database within the enrichment datastore environment where the data is stored.
• Schema: The schema used in the enrichment datastore. The schema is a logical grouping of database objects (tables, views, etc.). Each schema belongs to a single database.

Step 4: Click on the Finish button to complete the configuration process for the existing enrichment datastore.

When the configuration process is finished, a modal will display a success message indicating that your data has been successfully added.

Close the success message and you will be automatically redirected to the Source Datastore Details page where you can perform data operations on your configured source datastore.

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",
        "enrich_only": false,
        "trigger_catalog": true,
        "connection": {
            "name": "your_connection_name",
            "type": "synapse",
            "host": "synapse_host",
            "port": "synapse_port",
            "username": "synapse_username",
            "password": "synapse_password"
        }
    }

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrich_only": false,
        "trigger_catalog": true,
        "connection": {
            "name": "your_connection_name",
            "type": "synapse",
            "host": "synapse_host",
            "port": "synapse_port",
            "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",
        "enrich_only": false,
        "trigger_catalog": true,
        "connection_id": connection-id
    }

# 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

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",
        "enrich_only": true,
        "connection": {
            "name": "your_connection_name",
            "type": "synapse",
            "host": "synapse_host",
            "port": "synapse_port",
            "username": "synapse_username",
            "password": "synapse_password"
        }
    }

    {
        "name": "your_datastore_name",
        "teams": ["Public"],
        "database": "synapse_database",
        "schema": "synapse_schema",
        "enrich_only": true,
        "connection_id": connection-id
    }

# 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

Linking Datastore to an Enrichment Datastore through API

Endpoint (Patch)

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