While scanning data source the stored procedures are not reflecting in purview to generate lineage

Rutuja Kanase 20

As per standard process, I have registered Azure SQL Database data source in Microsoft Purview. While scanning the data source I am not getting list of stored procedures to scan and generate lineage for same.

Could you please assist on same ASAP? Can we catalog stored procedures in purview and generate the lineage?

phemanth 10,555 Reputation points Microsoft Vendor

2024-09-27T12:59:23.0333333+00:00
@Rutuja Kanase

Thanks for reaching out to Microsoft Q&A.:

It is indeed possible to extract lineage for stored procedures in Microsoft Purview. Here are the key points from the documentation:

Cataloging Stored Procedures:

Microsoft Purview supports the cataloging of stored procedures from Azure SQL Database. This includes capturing metadata about the stored procedures.

Lineage Generation:

Purview can generate lineage information for stored procedures, showing how data flows through these procedures and interacts with other data assets.

For more detailed steps and supported capabilities, you can refer to the following documentation:

Catalog Lineage User Guide

Register and Scan Azure SQL Database

If you need further assistance with the setup or have specific questions, feel free to ask!
phemanth 10,555 Reputation points Microsoft Vendor

2024-09-30T09:27:06.4+00:00

@Rutuja Kanase We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rutuja Kanase 20 Reputation points

2024-09-30T13:00:28.5566667+00:00

Hi phemanth, I tried by registering and scanning the Azure SQL database in Purview but while scanning the database, I am not getting the stored procedures to catalog and generate the lineage in Purview. Could you please assist on the issue?
Rutuja Kanase 20 Reputation points

2024-09-30T13:01:03.7133333+00:00

Hi phemanth, As per given steps, I tried by registering and scanning the Azure SQL database in Purview but while scanning the database, I am not getting the stored procedures to catalog and generate the lineage in Purview. Could you please assist on the issue?
phemanth 10,555 Reputation points Microsoft Vendor

2024-10-01T06:20:54.8+00:00
It looks like you’ve followed the necessary steps to register and scan your Azure SQL Database in Microsoft Purview, but you’re still not seeing the stored procedures reflected. Here are a few things to check

Ensure that the Microsoft Purview managed identity has the necessary permissions, such as db_owner, on the Azure SQL Database.

Verify that your Azure SQL Database allows public access or that it allows access from Azure services. Stored procedure lineage extraction is not supported if public access is disabled.

Make sure that lineage extraction is enabled in your scan configuration. This setting is crucial for capturing stored procedures.

Lineage is captured only when stored procedures are executed and transfer data from one table to another. Ensure that the stored procedures have been executed after setting up the scan.

Be aware of certain limitations, such as lineage extraction not being supported for temporary tables, functions, or triggers.

If you’ve checked all these points and are still facing issues, it might be helpful to manually document the stored procedures as part of your data cataloging process or use the Purview REST API to create custom lineage links. This approach requires more manual effort but can help in the interim.

For more detailed guidance, you can refer to the Microsoft Purview documentation.
Rutuja Kanase 20 Reputation points

2024-10-01T07:18:07.33+00:00

Thanks for your quick response. Could you please guide me how to check db_owner permission and public access for database using azure portal?
phemanth 10,555 Reputation points Microsoft Vendor

2024-10-03T09:25:47.41+00:00
@Rutuja Kanase

Sure, Here are the steps to check db_owner permissions and public access for your Azure SQL Database using the Azure portal:

Checking db_owner Permissions

Navigate to the Azure Portal: Go to portal.azure.com and sign in with your credentials.

Select Your SQL Database: In the left-hand menu, select SQL databases and then choose your specific database.

Access Control (IAM): In the database’s menu, select Access control (IAM).

Check Role Assignments: Click on Role assignments to see the list of users and their roles. Look for the db_owner role to ensure it is assigned to the appropriate user or managed identity.

Checking Public Access

Navigate to the Azure Portal: Go to portal.azure.com and sign in with your credentials.

Select Your SQL Server: In the left-hand menu, select SQL servers and then choose your specific server.

Networking: In the server’s menu, select Networking.

Public Network Access: Check the Public network access setting. It should be set to Yes to allow public access. If it is set to No, you need to change it to Yes to enable public access.
Rutuja Kanase 20 Reputation points

2024-10-03T10:16:02.5733333+00:00

Hi, I am having db_owner permission and public access is enabled but we are currently using Azure SQL dedicated Pool (formerly SQL DW) to catalog all tables and views in Synapse.

But still facing same issue. Could you please confirm whether it is possible with Azure SQL dedicated Pool or not?
phemanth 10,555 Reputation points Microsoft Vendor

2024-10-04T03:19:00.5+00:00
@Rutuja Kanase

Thanks for the additional details.

It is possible to catalog stored procedures and generate lineage in Microsoft Purview with Azure SQL dedicated Pool. Microsoft Purview supports the cataloging of stored procedures from Azure SQL Database, including dedicated SQL pools, and can generate lineage information for these procedures.

However, there are some limitations to be aware of:

Lineage extraction is not supported for temporary tables, functions, or triggers.

Lineage is captured only when stored procedures are executed and transfer data from one table to another.

you can check the Microsoft Purview documentation for more detailed guidance on cataloging stored procedures and generating lineage in Azure SQL dedicated Pool.

you can try the following troubleshooting steps:

Verify stored procedure execution: Ensure that the stored procedures have been executed after setting up the scan. Check scan configuration: Make sure that lineage extraction is enabled in your scan configuration. Check for limitations: Be aware of the limitations mentioned above and ensure that your stored procedures do not use temporary tables, functions, or triggers.

If you've checked all these points and are still facing issues, you might need to manually document the stored procedures as part of your data cataloging process or use the Purview REST API to create custom lineage links.

1: Connect to and manage dedicated SQL pools in Microsoft Purview

2: Metadata and lineage from Azure Synapse Analytics

1 answer

youzeliang 735 Reputation points

2024-09-26T05:31:55.53+00:00
In Microsoft Purview, scanning of Azure SQL Database primarily focuses on cataloging tables, views, and columns by default. However, when it comes to stored procedures, Purview does not natively catalog or scan stored procedures directly for lineage as it does with tables and columns. Stored procedures are considered operational logic, and currently, Microsoft Purview’s scanning capabilities do not support them out of the box.

Key Points:

1. Stored Procedure Cataloging: • As of now, Microsoft Purview does not natively support cataloging stored procedures from databases like Azure SQL or generating lineage for them directly. It focuses more on the metadata of tables, views, columns, and files for generating lineage. 2. Lineage Generation: • Purview is designed to generate lineage based on the relationships between datasets, which means it tracks how data flows between tables, files, and other data assets. Since stored procedures

If my answer is helpful to you, you can adopt it, thank you！
Please sign in to rate this answer.
Rutuja Kanase 20 Reputation points

2024-09-26T06:31:31.56+00:00

Hi youzeliang, as per MS documentation it is enable and possible to extract lineage.

https://learn.microsoft.com/en-us/purview/catalog-lineage-user-guide https://learn.microsoft.com/en-us/purview/register-scan-azure-sql-database?tabs=sql-authentication#supported-capabilities

Rutuja Kanase 20 Reputation points

2024-09-27T07:13:00.7266667+00:00

Hi @youzeliang , Could you please assist on same?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

While scanning data source the stored procedures are not reflecting in purview to generate lineage

1 answer

Your answer