We are trying to get apps and users off of using SQL accounts to access the Azure SQL DBs where I work. To make our lives easier, we are implementing managed identities.
Benefits of Using Managed Identities and Entra Groups
- Enhanced Security: Using managed identities eliminates the need to manage credentials, reducing the risk of credential theft.
- Simplified Management: Entra Groups streamline the management of permissions for multiple users or managed identities, making it easier to apply consistent access policies.
- Scalability: As your organization grows, you can easily manage access by adding new users or managed identities to Entra Groups without needing to update database permissions individually.
Creating a Managed Identity
Navigate to Managed Identities in Azure and choose Create.
I’m going to create a managed identity named appnamereadwriteid, where appname is your app’s name, so you can more easily audit what your apps are doing in the DBs.
Click Create. It’s that easy to create it. Now, we need to add it to a group.
Adding a Managed ID to an Entra Group
Navigate to Entra ID groups and click New group.
I will setup the group with the following settings.
And add this member, which is the managed identity I just created in the previous section.
Click Create, and now you have a group with that managed identity in it.
Adding the Group to Azure SQL DB
It’s super easy to add it to the DB, as well.
CREATE USER [appreadwritegroup] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [appreadwritegroup];
ALTER ROLE db_datawriter ADD MEMBER [appreadwritegroup];Connecting with this managed identity is happening on the app side. I wouldn’t normally connect to Azure SQL DB with it, but if you need to test it for any reason, you can’t easily connect with Azure Data Studio. However, there appears to be an option in SSMS to connect with Microsoft Entra Managed Identity.
You can have users use their Entra ID if they need to connect. I wouldn’t have them use managed identities to connect to the DB directly, though. If it can be avoided, I’m a big fan of random users not connecting directly to the DB.
The post Using Managed Identities with Azure SQL DB appeared first on sqlkitty.