Microsoft SQL Server

Adding and configuring Microsoft SQL Server connection within Qualytics empowers the platform to build a symbolic link with your database 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 Microsoft SQL Server 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 Microsoft SQL Server 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 🚀

Microsoft SQL Server Setup Guide

Qualytics connects to Microsoft SQL Server through the Microsoft JDBC Driver for SQL Server. It queries sys.schemas joined with sys.database_principals to list the schemas the login can see, 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.

Minimum SQL Server 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 SQL Server 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;

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;

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.
• Service Principal misconfiguration: when using Entra ID authentication, the Client ID, Client Secret, or Tenant ID is incorrect.

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 the definitions of the tables and views inside the schema.

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.
• Database is offline: the target database is in a recovery or offline state.

Scanning Actively Updated Tables

If a SQL Server database is updated by another process while Qualytics scans it (for example, an operational or reporting database with scheduled data loads, such as an Epic Clarity environment), a scan can occasionally fail partway through with a message that the connection was interrupted. This happens when the source ends the read session, often during a maintenance or data-load window, rather than because of a data-quality or connection problem. Re-running the scan usually succeeds.

To reduce these interruptions:

• Schedule scans to run outside the source's maintenance or data-load window.
• Use an incremental scan on large, frequently updated tables so each run reads only new or changed records.
• If the source is heavily loaded during your scan window, point the datastore at a read replica or reporting copy.

Qualytics reads SQL Server without blocking writes, so scanning does not lock the tables being read. For the full walkthrough, see Scan Troubleshooting.

Add a Source Datastore

A source datastore is a storage location Qualytics connects to so it can profile, scan, and monitor data. Adding Microsoft SQL Server 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 SQL Server Permissions the connecting login needs.

Field reference

The Add Datastore page shows the sections below when Microsoft SQL Server 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 SQL Server instance 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 SQL Server. 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.

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, and name the instance if your server uses one. 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. Microsoft SQL Server is supported for this role, so the same instance can hold both the data you monitor and the results.

Field reference

The Enrichment Datastore step shows the sections below when Microsoft SQL Server 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 SQL Server instance 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 SQL Server. 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 Microsoft SQL Server 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

This section provides detailed examples of API payloads to guide you through the process of creating and managing datastores using Qualytics API.

Each example includes endpoint details, sample payloads, and instructions on how to replace placeholder values with actual data relevant to your setup.

Creating a Source Datastore

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

Endpoint: /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "sqlserver_database",
    "schema": "sqlserver_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection": {
        "name": "your_connection_name",
        "type": "sqlserver",
        "host": "sqlserver_host",
        "port": 1433,
        "username": "sqlserver_username",
        "password": "sqlserver_password"
    }
}

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "sqlserver_database",
    "schema": "sqlserver_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection": {
        "name": "your_connection_name",
        "type": "sqlserver",
        "host": "sqlserver_host",
        "port": 1433,
        "username": "application_client_id",
        "password": "client_secret",
        "parameters": {
            "authentication_type": "SERVICE_PRINCIPAL",
            "tenant_id": "azure_ad_tenant_id"
        }
    }
}

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

# Step 1: Create a Connection
qualytics connections create \
    --type sqlserver \
    --name "your_connection_name" \
    --host ${SQLSERVER_HOST} \
    --port 1433 \
    --username ${SQLSERVER_USER} \
    --password ${SQLSERVER_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

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

Endpoint: /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "sqlserver_database",
    "schema": "sqlserver_enrichment_schema",
    "enrichment_only": true,
    "connection": {
        "name": "your_connection_name",
        "type": "sqlserver",
        "host": "sqlserver_host",
        "port": 1433,
        "username": "sqlserver_username",
        "password": "sqlserver_password"
    }
}

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

# Step 1: Create a Connection
qualytics connections create \
    --type sqlserver \
    --name "your_connection_name" \
    --host ${SQLSERVER_HOST} \
    --port 1433 \
    --username ${SQLSERVER_USER} \
    --password ${SQLSERVER_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

Link an Enrichment Datastore to a Source Datastore

Use the provided endpoint to link an enrichment datastore to a source datastore:

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