Azure DB Server-Roles and Microsoft Entra Groups

1 vote

1 answer

142 views

sql-server security connectivity azure role

                          I am facing an strange issue dealing with Server Roles in my Azure DB environment when using a login derived from AAD Group (Microsoft Entra) for low-privileged admin/deployment activities (such as create DBs, deploy changes in it, etc.).

**IMPORTANT:**
It **does** work when I am using a simple SQL Login and individual AAD Logins but **does not** work with AAD Groups. 

For example if I add **AAD Login** (Group) to **##MS_DatabaseConnector##** role, I am not able to login to a virtual server and getting the following error: 

    Login failed for user 'token-identified principal'. (Framework Microsoft SqlClient Data Provider)

Script I am using to add user/permissions is very trivial:

    CREATE LOGIN [SG-User] FROM  EXTERNAL PROVIDER;
    GO
    ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [SG-User];
    GO

Selecting below give me empty result-set when testing:

    SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 

However, selecting below show me appropriate role membership for a login:

    SELECT member.principal_id AS MemberPrincipalID,
        member.name AS MemberPrincipalName,
        roles.principal_id AS RolePrincipalID,
        roles.name AS RolePrincipalName
    FROM sys.server_role_members AS server_role_members
    INNER JOIN sys.server_principals AS roles
        ON server_role_members.role_principal_id = roles.principal_id
    INNER JOIN sys.server_principals AS member
        ON server_role_members.member_principal_id = member.principal_id
    LEFT JOIN sys.sql_logins AS sql_logins
        ON server_role_members.member_principal_id = sql_logins.principal_id
    WHERE member.principal_id NOT IN (
        -- prevent SQL Logins from interfering with resultset
        SELECT principal_id
        FROM sys.sql_logins AS sql_logins
        WHERE member.principal_id = sql_logins.principal_id
    );

I know there is a possibility to use another way to grant access via users in master DB by adding to specific master db roles but I would like to make it work according to Microsoft recommendation where those new Server Roles is the right way to go for such requirements.

Can someone please point me to the right direction if this is a known issue?
So far I have not been able to locate similar issues in the web.


                        

Asked by Aleksey (11 rep)

Sep 17, 2024, 04:20 AM
Last activity: Sep 27, 2024, 05:13 PM

Azure DB Server-Roles and Microsoft Entra Groups

Related Questions