Azure Synapse

Prerequisites for Adding an Azure Synapse Source

Azure Synapse is a Business Intelligence Tool that works with SQL Server, with two options: serverless (on-demand) SQL pool and dedicated SQL pool.

To connect to Azure Synapse, you need existing Microsoft Entra ID or Microsoft SQL Server accounts. These accounts need to have the permissions listed in the following table to access databases, schemas, and tables within your Synapse workspace. For more information, see Synapse Database Objects and Microsoft Azure documentation.

❗️

Credential Permission Requirements

Validio Credentials require VIEWER access rights when connecting to sources to read and access data. Admins must ensure that they do not provide EDITOR access rights to their credentials.

📘

Notes

1. The Storage Blob Data Reader permission is needed on the blob storage services containing external tables. Microsoft SQL Server credentials (login and password) cannot feature or read data from external tables.
2. For authorization with Microsoft Entra ID for Synapse workspaces on a server-less pool, you will be able to view all catalog assets in Lineage (as nodes) and table-view relationships (as edges), but not table-table relationships (as edges). Also, the utilization metrics in Catalog will only show the count of reads, because writes are handled outside of the Synapse (updating the files in the blog store).

Add Azure Synapse Credentials

To monitor Azure Synapse Analytics in Validio, add valid credentials for either Microsoft Entra ID or Microsoft SQL Server. Refer to the following credential parameters tables. For more information, see About Credentials.

👍

Recommendation

For Synapse workspaces on a server-less pool, the Microsoft SQL Server account may not have access to the data. We recommend creating your credential with Microsoft Entra ID authorization.

To add a credential for Azure Synapse,

1. Navigate to Credentials and click + New Credential.
2. Under Namespace, select a namespace where the resources will be created.
3. For Credential Type, select Microsoft Entra ID Credential or Microsoft SQL Server Credential.
4. Fill in the credential parameter fields. Refer to the relevant credential parameters table.
5. Check Use for catalog to automatically discover credentials and add them to the catalog page.
6. Click Create.

Validio will validate the connection to the Microsoft account. If validation passes, Validio will automatically start fetching data. If validation fails, check that you provided the correct parameter values and try again.

Once the credential is created, you can add a source to monitor Azure Synapse data.

Microsoft Entra ID Credential Parameters

Microsoft SQL Server Credential Parameters

Add an Azure Synapse Source

To add a source for Azure Synapse,

1. Navigate to Sources and click + New source.
2. Under Source type, select Azure Synapse.
3. Under Config,
   1. Select the valid Credential or create a new credential to authenticate your connection to the data warehouse.
   2. Enter the Database, Schema, and Table to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the Configuration Parameters table.
   3. Set how many days of Historic data to use when you start the source.
   4. Set the Polling schedule, which is how frequently the validators on the source will check for changes.
4. Under Schema, click Continue to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
5. Under Source details,
   1. Add Tags to help group related sources or to use for routing notifications.
   2. Add an Owner who will be the contact for incident notifications.
6. Click Continue to create the source.
   Source names are generated automatically and will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

Configuration Parameters

Synapse Database Objects

Validio queries specific database objects to populate Catalog and Lineage pages after the source is added.

Catalog:

• sys.databases
• sys.schemas
• sys.objects
• sys.tables
• sys.columns
• sys.extended_properties
• sys.types
• INFORMATION_SCHEMA.TABLES

Lineage:

• sys.dm_exec_requests_history
• sys.dm_pdw_exec_requests
• sys.dm_pdw_exec_sessions