Share via

Does Azure Synapse Dedicated SQL Pools support group-based authentication?

Vivek Komarla Bhaskar 936 Reputation points
2025-04-23T11:26:48.31+00:00

Requirement:

Grant read-only (or specific) access to all tables in an Azure Synapse Dedicated SQL Pool for a set of users by assigning them to a group in Azure Entra ID.

Execution:

For example, say I've created a group on Entra ID: sg-datahub-synapse-dev-read and I’ve added users like ******@companyname.com to this group and trying to grant this group following permissions on my dedicated sql pool.

--On Master Database
CREATE LOGIN [sg-datahub-synapse-dev-read] FROM EXTERNAL PROVIDER;

--On Dedicated Sql Pool
CREATE USER [sg-datahub-synapse-dev-read] FROM LOGIN [sg-datahub-synapse-dev-read];

EXEC sp_addrolemember 'db_datareader', 'sg-datahub-synapse-dev-read';

GRANT VIEW DEFINITION TO [sg-datahub-synapse-dev-read];

After completing the setup, I’m trying to log in to my Azure Synapse Dedicated SQL Pool using the email ******@companyname.com with Microsoft Entra ID - Universal with MFA support as the authentication type. While the same setup works perfectly on Azure SQL Server and Azure SQL Database, the login fails on Synapse Dedicated SQL Pool with an error related to 'Service Principal - login failed'.

Could you please advise how to resolve this issue?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,305 questions
0 comments
Accepted answer
  1. Smaran Thoomu 22,350 Reputation points Microsoft External Staff
    2025-04-24T11:07:21.7233333+00:00

    Hi @Vivek Komarla Bhaskar
    Thanks for the detailed explanation and for sharing your setup steps - that’s really helpful for understanding the issue.

    You're correct in noting that group-based authentication with Azure Entra ID (formerly Azure AD) works smoothly with Azure SQL Database and SQL Server, but Azure Synapse Dedicated SQL Pools have some key limitations in this area.

    1. Azure Synapse Dedicated SQL Pools currently do not support group-based authentication directly in the same way Azure SQL Database does.
    2. While you can create logins and users from external providers, Dedicated SQL Pools require the login to be an individual Entra ID identity, not a group.
    3. This is why you're encountering errors like:
      • "Service Principal - login failed" or
      • "Login failed for user '<token-identified principal>'" These typically appear when group-based access is attempted and the token identity doesn’t resolve as an individual user login within the pool.

    Suggested Workarounds:

    Use Individual Entra ID Users:

    • Grant access to users individually (e.g., CREATE USER [******@domain.com] FROM EXTERNAL PROVIDER).
    • This works reliably and is currently the supported pattern.

    Automate Access Granting via Script:

    • If managing many users, consider scripting the creation of users from a group list in Entra ID using PowerShell or CLI to keep it maintainable.

    Log Feedback / Feature Request:

    • Group-based authentication is a commonly requested feature for Synapse. You can upvote and track progress on Microsoft Feedback or escalate internally if needed.

    If you're using SSMS or Synapse Studio, ensure you’re connecting using Azure Active Directory - Universal with MFA and not a service principal.

    Also confirm that the user has been assigned the proper Synapse RBAC roles at the workspace level in addition to SQL roles.

    Hope this helps. Do let us know if you any further queries.

    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 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 34,941 Reputation points MVP
    2025-04-23T11:58:27.6466667+00:00

    Please try executing the below query on Synapse dedicated pool directly :

    create user [sg-datahub-synapse-dev-read] from external provider;

    EXEC sp_addrolemember 'db_datareader', 'sg-datahub-synapse-dev-read';

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.