Microsoft Purview is not able to scan serverless table's fields.

Hello

Welcome to Microsoft QnA !

I understand you are having trouble scanning Synapse Serverless with Purview

There are specific prerequsites to do that !

https://learn.microsoft.com/en-us/azure/purview/register-scan-synapse-workspace?tabs=MI

For Scanning : (https://www.serverlesssql.com/integrating-azure-purview-with-synapse-analytics-serverless-sql-pools/)

Scan Data Source to Discover & Classify Assets

Once we have registered a data source, we can initiate a scan and instruct Purview what to scan. By clicking the New Scan icon on the Synapse data source, we can now enter a name for the scan and select the Serverless SQL Pools databases we would like to scan. However, we are instructed to add the Managed Identity of the Purview account to the Synapse Analytics workspace.

To allow Purview to connect to and scan the Serverless SQL Pools databases, we must do the following. Please note that the instructions below are taken from Microsoft’s official documentation available here. The steps below are relevant to Serverless SQL Pools.

Add Purview Account to the Synapse Workspace

• Make a note of the Managed Identify, in the case of this particular account it is called dhpurviewone
• In the Azure portal https://portal.azure.com, go to the relevant Azure Synapse workspace resource you added to Purview.
• On the left pane, select Access Control (IAM).
• Select the Add button.
• Set the Reader role and enter your Azure Purview account name (the Managed Identity name)
• Select Save

Add Purview Account as a Storage Blob Data Reader

Purview must have access to any storage accounts that contain data that Serverless SQL Pools references.

• In the Azure portal, go to the Subscription that the Azure Synapse workspace is in. You can also go to Resource Groups to localise permissions, but in this blog post we are assigning permissions at the subscription level.
• On the left pane, select Access Control (IAM).
• Select the Add button.
• Select the Storage blob data reader role and enter your Azure Purview account name (the Managed Identity name).
• Select Save

Create Login for Purview Account

• Go to your Azure Synapse workspace and open the Synapse Studio.
• Select the Data tab on the left menu.
• Create a new SQL Script and run the command below on the master database, change the LOGIN name to the appropriate Purview account you created.

CREATE LOGIN [dhpurviewone] FROM EXTERNAL PROVIDER;

Add Purview Account as User to Databases

We must now add the Purview Login as a User to each database we wish to be scanned. To do this:

• Go to your Azure Synapse workspace and open the Synapse Studio.
• Select the Data tab on the left menu.
• Create a new SQL Script and run the command below on any appropriate database

CREATE USER [dhpurviewone] FOR LOGIN [dhpurviewone];

ALTER ROLE db_datareader ADD MEMBER [dhpurviewone]; 

If there are a number of databases to scan then use the following script to enumerate over each database to assign permissions:

CREATE TABLE #Databases (DatabaseRowNumber INT, DatabaseName VARCHAR(255))

INSERT INTO #Databases
SELECT row_number() OVER (ORDER BY database_id), [name] 
FROM sys.databases 
WHERE [name] NOT IN ('master','default')
AND is_auto_create_stats_on = 1
ORDER BY database_id

DECLARE @mindatabase INT,
        @maxdatabase INT,
        @sqltext NVARCHAR(4000)

SELECT @mindatabase = MIN(DatabaseRowNumber) FROM #Databases
SELECT @maxdatabase = MAX(DatabaseRowNumber) FROM #Databases

WHILE @mindatabase <= @maxdatabase
BEGIN
    SET @sqltext = '; CREATE USER [dhpurviewone] FOR LOGIN [dhpurviewone]; ALTER ROLE db_datareader ADD MEMBER [dhpurviewone];'

    SELECT @sqltext = CONCAT('USE ',DatabaseName,@sqltext)
    FROM #Databases
    WHERE DatabaseRowNumber = @mindatabase

    PRINT @sqltext

    EXEC sp_executesql @tsl = @sqltext

    SET @mindatabase = @mindatabase +1
END;

DROP TABLE #Databases

Once permissions have been set, we should now be able to select the databases we wish to scan. In this example we have a database called sqldatapurviewtest which contains several Views and an External Table that references an Azure Data Lake Gen2 storage account.

We’ll then click Continue, accept the default scan rule set set, the scan trigger to Once, then finally click Save and Run. Once the scan is running we can click the View Details link on the Synapse Analytics data source in the Data Map view.

Show Results of Scan

Once the scan has completed we can see a results page showing us the success/failure status of scans, how many scans have been initiated, how many assets (eg External Tables and Views) have been scanned, and how many of those assets have been automatically classified.

Serverless SQL Pools Data Processed

As Purview is scanning Serverless SQL Pools databases for objects to register, it will incur data processed charges so please be aware of this. All queries have a minimum of 10MB data processed. In our example, we have a total of 94MB data processed.

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards