Purview data quality profiling - how to give access to SQL db?

Question

Purview data quality profiling - how to give access to SQL db?

Marcel-Jan Krijgsman 25

In Purview I've added an Azure SQL database as a data source. And I've done a scan of the database and I have tables now as assets. Now I want to start doing data quality checks on these assets. So I've created a governance domain and a data product in that governance domain. And now I need to give access to the SQL database.

CleanShot 2025-03-12 at 10.39.22

I have a perfectly working managed identity with database access that I have used for the scan of the data source, but it doesn't appear here in the list of credentials.

On the other hand, I don't find this Microsoft Purview MSI (system) identity in the list of identities to give access to on the database. I also don't see it in the list of managed identities in the Azure Portal. Also I don't know how to grant it any access in SQL, because I don't know its username.

Either route is fine with me: either add my managed identity as an option to give access. Or give the system identity access in the database. How can I do this?

Venkat Reddy Navari 95 Reputation points Microsoft External Staff

2025-03-12T12:45:24.9033333+00:00

Hi @Marcel-Jan Krijgsman
Based on the screenshot you provided, encountering a 502 Bad Gateway or Login failed (error code 18456) typically indicates an authentication issue.
To resolve this, start by verifying your username and password for SQL Server or Managed Identity. If you're using Azure AD, ensure you have the correct permissions. Then check that the ODBC Driver 18 is properly configured with all necessary connection details.

I have a perfectly working managed identity with database access that I have used for the scan of the data source, but it doesn't appear here in the list of credentials.

To grant access to your Managed Identity, go to the Azure Portal and locating the resource (such as a Virtual Machine or App Service) that has the Managed Identity enabled. In the Identity section, you can copy the Object ID.
Then open SQL Server Management Studio (SSMS) and connect to your SQL database using an Azure AD admin account. Once connected, run the following SQL command to create a user for your Managed Identity, using the Object ID you copied:

CREATE USER [<YourManagedIdentityName>] FROM EXTERNAL PROVIDER;

Replace <YourManagedIdentityName> with the copied Object ID. After creating the user, grant the necessary permissions. For read-only access, use the command:

ALTER ROLE db_datareader ADD MEMBER [<YourManagedIdentityName>];

If you need read/write access, use:

ALTER ROLE db_datawriter ADD MEMBER [<YourManagedIdentityName>];

Then verify the access by checking if the Managed Identity can read or change data in the database, ensuring everything is set up correctly.

On the other hand, I don't find this Microsoft Purview MSI (system) identity in the list of identities to give access to on the database. I also don't see it in the list of managed identities in the Azure Portal. Also, I don't know how to grant it any access in SQL, because I don't know its username.

To grant access to Microsoft Purview, go to your Microsoft Purview instance in the Azure Portal and ensure the System-assigned Managed Identity is enabled.
Then, open SQL Server Management Studio (SSMS) and connect to your SQL database using an Azure AD admin account. Run the following command to create a user for the Purview managed identity:

CREATE USER [purview-system-identity] FROM EXTERNAL PROVIDER;

Grant the necessary permissions.
For read-only access, run:

ALTER ROLE db_datareader ADD MEMBER [purview-system-identity];
For read/write access, run:

ALTER ROLE db_datawriter ADD MEMBER [purview-system-identity];

Verify that Purview can scan and interact with the SQL database correctly, ensuring it has the necessary access.

For more information refer to Microsoft's documentation: https://docs.azure.cn/en-us/entra/identity/managed-identities-azure-resources/how-to-view-managed-identity-activity#view-updates-made-to-user-assigned-managed-identities

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

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Venkat Reddy Navari 95 Reputation points Microsoft External Staff

2025-03-13T09:57:14.82+00:00

@Marcel-Jan Krijgsman
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.
Marcel-Jan Krijgsman 25 Reputation points

2025-03-14T10:12:39.33+00:00

Hi @Venkat Reddy Navari ,

Thanks for your extensive answer. I only had time to try it this morning.

I went through your advice point by point.

Data quality profiling using my managed identity

To resolve this, start by verifying your username and password for SQL Server or Managed Identity. If you're using Azure AD, ensure you have the correct permissions. Then check that the ODBC Driver 18 is properly configured with all necessary connection details.

So I've got a working connection from Data Map in Purview with the managed identity I created myself. The data source scan was able to find tables and columns. And when I create the connection under Data Quality I am able to choose a Data Source "From Data Map". There could still be permission issues, but I'm going to assume the ODBC driver and connection are alright.

To grant access to your Managed Identity, go to the Azure Portal and locating the resource (such as a Virtual Machine or App Service) that has the Managed Identity enabled.

So for this I go to the SQL server, Security and Identity. The system assigned managed identity was already enabled. I had also already added my own managed identity to this (as I also use it in Data Map).

I quickly tried what would happen if I would remove my own managed identity. Maybe the system managed identity would then be able to have access? It did not. So I reinstated my own managed identity as primary identity again.

In the Identity section, you can copy the Object ID.

I don't see an Object ID anywhere in the Identity section. But if I go in the Portal to Managed Identities I can find an Object ID.

Since I'm on a Mac SSMS doesn't really work, I think. But I tried creating a user this way in DBeaver:

CREATE USER [5xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxe] FROM EXTERNAL PROVIDER;

Which gives an error:

SQL Error [33130] [S0001]: Principal '5xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxe' could not be found or this principal type is not supported.

And actually, I already had more success with connecting my managed identity with just its username.

CREATE USER``purview_manident``FROM EXTERNAL ``PROVIDER;

Remember, I already got the data source scanning working this way.

Adding extra permissions is no problem:

ALTER ROLEdb_datareaderADD MEMBER [purview_manident];

ALTER ROLEdb_datawriterADD MEMBER [purview_manident];

I've checked that this user works in a connection from DBeaver also.

But my managed identity will not appear in the list of credentials in Purview for Data Quality connections:

For my managed identity, this is the main problem. If there's some way to add my managed identity to a list of credentials for data quality profiling, that would be great. I see no such option for this. (Only under Data Map, but it won't pick up my credential from there.)

Data quality profiling using the system managed identity

But we still have this option.

To grant access to Microsoft Purview, go to your Microsoft Purview instance in the Azure Portal and ensure the System-assigned Managed Identity is enabled.

As we've seen above, this is already done.

Then, open SQL Server Management Studio (SSMS) and connect to your SQL database using an Azure AD admin account. Run the following command to create a user for the Purview managed identity: CREATE USER [purview-system-identity] FROM EXTERNAL PROVIDER;

I've done this from DBeaver. I get this error:

SQL Error [33130] [S0001]: Principal 'purview-system-identity' could not be found or this principal type is not supported.

I've tried removing my user managed identity, so it wouldn't be the principal identity anymore. The CREATE USER command still fails.

So unfortunately it still isn't working.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Marcel-Jan Krijgsman 25 Reputation points

2025-03-17T10:40:27.97+00:00

@Venkat Reddy Navari I've responded last Friday with a lot of details. I really hope there's a solution for this issue. So if you have some time, check out my response.
Venkat Reddy Navari 95 Reputation points Microsoft External Staff

2025-03-18T11:27:25.1466667+00:00
@Marcel-Jan Krijgsman Apologies for the earlier follow-up. I hadn't seen your response initially, but after a couple of refreshes, I can now see it.

Thanks for providing the detailed update. It seems like you're encountering two main issues:

Managed Identity not appearing for Data Quality in Purview

Error when trying to create the system-managed Purview identity in the database.

Let me break down a few thoughts based on your observations:

Managed Identity Not Appearing in Purview Data Quality

It seems that you're able to use your managed identity successfully for the Data Map, but it doesn't show up in the list of credentials for Data Quality profiling. This issue typically happens because the credential list in Purview Data Quality is separate from the Data Map connections, even though they use the same identity.

If you're not seeing your managed identity as an option in the Data Quality settings, you may need to explicitly add it there. Since you mentioned that you've already connected the identity successfully for the data source scan, I’d recommend checking these additional steps:

Verify the permissions: Make sure your managed identity has the necessary permissions on both the Purview and SQL Server databases. You might need to ensure that the role db_datareader (or db_datawriter if needed) is granted not just in the SQL database, but also in Purview.

Data Quality Connection: Check if there's any specific configuration that requires re-authentication or refreshing the credentials for the Data Quality connection. You could try re-adding the data source connection to see if that triggers the system to pick up the correct managed identity.

Use Purview Data Quality via API: If the UI is limiting your ability to select the managed identity, using the Azure Purview API to configure the data quality checks could provide a workaround.

Error Creating System Managed Identity User in SQL Database

It seems like you're encountering the error when trying to create the Purview system-managed identity in the SQL database with the command:

CREATE USER [purview-system-identity] FROM EXTERNAL PROVIDER;

The error Principal 'purview-system-identity' could not be found, or this principal type is not supported typically indicates that the Purview system-managed identity is not being recognized by the SQL database, which can happen due to:

Identity not being recognized by SQL Server: This can occur if the system-assigned Purview identity hasn’t been properly synced with your SQL Server instance. Ensure that the System-Assigned Managed Identity is enabled for your Purview account in the Azure portal and that it has been granted necessary permissions (like db_datareader).

Azure AD Permissions: If you’re using Azure Active Directory for authentication, ensure that the system-assigned identity has the correct Azure AD permissions to authenticate against the SQL Server. This can be tricky as sometimes the system-managed identity doesn’t automatically get registered with the SQL Server.

Try the SQL Server Admin Account: It looks like you're using DBeaver instead of SSMS on Mac, but I would suggest temporarily trying to use an Azure SQL Server admin account to perform this operation, as it might give you more detailed error messages or permissions.

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

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Your answer

Venkat Reddy Navari 95 Reputation points Microsoft External Staff

2025-03-13T09:57:14.82+00:00

@Marcel-Jan Krijgsman
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.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Marcel-Jan Krijgsman 25 Reputation points

2025-03-17T10:40:27.97+00:00

@Venkat Reddy Navari I've responded last Friday with a lot of details. I really hope there's a solution for this issue. So if you have some time, check out my response.

Share via

Purview data quality profiling - how to give access to SQL db?

Data quality profiling using my managed identity

Data quality profiling using the system managed identity

Your answer