Azure Purview Lineage to Azure SQL Database Connection Fail

Question

Azure Purview Lineage to Azure SQL Database Connection Fail

Chito Sto. Domingo 6

I am trying to perform a Azure Purview scan of Azure SQL database. It can connect to the Azure SQL successfully but fails when I enable the Lineage Extraction. I am using Microsoft Purview MSI and the Managed VN IR to connect. The MSI were created as user in the database and made member of the db_owner role. There is already an existing 'master key' in the database so its the only thing I did not do in the setup instructions.

It's not networking, I know that Purview can reach the database because it can scan it without the Lineage option.

Do I need to drop and recreate the 'master key'?

2 answers

Your answer

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

Could you please review the steps to configure authentication for a scan that are documented here?

Have you configured Azure SQL firewall to allow database access to Azure services?

User's image

Finally, please review all the prerequisites for setting up a scan with lineage extraction that are outlined here. After that follow the steps provided on how to perform the scan. Take your time to read this resource.

Chito Sto. Domingo 6 Reputation points

2023-06-08T23:33:31.15+00:00

The Azure SQL networking is configured with private endpoints, public access is disabled. Purview is setup with managed private endpoint and its approved to connect to Azure SQL.

I have done all the prerequisites. Created the purview MSI as a user in the database and granted the role of db_owner. The only thing I did not do is creating a 'master key' because it already exists in the database.

The Purview test can successfully scan the Azure SQL database without the lineage. With Lineage enabled it fails.
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-06-09T02:48:31.34+00:00
Could you please add the following permissions to the identity?

CREATE USER managed_identity_purview FROM EXTERNAL PROVIDER; ALTER ROLE db_owner ADD MEMBER managed_identity_purview;
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-06-09T02:50:04.59+00:00

When the scan fails, what error you receive that you can share with us?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-09T23:48:06.08+00:00

Hello Chito Sto. Domingo,

As Alberto Morillo requested, could you please provide us the error message that you received.
Chito Sto. Domingo 6 Reputation points

2023-06-10T11:55:21.47+00:00

I was just running the test. The prerequisite creating the Purview and MSI user and grant the db_owner (as mentioned) in my question have been done, except the 'create master' because it already existed in the database.

Here's the error appeared in the test connection with Lineage enabled.
Chito Sto. Domingo 6 Reputation points

2023-06-10T12:03:58.5666667+00:00

And here's the a successful test without the Lineage:
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-11T03:03:51.56+00:00

Hello Chito Sto. Domingo,

Can you click on "more info" (in the connection failed screenshot) and provide the error message?

If you have not already configured the portal authentication, please follow this documentation:
https://learn.microsoft.com/en-us/azure/purview/register-scan-azure-sql-database?tabs=managed-identity#configure-portal-authentication

and Configure Azure AD authentication in the database account
Chito Sto. Domingo 6 Reputation points

2023-06-11T04:45:07.0533333+00:00

The 'more info' shows 'Error (5222) Test connection failed' and provide the 'Learn more' link that points to https://learn.microsoft.com/en-au/azure/purview/troubleshoot-connections?wt.mc_id=mspurview_inproduct_learnmoreerrorlinks_troubleshootscanconnection_csadai.

Yes I have granted the Purview MSI with "Reader' RBAC to the Azure SQL service.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-12T21:00:04.1266667+00:00

Hello Chito Sto. Domingo,

Thank you for the details. Sorry, the error message is not comprehensive to troubleshoot the issue based on the error details.

Since you use virtual networks, please check your Network settings and NSG rules.

To determine if this issue is related to Vnet IR, please try to test the connection using the auto integration run time and see if you are getting the same error.

https://learn.microsoft.com/en-us/azure/purview/catalog-managed-vnet
Anonymous

2023-06-13T16:07:10.78+00:00

I have exactly the same issue. Can connect fine when lineage extraction toggle is off, when setting it to on (and giving the ManagedIdentity db_owner & reader in the portal to the Azure SQL DB), the test connection fails with the same output as the OP
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-13T17:23:06.7566667+00:00

Hello Chito Sto. Domingo,

Are you using Vnet IR here? Did you try Auto IR?
Chito Sto. Domingo 6 Reputation points

2023-06-13T23:55:00.63+00:00

Yes. I am using Managed VNET IR to connect to the Azure SQL. I cannot use the Auto IR. The datasource Azure SQL is in privAte endpoint with public access disabled.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-14T00:01:53.2933333+00:00

Got it. Thank you for the details.

Can you please follow the below document and see if you miss anything from your end. Meanwhile, I will test this(using Vnet IR) from my end and get back to you.

https://learn.microsoft.com/en-us/azure/purview/catalog-managed-vnet
Chito Sto. Domingo 6 Reputation points

2023-06-14T00:06:13.48+00:00

I am confident that I configured the Managed Vnet IR using the link you provided. Here's the the Purview Managed endpoint approvals:
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-14T17:19:43.7333333+00:00

Hello Chito Sto. Domingo,

Can you let me know your Purview account region?

And please confirm your managed network is in the same region as the Microsoft Purview account region.
Chito Sto. Domingo 6 Reputation points

2023-06-15T00:10:34.28+00:00

Both Purview and the Azure SQL are in Australia East region.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-15T20:30:42.3966667+00:00

Hello Chito Sto. Domingo,

I was able to reproduce the error from my end. If I turn on Lineage extraction (preview) then the connection fails.

I will reach out to PG and get back to you.

Thanks for your patience.

Answer 2

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hello Chito Sto. Domingo,

I got a confirmation from PG that this is expected behavior.

Extract lineage (preview)

Lineage is not currently supported using a self-hosted integration runtime or managed VNET runtime and a private endpoint. You need to enable Azure services to access the server under network settings for your Azure SQL Database.

Please see the below document.

Discover and govern Azure SQL Database - Microsoft Purview | Microsoft Learn

In this scenario, the lineage extraction for Azure SQL DB is currently available only on the public network as we need to enable "Allow Azure services and resources to access this server". This option exists on the Public network.

In order to use the lineage extraction for Azure SQL DB, we need to enable "Allow Azure services and resources to access this server" from Public access.

I hope this helps. Please let me know if you have any further questions.

User's image

Chito Domingo 0 Reputation points

2023-06-15T22:51:58.7766667+00:00

Is there a plan or development pipeline to have Purview lineage to allow Managed VNET?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-06-16T19:05:38.6266667+00:00

Hello Chito Domingo,

Currently, there is no plan or development pipeline for Purview lineage to allow Managed VNET for Azure SQL DB.

Please submit this feature request in the Purview Ideas forum( Provide your Scenario, Desired Outcome, and Impact )

https://feedback.azure.com/d365community/forum/82d7bddb-fb24-ec11-b6e6-000d3a4f07b8

This is open for the user community to upvote & comment on. This allows product teams to prioritize your idea against our existing feature backlog effectively and gives insight into the potential impact of implementing the suggested feature.

Please let me know if you have any further questions.

Share via

Azure Purview Lineage to Azure SQL Database Connection Fail

2 answers

Your answer