Entra ID logins on Azure SQL Server

[Brucekeeper]

I am connecting a Canvas App to Azure SQL Server using Entra ID authentication and explicit connections (i.e. not just one service account). What are my options to create and remove logins on SQL Server? I am looking for a simple clean way for  the administrator of my PowerApps system to manage her users, without having to go in to SSMS and execute a CREATE USER xxx FROM EXTERNAL PROVIDER command. I was hoping I might be able to issue a SP command from an Admin PowerApp. What is best practice? I would love for her to be able to place users into a Security Group or something similar in the Azure Portal as this is a more familiar interface. Please point me in the right direction...

[VishalJhaveri · Accepted solution]

I got this prompt response. Maybe it can help.

To simplify user management for your Canvas App connecting to Azure SQL Server with Azure AD authentication, you can leverage Azure Active Directory (Azure AD) features for user management. Here's how you can achieve this:

1. Azure AD Groups:
- Create Azure AD groups and add users to these groups in the Azure Portal.
- Grant permissions at the database level to these groups. This way, when a user is added or removed from the group, their permissions in the database are automatically updated.
- For example, you could create groups like "Readers", "Writers", and "Administrators" in Azure AD and grant corresponding permissions in the SQL Server database.

2. Role-Based Access Control (RBAC):
- Utilize RBAC within Azure AD to assign roles to users or groups. Azure SQL Database supports RBAC, allowing you to assign roles like `db_datareader`, `db_datawriter`, or custom roles with specific permissions.
- This approach allows you to manage permissions centrally within Azure AD.

3. Azure Logic Apps or Azure Functions:
- You can create Azure Logic Apps or Azure Functions that respond to events like user additions or updates in Azure AD groups.
- These apps or functions can trigger scripts or stored procedures in your Azure SQL Database to manage user access accordingly.

4. Custom Admin PowerApp:
- Develop a custom PowerApp specifically for administrators to manage users and permissions.
- The app can leverage Microsoft Graph API to interact with Azure AD for user management tasks such as adding or removing users from groups.

5. Azure AD Privileged Identity Management (PIM):
- Utilize Azure AD PIM to manage, control, and monitor access within your organization.
- PIM provides just-in-time privileged access to Azure resources and Azure AD roles.

6. Dynamic Data Masking and Row-Level Security:
- Consider implementing features like Dynamic Data Masking and Row-Level Security within your Azure SQL Database to further enhance data security.

By leveraging these options, you can provide a simplified and user-friendly experience for your PowerApps administrator to manage user access without needing to directly interact with SQL Server Management Studio (SSMS) or execute SQL commands manually. Instead, they can use familiar Azure Portal interfaces or custom PowerApps for user management tasks.

[Brucekeeper]

Wow! Thank you. There's got to be multiple Solutions in there. Thank you.