> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dqlabs.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Setup

> Prerequisites, authentication options, and step-by-step instructions for connecting Microsoft SQL Server to Prizm.

<script type="application/ld+json">
  {`{
            "@context": "https://schema.org",
            "@type": "TechArticle",
            "headline": "SQL Server Setup",
            "description": "Prerequisites, authentication options, and step-by-step instructions for connecting Microsoft SQL Server to Prizm.",
            "url": "https://docs.dqlabs.ai/sources/sql/setup",
            "publisher": {
              "@type": "Organization",
              "name": "DQLabs Inc",
              "logo": "https://media.brand.dev/332adc35-5bc4-4d2b-bf78-256aa4a5e414.svg"
            }
            }`}
</script>

Select your deployment type to see the relevant prerequisites and configuration:

<Tabs>
  <Tab title="SQL Server (On-Premises / VM)">
    ## Prerequisites

    Before connecting, ensure the following are in place:

    * The SQL Server instance is network-accessible from the Prizm agent on **port 1433**.
    * SQL Server TCP/IP is enabled (SQL Server Configuration Manager → Protocols → TCP/IP → Enabled).
    * A dedicated SQL login exists for Prizm with the required permissions (see below).

    ### Required Permissions

    Run the script below against each database you want to include. Replace `<prizm_login>` with your service account name and `<database_name>` with the target database.

    ```sql theme={null}
    -- Create a login for Prizm (if it does not exist)
    USE master;
    CREATE LOGIN <prizm_login> WITH PASSWORD = '<strong_password>';

    -- Grant access to the target database
    USE <database_name>;
    CREATE USER prizm_user FOR LOGIN <prizm_login>;

    -- Grant metadata and observability read access
    GRANT VIEW DEFINITION TO prizm_user;
    GRANT SELECT ON SCHEMA::INFORMATION_SCHEMA TO prizm_user;
    GRANT SELECT ON SCHEMA::sys TO prizm_user;

    -- Grant data quality execution access
    GRANT SELECT ON DATABASE::<database_name> TO prizm_user;

    -- For performance metrics (query stats, session monitoring)
    USE master;
    GRANT VIEW SERVER STATE TO <prizm_login>;
    ```

    | Permission                       | Purpose                                                                                                       |
    | :------------------------------- | :------------------------------------------------------------------------------------------------------------ |
    | `VIEW DEFINITION`                | Schema and stored procedure metadata extraction                                                               |
    | `SELECT` on `INFORMATION_SCHEMA` | Table, column, view metadata                                                                                  |
    | `SELECT` on `sys` schema         | Object-level metadata, FK relationships, partitions, statistics                                               |
    | `SELECT` on database             | Running data quality metrics                                                                                  |
    | `VIEW SERVER STATE`              | Performance metrics — query stats and session counts via `sys.dm_exec_query_stats` and `sys.dm_exec_sessions` |

    <Warning>
      `VIEW SERVER STATE` is a server-level grant. Without it, performance metrics will be unavailable but all catalog, observability, and quality features continue to work.
    </Warning>

    ### Authentication

    Two methods are supported:

    | Method                     | When to use                                                                                                                                                           |
    | :------------------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
    | **Username & Password**    | Recommended for all deployments, including containers. Uses SQL Server Authentication — login managed inside SQL Server.                                              |
    | **Windows Authentication** | Domain-joined on-premises or VM environments where the Prizm agent runs as a Windows service account. Not supported in containerised (Kubernetes/Docker) deployments. |

    For Windows Authentication, create the login using `FROM WINDOWS`:

    ```sql theme={null}
    USE master;
    CREATE LOGIN [DOMAIN\prizm_service_account] FROM WINDOWS;

    USE <database_name>;
    CREATE USER prizm_user FOR LOGIN [DOMAIN\prizm_service_account];
    GRANT VIEW DEFINITION TO prizm_user;
    -- ... (remaining grants as above)
    ```

    ### Network & Firewall

    * TCP port **1433** must be open between the Prizm agent and the SQL Server host.
    * If your SQL Server is behind a firewall or in a private network that Prizm cannot reach directly, deploy the **Prizm Outbound Agent** inside your network. The agent connects outbound to Prizm over WSS port 443 — no inbound firewall rules required.
    * If outbound connectivity from the agent is restricted, allow the Prizm agent's egress IPs:

    | Cloud Platform | Region               | IP Addresses       |
    | :------------- | :------------------- | :----------------- |
    | AWS            | US-EAST-1 (Virginia) | `20.119.65.168/29` |
  </Tab>

  <Tab title="Azure SQL Database / Managed Instance">
    ## Prerequisites

    Before connecting, ensure the following are in place:

    * A SQL login exists inside the Azure SQL database for Prizm (Azure AD / Entra Service Principal authentication is not currently supported).
    * The Prizm agent's egress IP is permitted in the Azure SQL firewall, or the agent is on a network with a private endpoint to the Azure SQL resource.
    * Port **1433** with TLS is open from the Prizm agent to `<your-server>.database.windows.net`.

    <Note>
      No Azure subscription-level or resource-level RBAC is required. The agent connects to Azure SQL over TDS using a SQL login — it does not call Azure Resource Manager APIs.
    </Note>

    ### Required Permissions

    Run the script below inside each Azure SQL database you want to include. Replace `<prizm_login>` with your chosen login name and `<database_name>` with the target database name.

    ```sql theme={null}
    -- Create a SQL login (run in the master database of the logical server)
    CREATE LOGIN <prizm_login> WITH PASSWORD = '<strong_password>';

    -- Grant access to the target database
    USE <database_name>;
    CREATE USER prizm_user FOR LOGIN <prizm_login>;

    -- Grant metadata and observability read access
    GRANT VIEW DEFINITION TO prizm_user;
    GRANT SELECT ON SCHEMA::INFORMATION_SCHEMA TO prizm_user;
    GRANT SELECT ON SCHEMA::sys TO prizm_user;

    -- Grant data quality execution access
    GRANT SELECT ON DATABASE::<database_name> TO prizm_user;
    ```

    | Permission                       | Purpose                                                         |
    | :------------------------------- | :-------------------------------------------------------------- |
    | `VIEW DEFINITION`                | Schema and stored procedure metadata extraction                 |
    | `SELECT` on `INFORMATION_SCHEMA` | Table, column, view metadata                                    |
    | `SELECT` on `sys` schema         | Object-level metadata, FK relationships, partitions, statistics |
    | `SELECT` on database             | Running data quality metrics                                    |

    <Note>
      `VIEW SERVER STATE` is not available in Azure SQL Database. Performance metrics (query stats, session counts) are not supported for Azure SQL — all catalog, observability, schema, and quality features work without it.
    </Note>

    ### Authentication

    Use **Username & Password** with the following host details:

    | Field        | Value                                |
    | :----------- | :----------------------------------- |
    | **Host**     | `<your-server>.database.windows.net` |
    | **Port**     | `1433`                               |
    | **Username** | The SQL login name created above     |
    | **Password** | The SQL login password               |

    Azure AD / Entra Service Principal token authentication is not currently supported by this connector.

    ### Network & Firewall

    The Prizm agent connects to Azure SQL over **TDS on port 1433 with TLS**. Choose one of the following access methods:

    **Option 1 — Azure SQL firewall rules (public endpoint)**

    Add the Prizm agent's egress IP to the Azure SQL firewall: **Azure Portal → SQL Server → Networking → Firewall rules → Add a firewall rule**.

    | Cloud Platform | Region               | IP Addresses       |
    | :------------- | :------------------- | :----------------- |
    | AWS            | US-EAST-1 (Virginia) | `20.119.65.168/29` |

    **Option 2 — Private endpoint**

    If your Azure SQL resource uses a private endpoint, ensure the Prizm agent is deployed on a network (VNet or peered network) with connectivity to the private endpoint. No public firewall rules are needed in this case.

    <Note>
      No Azure subscription-level or resource-group-level RBAC is required for either access method. The connector uses TDS only and does not interact with Azure Resource Manager.
    </Note>
  </Tab>
</Tabs>

***

## Connector Setup

The steps below apply to both deployment types. Where a field value differs between SQL Server and Azure SQL, the difference is noted inline.

<Steps>
  <Step title="Open the connector wizard">
    Navigate to **Sources** and click **Add**. Select **SQL Server** from the source type grid.

    <Frame>
      <img src="https://mintcdn.com/dqlabs/-PfGmQ9dxOvGkLMa/images/image-121.png?fit=max&auto=format&n=-PfGmQ9dxOvGkLMa&q=85&s=1ad8e8a54d3e725486bb5fef62df3295" alt="Image" width="2598" height="1244" data-path="images/image-121.png" />
    </Frame>

    | Field               | SQL Server (On-Premises / VM)                  | Azure SQL                                          |
    | :------------------ | :--------------------------------------------- | :------------------------------------------------- |
    | **Connection Name** | A label for this instance (e.g., `mssql-prod`) | A label for this instance (e.g., `azure-sql-prod`) |
    | **Host**            | SQL Server hostname or IP address              | `<your-server>.database.windows.net`               |
    | **Port**            | `1433` (default)                               | `1433`                                             |
    | **Database**        | Target database name                           | Target database name                               |
  </Step>

  <Step title="Choose an authentication method">
    Select **Username and Password** from the Authentication Type dropdown and enter the SQL login credentials created in the Prerequisites step.

    For on-premises deployments on domain-joined hosts, **Windows Authentication** is also available — select it from the dropdown. No credentials are entered; authentication is handled by the agent host's OS identity.

    <Warning>
      Windows Authentication is not supported in containerised (Kubernetes/Docker) deployments. Use Username & Password for container-based installations, including Helm chart deployments.
    </Warning>
  </Step>

  <Step title="Configure asset scope">
    Once valid authentication details are provided, the asset selection page opens. Use Include and Exclude wildcard patterns to control which schemas and tables are onboarded.

    <Frame>
      <img src="https://mintcdn.com/dqlabs/-PfGmQ9dxOvGkLMa/images/image-122.png?fit=max&auto=format&n=-PfGmQ9dxOvGkLMa&q=85&s=e2ac377a8492d038f8e3a12da85effcd" alt="Image" width="2590" height="1150" data-path="images/image-122.png" />
    </Frame>

    | Level      | Example Patterns                                         |
    | :--------- | :------------------------------------------------------- |
    | Schema     | Include: `dbo`, `finance` — Exclude: `tmp_*`, `_staging` |
    | Table/View | Select the required table or view from the dropdown      |

    <Note>
      Exclude rules take precedence over include rules when both match the same object.
    </Note>
  </Step>

  <Step title="Job schedules">
    | Job Type      | Default Schedule | Objects Covered                                 |
    | :------------ | :--------------- | :---------------------------------------------- |
    | Observability | Every 1 hour     | Table, Column, View, Lineage                    |
    | Catalog       | Every 24 hours   | Database, Function, Procedure                   |
    | Performance   | Every 24 hours   | Query history, session stats (on-premises only) |

    You can override each schedule individually from the connector detail page after saving. If a job is triggered manually, it runs immediately without affecting the configured schedule.
  </Step>

  <Step title="Save and verify">
    Click **Update** to save the connection. Prizm runs an initial full scan immediately — assets appear in the catalog within a few minutes. Navigate to **Settings → Connectors → Logs** to monitor scan progress.
  </Step>
</Steps>

## Next Steps

<CardGroup cols={2}>
  <Card title="What We Collect" icon="database" href="/sources/sql/what-we-collect">
    See the full field-level breakdown of every metadata object Prizm extracts.
  </Card>

  <Card title="FAQ" icon="circle-question" href="/sources/sql/faq">
    Common questions about credentials, scoping, and permissions.
  </Card>
</CardGroup>