1

I have an Azure SQL Server database, which I am trying to scan with Purview. A normal scan works fine, but when I switch on the lineage extraction (to understand dependencies between database objects) the scan fails with the following error message.

I'm aware that in order to perform a lineage extraction, the Purview Managed Identity requires additional permissions, which I have already granted. I have also granted CONTROL permission to the same managed identity. I can't find any reference to this error on the Microsoft website and wondered if anyone in the Stack Overflow community had come across it before and has any ideas on the possible resolution.

The error message seems to indicate a problem with the extended events session, but this is an area of SQL Server in which I don't have a great deal of expertise.

Image shows error message of failed scan on database

Jazza
  • 1,042
  • 1
  • 9
  • 22

1 Answers1

0

Looking at the error it seems like referring to a problem with an event session's startup. You may try executing the following query to verify the event session's state in order to troubleshoot this problem:

SELECT * FROM sys.dm_xe_database_sessions;

All of the event sessions' information, including their status, will be returned in response to this query. You can try beginning the event session with the following command if the state is not "running":

ALTER EVENT SESSION your_event_session_name ON SERVER STATE = START;

substitute the name of the event session you want to start for "your_event_session_name".

Here is my Normal scan on purview:

enter image description here

And to turn on lineage extraction, you must perform the following actions:

  • Give Microsoft Purview access to the db_owner role in the Azure SQL Database MSI
CREATE USER managed_identity_purview FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER managed_identity_purview;
  • Run "create Master Key" in the Azure SQL Database (only if it doesn't exist before).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

Here is my linage Extraction scan on purview after above additional changes:

enter image description here

Pratik Lad
  • 4,343
  • 2
  • 3
  • 11
  • There are some entries in the sys.dm_xe_database_sessions table, but these look like they were from the previous non-lineage extraction enabled scans. Pretty sure I've followed all the pre-requisite steps too, including permissions, but scan still fails when I turn on lineage extraction. – Jazza May 30 '23 at 15:44