Self-service access workflow – auto-provisioning support for Azure Synapse Dedicated SQL Pool

Hey Pierre-Louis Bourgeois,
thanks for laying out your scenario so clearly. You’re right that today self-service auto-provisioning in Purview only covers ADLS Gen2, Blob storage and Azure SQL DB, and Dedicated SQL Pools (formerly SQL DW) aren’t on that list yet. Here’s a quick summary and some ideas you can try:

1. Roadmap for Synapse Dedicated SQL Pool auto-provisioning • As of now there’s no public ETA for extending Data Policy Enforcement (DPE) auto-provisioning to Dedicated SQL Pools in the Unified Catalog. Microsoft is actively investing in Purview’s policy engine, so I’d recommend: – Watching the Azure Purview “What’s new” page and the Purview feedback portal for roadmap announcements. – Upvoting or commenting on the dedicated SQL pool item in the Purview GitHub/feedback site so it gains priority.
2. Workaround via custom workflow integration While native auto-grant isn’t yet available, you can wire up your own automation “glue” between Purview’s approval workflow and Synapse. A common pattern is: • In Purview, build or modify your self-service workflow to include a webhook or event-hub/Kafka action on approval. • Hook that event into an Azure Function or Logic App (or Power Automate) that:
   1. Parses the approved request (user principal, resource/container/table name, etc.)
   2. Connects to the Dedicated SQL Pool using a service principal or managed identity
   3. Runs a T-SQL script such as:

      
              CREATE USER [******@contoso.com] FROM EXTERNAL PROVIDER;
      
              ALTER ROLE db_datareader ADD MEMBER [******@contoso.com];
      
              GRANT SELECT ON schema.table TO [******@contoso.com];
      
      

   • This gives you fully unattended permission grants as soon as the request is approved.
3. Recommended architecture in the Unified Catalog world Here’s a high-level pattern you could adopt:
   1. Catalog your Synapse workspace + Dedicated SQL Pool in Purview (register it for scanning).
   2. Author a self-service data access workflow in the new Unified Catalog portal and assign it to the collection containing your SQL Pool assets.
   3. In the workflow’s approval step, call out to an Azure Event Hub or webhook.
   4. Build an Azure Function (or Logic App) that: – Is triggered by the Purview event – Uses a managed identity or SPN with “Synapse SQL Admin” rights – Executes the necessary T-SQL GRANT/CREATE USER commands against the pool
   5. Return success/failure back to the workflow (optional) or simply rely on logging/alerts.
   6. Data consumer can then open SSMS or Synapse Studio and immediately see the newly granted access.

Follow-up questions to fine-tune things:

1. Are you using Azure AD (recommended) or SQL authentication on your Dedicated SQL Pool?
2. Do you already have a home for your automation code (Functions, Logic Apps or Power Automate)?
3. How do you plan to authenticate your automation – managed identity, service principal or shared key?
4. Are you comfortable authoring a small Azure Function/Logic App to run T-SQL statements?

Hope that gives you a clear path forward! Let me know if you need more details on any of the steps.

—

Reference docs:

• Data Policy Enforcement overview (supported sources + prerequisites)

https://learn.microsoft.com/purview/how-to-enable-data-policy-enforcement

• Self-service workflows authoring in Purview (hybrid data estates doc)

https://learn.microsoft.com/purview/legacy/how-to-workflow-self-service-data-access-hybrid

• How to register & scan Synapse Dedicated SQL pools in Purview

https://learn.microsoft.com/purview/register-scan-azure-synapse-analytics

• Synapse security & T-SQL permission grants for Dedicated SQL Pool

https://learn.microsoft.com/azure/synapse-analytics/security/how-to-set-up-access-control#step-7b-configure-dedicated-sql-pools