Not able to enumerate SQL Serverless Synapse database from purview

Question

Not able to enumerate SQL Serverless Synapse database from purview

Parth Kapadia 5

Our purview instance is in a different Subscription (Prod Subscription) and the synapse workspace that we are trying to connect is in a different subscription (Dev Subscription).

We followed this documentation and set up all the permissions (https://learn.microsoft.com/en-us/purview/register-scan-synapse-workspace).
The permissions we gave were:

Reader role to Purview MSI on Dev Synapse workspace
Storage blob data reader role to Purview MSI on Dev Storage account
Created a login:
- CREATE LOGIN [PurviewAccountName] FROM EXTERNAL PROVIDER;

Gave permissions to scan:

  CREATE USER [PurviewAccountName] FOR LOGIN [PurviewAccountName];
  ALTER ROLE db_datareader ADD MEMBER [PurviewAccountName];

  SELECT p.name AS UserName, r.name AS RoleName
  FROM sys.database_principals p
  LEFT JOIN sys.database_role_members rm ON p.principal_id = rm.member_principal_id
  LEFT JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
  WHERE p.authentication_type_desc = 'EXTERNAL'
  ORDER BY p.name;

We don't have any external tables as this query returns empty:

  Select name, credential_identity
  from sys.database_scoped_credentials;

Firewall rule in Synapse already allows traffic from all Azure services.

If I try to run the scan after doing all of the above changes, we are getting an error:

Failed to load serverless databases from Synapse workspace, please give the managed identity of the Purview account permissions to enumerate serverless databases.

Note: We did the same changes on Prod synapse workspace (in Prod subscription) and it is working as expected and we are able to scan the database. I am not sure what we are missing.

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2025-11-03T13:33:27.9733333+00:00

Parth Kapadia Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2025-11-03T13:33:27.9733333+00:00

Parth Kapadia Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Amira Bedhiafi 42,941 MVP Volunteer Moderator

Hello Parth !

Thank you for posting on Microsoft Learn Q&A.

Try to check if you created the LOGIN in master (the serverless endpoint) :

-- master database
CREATE LOGIN [<Purview-MSI-Display-Name>] FROM EXTERNAL PROVIDER;

Purview uses Entra ID and the name must match the Enterprise Application display name of the Purview account’s managed identity.

https://learn.microsoft.com/en-us/purview/register-scan-synapse-workspace

Grant workspace-wide read access that lets Purview enumerate databases/tables:

GRANT CONNECT ANY DATABASE     TO [<Purview-MSI-Display-Name>];
GRANT SELECT ALL USER SECURABLES TO [<Purview-MSI-Display-Name>];

this pair is the recommended pattern for serverless to list/scan databases without object changing rights.

https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/database

and create the USER in each serverless database you intend to scan for each database :

-- run in each target db
CREATE USER [<Purview-MSI-Display-Name>] FOR LOGIN [<Purview-MSI-Display-Name>];
ALTER ROLE db_datareader ADD MEMBER [<Purview-MSI-Display-Name>];

Purview needs a USER mapping per database, not only the LOGIN.

If you have external tables with scoped credentials, grant REFERENCES on each DATABASE SCOPED CREDENTIAL :

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[<credential_name>]
TO [<Purview-MSI-Display-Name>];

Confirm Azure RBAC on the Azure side :

on the Dev Synapse workspace: give the Purview MSI reader
on the Dev ADLS Gen2 that backs serverless data: storage blob data reader

Parth Kapadia 5 Reputation points

2025-11-06T07:35:42.8+00:00

Login exists in master database:
SELECT name, type_desc

FROM sys.server_principals

WHERE name = '<Purview MSI Name>'
returns a row with external user.

We have db_reader on all databases (not master but the ones we want to scan).
We do not have scoped credentials:
SELECT name, type, location, credential_idFROM sys.external_data_sources;
returns credential ID as 0.

SELECT name FROM sys.database_scoped_credentials;
returns empty

I added all the permissions that you mentioned (CONNECT ANY DATABASE and SELECT ALL USER SECURABLES) but still facing the same error
Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2025-11-06T18:31:21.2866667+00:00
Hi Parth Kapadia

Thanks for sharing the details - it looks like you’ve already set up most of the right permissions. Since this setup works fine in your Prod subscription but not in Dev, the issue is likely related to identity or access differences between the two environments.

Here are a few quick things you can double-check:

In your Purview account (Prod), open the Properties tab and note the Managed Identity Object ID. Then check in your Dev Synapse workspace (under master DB → sys.server_principals) that the login name matches exactly.

Purview scans only work against the ondemand endpoint, for example:
yourworkspace-ondemand.sql.azuresynapse.net

Ensure both subscriptions belong to the same Entra tenant, and that the Purview MSI has the Reader role on the Synapse workspace itself, not just the resource group.

Even with “Allow Azure services” enabled, check that the Purview account can reach the Synapse endpoint (especially if private endpoints or region boundaries are involved).

If all of these look fine, try removing and re-registering the Synapse source in Purview and wait 10–15 minutes before triggering a new scan - this often helps refresh the MSI token and metadata.

If it still fails, grab the Activity ID from the Purview scan details and share it with Microsoft Support; they can trace the backend call to confirm what’s blocking the enumeration.

Hope this helps!

Share via

Not able to enumerate SQL Serverless Synapse database from purview

1 answer

Your answer