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

Question

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.

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?

Answer 1

Hi Marcel-Jan Krijgsman
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Issue: 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.

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?

Solution: The customer found a solution to the issue. Initially, they attempted to create a user using the command:

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

However, this approach did not work. Upon further investigation, they noticed a Purview-related managed identity appearing in access control pages, such as the Access Control (IAM) page for the SQL server. Instead of using the originally attempted identity, they identified the actual Microsoft Purview account name and created a user based on that managed identity with appropriate database roles:

CREATE USER [DIKWIntelligence] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_owner', [DIKWIntelligence];
EXEC sp_addrolemember 'db_datareader', [DIKWIntelligence];
EXEC sp_addrolemember 'db_datawriter', [DIKWIntelligence];

After assigning these roles, the test connection was successful, and they were able to profile data in the database successfully.

Final Solution: To resolve this issue, create a user in the database using the external provider and ensure it matches the Microsoft Purview account name. This grants the necessary permissions for the connection to work as expected.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members

Answer 2

Hold on. I didn't have time to test everything yet.

But I think I've got the solution. So earlier I showed that this command doesn't work:

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

But it got me thinking, because I saw a Purview account in some access control pages, like the Access Control (IAM) page for the SQL server I saw another Purview related managed identity pop up:

This is the actual name of our Purview account:

So I created a user based on that managed identity and gave it a liberal amount of roles:

CREATE USER [DIKWIntelligence] FROM EXTERNAL ``PROVIDER;

EXEC``sp_addrolemember`` ``'db_owner'``,[DIKWIntelligence]

EXEC``sp_addrolemember`` ``'db_datareader'``,[DIKWIntelligence]

EXEC``sp_addrolemember`` ``'db_datawriter'``,[DIKWIntelligence]

And wouldn't you know it? The test connection suddenly is successful.

So now I profiled a small table (couple of hundred rows). About 5 minutes I got DQ results:

So the solution: create user from external provider in the database with the same name as the Microsoft Purview Account.

The other route I was pursuing, using my own managed identity, will have less priority now.