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

# FAQ

> Common questions about connecting Microsoft SQL Server to Prizm, permissions, scoping, and feature behaviour.

<script type="application/ld+json">
  {`{
            "@context": "https://schema.org",
            "@type": "TechArticle",
            "headline": "SQL Server FAQ",
            "description": "Common questions about connecting Microsoft SQL Server to Prizm, permissions, scoping, and feature behaviour.",
            "url": "https://docs.dqlabs.ai/sources/sql/faq",
            "publisher": {
              "@type": "Organization",
              "name": "DQLabs Inc",
              "logo": "https://media.brand.dev/332adc35-5bc4-4d2b-bf78-256aa4a5e414.svg"
            }
            }`}
</script>

## Connection & Authentication

<AccordionGroup>
  <Accordion title="Which authentication methods are supported?">
    Prizm supports two authentication methods for SQL Server:

    | Method                     | Best for                                                                                                                                                |
    | :------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------ |
    | **Username & Password**    | All deployments — on-premises, VM, Azure SQL, and containers. Recommended for most setups.                                                              |
    | **Windows Authentication** | Domain-joined on-premises or VM environments where the Prizm agent runs as a Windows service account. Not fully supported in containerised deployments. |

    Azure AD / Entra Service Principal token authentication is not currently supported. For Azure SQL, use a SQL login (Username & Password) with the host set to `<your-server>.database.windows.net`.
  </Accordion>

  <Accordion title="Does connecting to Azure SQL require any Azure subscription or resource-level permissions?">
    No. The Prizm agent connects to Azure SQL over TDS (port 1433) using a SQL login — it does not call Azure Resource Manager APIs. No Reader, Contributor, or any other Azure RBAC role is required on the SQL Server resource, resource group, or subscription.

    The only Azure-side configuration required is:

    1. A SQL login with the permissions in the Prerequisites script (created inside the Azure SQL database).
    2. The agent's egress IP allowed in the Azure SQL firewall rules (or access via private endpoint).
  </Accordion>

  <Accordion title="Is the SQL Server connector included in the Prizm Helm chart?">
    Yes. The SQL Server connector is available out of the box in the standard Prizm Helm chart deployment — no additional connector chart or sidecar is required. Configure a new connection from **Sources → Add Source → SQL Server** after the Helm deployment is complete.
  </Accordion>

  <Accordion title="Does Prizm support Vault integration for SQL Server credentials?">
    Yes. Prizm supports external Vault integration to connect to SQL Server and extract credentials. With Vault-backed connections, credential rotation happens entirely within your secrets manager — you update the secret in Vault, and Prizm automatically picks up the new credentials on the next job run without any changes to the connector configuration. See the **Use Vault** option in the connection form when setting up authentication.
  </Accordion>

  <Accordion title="Are there limits on how many SQL Server connections I can create?">
    There is no predefined limit on the number of connectors. You can create separate connectors for each SQL Server instance, database, or environment (e.g., `mssql-prod`, `mssql-dev`).
  </Accordion>

  <Accordion title="Does Prizm support SQL Server on Azure vs. on-premises?">
    Yes. Prizm supports SQL Server in all deployment modes: on-premises, self-hosted VM, Azure SQL Database, and Azure SQL Managed Instance. Use **Username & Password** for all deployments. For Azure SQL, set the host to `<your-server>.database.windows.net` and port `1433` — the connector connects over TDS with TLS, the same as any other SQL Server instance.
  </Accordion>

  <Accordion title="How do I connect a SQL Server instance in a private network?">
    Use the **Prizm Outbound Agent**. The agent runs inside your network and connects outbound to Prizm over WSS port 443 — no inbound firewall rules are required. This is the recommended approach for air-gapped environments or deployments behind a strict network boundary.
  </Accordion>
</AccordionGroup>

## Permissions

<AccordionGroup>
  <Accordion title="What is VIEW SERVER STATE and why does Prizm need it?">
    `VIEW SERVER STATE` is a server-level permission that grants access to SQL Server dynamic management views (DMVs) such as `sys.dm_exec_query_stats` and `sys.dm_exec_sessions`. Prizm uses these for:

    * Query count per asset over a configurable time window
    * Session count (spike detection for platform health)
    * Query execution time statistics

    Without this grant, performance metrics will be unavailable. It is granted at the server level (`USE master; GRANT VIEW SERVER STATE TO <login>`) rather than at the database level.
  </Accordion>

  <Accordion title="What is the minimum permission set if I only want catalog discovery?">
    For catalog-only (no observability, quality metrics, or lineage):

    ```sql theme={null}
    USE <database_name>;
    GRANT VIEW DEFINITION TO prizm_user;
    GRANT SELECT ON SCHEMA::INFORMATION_SCHEMA TO prizm_user;
    ```

    No `SELECT` on data tables or `VIEW SERVER STATE` required for metadata-only discovery.
  </Accordion>

  <Accordion title="Does Prizm need access to all databases on the instance?">
    No. Prizm connects per-database. Run the permissions script once per database you want to include, and configure scope in the connector wizard to include only the targeted databases and schemas. Databases not granted access are not visible to Prizm.
  </Accordion>

  <Accordion title="Can I use an existing service account instead of creating a new one?">
    Yes. Any login with the required grants will work. The `CREATE LOGIN` step in the prerequisites script creates a new login for clarity — substitute your existing service account name throughout.
  </Accordion>
</AccordionGroup>

## Scoping & Object Inclusion

<AccordionGroup>
  <Accordion title="Can I exclude specific schemas or tables?">
    Yes. Use the **Include / Exclude** wildcard patterns in the Asset Scope step of the connector wizard. Exclude rules take precedence over include rules when both match the same object. For example:

    * Include: `dbo`, `finance`
    * Exclude: `tmp_*`, `_staging`
  </Accordion>

  <Accordion title="Why is a table I expect not appearing in Prizm?">
    Check that:

    1. The table's schema matches your include patterns.
    2. The table is not matched by an exclude pattern.
    3. The Prizm service account has `SELECT` and `VIEW DEFINITION` on that table.
    4. The Catalog job has completed at least once since the table was created.

    Run `SELECT HAS_PERMS_BY_NAME('<schema>.<table>', 'OBJECT', 'SELECT')` as the Prizm login to verify permissions directly.
  </Accordion>

  <Accordion title="Does Prizm catalog stored procedures?">
    Yes. Stored procedures are cataloged for lineage — Prizm extracts the procedure definition from `sys.sql_modules` and uses `sys.dm_sql_referenced_entities` to map which tables and views the procedure reads from or writes to. Stored procedures appear in the lineage graph as source or target nodes.
  </Accordion>
</AccordionGroup>

## Observability & Freshness

<AccordionGroup>
  <Accordion title="How does Prizm calculate freshness for SQL Server tables?">
    Prizm reads `last_user_update` from `sys.dm_db_index_usage_stats`. This timestamp reflects the last time any user DML operation (INSERT, UPDATE, DELETE, MERGE) was executed against the table. It resets when the SQL Server service restarts, so freshness may briefly appear stale after a restart.

    If `last_user_update` is NULL (no DML since the last service restart), freshness shows as `NA` until the next write occurs.
  </Accordion>

  <Accordion title="Why is FRESHNESS showing 'NA' for my table?">
    `NA` appears when:

    1. The table has not been written to since the last SQL Server service restart (no `last_user_update` value).
    2. The Observability job has only run once (needs at least two data points for trend detection).
    3. The Prizm service account lacks `VIEW SERVER STATE` (required to read `sys.dm_db_index_usage_stats`).
  </Accordion>

  <Accordion title="How does Prizm detect schema changes?">
    Schema changes are detected by comparing the column list (from `sys.columns`) between Observability job runs. When a change is detected, Prizm diffs the current column list against the stored snapshot. Added, removed, renamed, and type-changed columns are each reported as separate events.
  </Accordion>

  <Accordion title="What is the difference between an alert and an issue?">
    An **alert** is an automated signal that a monitored metric or quality check has breached its threshold. Alerts are generated by the observability engine without any human action.

    An **issue** is a validated, actionable problem created from one or more related alerts. Issues are tracked work items that can be assigned, commented on, and resolved.

    | Stage           | What it means                                                        |
    | :-------------- | :------------------------------------------------------------------- |
    | **Alert**       | A metric or test breached its threshold                              |
    | **Alert Group** | Related alerts on the same asset are clustered together              |
    | **Issue**       | The group meets priority or correlation criteria and requires action |
  </Accordion>

  <Accordion title="How are anomaly detection thresholds calculated?">
    Prizm uses time-series forecasting models to compute dynamic thresholds automatically. The system uses up to 100 recent historical run values, applies a Hampel filter to remove outliers, and selects the forecasting model based on data characteristics (EWMA for stable series, AutoReg for short series, Prophet for default, SARIMAX for seasonal data). At least 5 historical values are required before alerts begin firing on a new metric.
  </Accordion>
</AccordionGroup>

## Lineage

<AccordionGroup>
  <Accordion title="How does Prizm build lineage for SQL Server?">
    Prizm derives lineage from three sources without requiring query log parsing:

    * **Table-to-table** — from `sys.foreign_keys` and `sys.foreign_key_columns`, which give direct parent → child table edges.
    * **View lineage** — from `sys.dm_sql_referenced_entities`, which returns every table and column referenced by a view without parsing the view definition.
    * **Stored procedure lineage** — from the same DMV: `sys.dm_sql_referenced_entities('schema.proc_name', 'OBJECT')` maps every table, view, and column referenced inside the procedure body.
  </Accordion>

  <Accordion title="Does Prizm support cross-database lineage?">
    Yes, within the same SQL Server instance. `sys.synonyms` captures aliases that point to objects in other databases or servers on the same instance. Cross-server lineage (linked servers) is not currently supported.
  </Accordion>
</AccordionGroup>

## Write-Back & Data Safety

<AccordionGroup>
  <Accordion title="Does Prizm write data back to SQL Server?">
    Prizm is read-only by default. No data is written to your SQL Server instance during normal operation. Prizm optionally writes `PRIZM_*` extended properties back to SQL Server objects to record quality metadata — this must be explicitly enabled and requires `ALTER` permissions on the target objects.
  </Accordion>

  <Accordion title="Does Prizm store or copy my SQL Server data?">
    No. Prizm reads metadata and statistical aggregates (counts, min/max, means) — it does not copy row-level data. Profile jobs run SQL queries like `SELECT COUNT(*), COUNT(DISTINCT col), MIN(col)...` and store only the aggregate results.
  </Accordion>
</AccordionGroup>

## Connector Lifecycle

<AccordionGroup>
  <Accordion title="What happens if I delete a connector?">
    Deleting a source triggers an approval workflow based on your organization's settings. Once approved, the source and all associated metadata are permanently deleted. The deletion cannot be undone after approval.
  </Accordion>

  <Accordion title="What happens to existing data and alerts if a connector is paused or disconnected?">
    Existing metadata, quality scores, and alerts are retained while the connector is inactive. No new data is collected until the connector is re-enabled. If the connector is permanently deleted (after approval), all associated data is removed.
  </Accordion>

  <Accordion title="How long does Prizm retain historical data?">
    The default data retention window for time-series observability data, quality score history, and query usage history is **90 days**.
  </Accordion>

  <Accordion title="How does manual re-sync work alongside scheduled jobs?">
    Triggering a manual scan runs the job immediately without affecting the configured schedule. Any schedule update overrides the existing schedule and applies to all future executions.
  </Accordion>
</AccordionGroup>

***

For connection errors, permission issues, and performance problems, contact your Prizm platform administrator. For definitions of terms used in this FAQ, see the [Glossary](/help/glossary).