Azure Purview lineage extraction not working on Azure SQL database

Question

I am able to connect Azure Purview to my Azure SQL Database using SQL credentials for a service account I made for this purpose. The service account has db_owner permissions and a master key exists in the database.

When I attempt to activate lineage extraction, I get an error upon attempting to connect:
'Failed to testConnection: Permission is not sufficient to perform SQL action in database.'

I understand this feature is in preview, but I am hoping that someone has had a similar experience and was able to resolve it.

Answer 1

@Tyler Simpson - Thanks for the question and using MS Q&A platform.

It sounds like the service account you created may not have the necessary permissions to perform the SQL actions required for lineage extraction. Here are a few things you can check:

1. Make sure that the service account has the necessary permissions to read metadata from the database. In addition to the db_owner role, the service account may also need the VIEW DEFINITION permission on the database objects that you want to extract lineage from.
2. Check that the master key is correctly configured in the database. The master key is used to encrypt and decrypt sensitive data, such as credentials, that are stored in the database. If the master key is missing or corrupted, it can cause issues with lineage extraction.
3. Verify that the SQL Server firewall rules are correctly configured to allow traffic from Azure Purview. You can check the firewall rules in the Azure portal or using SQL Server Management Studio.
4. Check that the connection string used by MS Purview is correct and includes the necessary parameters, such as the database name and the service account credentials.

Fore more details, refer to below articles:
https://learn.microsoft.com/en-us/purview/register-scan-azure-sql-database?tabs=sql-authentication
https://learn.microsoft.com/en-us/answers/questions/821168/unable-to-scan-a-azure-sql-database-in-azure-purvi

https://stackoverflow.com/questions/76333462/purview-scan-of-azure-sql-database-not-working-with-lineage-extraction

If you have checked all of these things and are still experiencing issues, please do let us know for further assistance.

Answer 2

I've tried getting lineage extraction working with a scan on an Azure SQL database, but failed. Here's what I've tried.

I've created a managed identity. (I know there should be a Microsoft Purview MSI (system) somewhere, but I can't find it among managed identities in the Azure Portal and therefore can't seem to add access to it.)

az login

az identity create --resource-group myRG --name purview_manident

In the SQL database I created a login and gave it the db_owner role:

CREATE USER purview_manident FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember 'db_datareader', 'purview_manident';

ALTER ROLE db_owner ADD MEMBER purview_manident;

GRANT VIEW DEFINITION TO purview_manident;

I've created a master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somethingSomething+1';

I've added the managed identity to the Purview account. I've created the Purview credential.

I've even followed the advice on the Stack Overflow answer (https://stackoverflow.com/questions/76333462/purview-scan-of-azure-sql-database-not-working-with-lineage-extraction) and changed the tier of the SQL database back and forth, so it would restart.

The scan without Lineage extraction works. No problems there. I'd think the firewall should not be an issue there. Also I've added that the SQL server should be accessible to Azure services.

But still a scan with Lineage extraction, even a test connection runs into the “Permission is not sufficient to perform SQL action in database”.

It seems I've done all the things, so what am I missing here?