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

Share via

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

Your answer