Create Database-Scoped Users for Microsoft Entra Security Groups

Overview

In modern cloud environments, managing access to databases at scale can be challenging. By integrating Azure SQL with Microsoft Entra (formerly Azure Active Directory) Security Groups, you can streamline access control while ensuring secure and efficient database operations.

This article outlines the step-by-step process to create database-scoped users tied to Microsoft Entra Security Groups.

We will cover the following:

• Creating Microsoft Entra Security Groups: db_readers and db_writers for DEV, QA, and PRD environments.
• Provisioning Azure SQL Server and Databases: Using PowerShell to set up the infrastructure.
• Creating Users and Assigning Permissions: Using T-SQL to grant appropriate access to each database.

1. Create Microsoft Entra Security Groups

1. Navigate to the Azure Portal.
2. Go to Azure Active Directory > Groups.
3. Create the following groups (of type Security) for each environment:
   • db_dev_readers
   • db_dev_writers
   • db_qa_readers
   • db_qa_writers
   • db_prd_readers
   • db_prd_writers
4. Assign the required team members to their respective groups based on the environment and their roles. Please note that, in the real world, the higher the environment, the lower would be the number of people having access. For example, developers would have read &write permissions in DEV but only read permissions in higher environments.

2. Create an Azure SQL Server and Databases Using Azure Portal

1. Create a SQL Server with Microsoft Entra Enabled and make yourself the Administrator. You can also configure yourself as an Administrator after the SQL Server is created.
2. Create Azure SQL Database
3. In the Firewall rules, add your IP Address to connect using SSMS, as explained in the next section.

3. Create Users and Assign Permissions Using T-SQL

1. Connect to your Azure SQL Server using a tool like Azure Data Studio or SQL Server Management Studio (SSMS) with Microsoft Entra MFA.
2. Execute the following T-SQL commands in Database1:

-- Create database-scoped user for Entra Security Group

CREATE USER [db_dev_readers] FROM EXTERNAL PROVIDER;
CREATE USER [db_dev_writers] FROM EXTERNAL PROVIDER;

-- Assign permissions

ALTER ROLE db_datareader ADD MEMBER [db_dev_readers];
ALTER ROLE db_datawriter ADD MEMBER [db_dev_writers];

Once you run the above scripts, you can view the Microsoft Entra groups created as users within the Database, as shown below (screenshot taken from Azure Data Studio)

Henceforth, whenever you get a request to add new users, you can just add them to the Microsoft Entra as members of the Security Groups instead of adding them separately in the database.

Summary

Integrating Azure SQL with Microsoft Entra Security Groups provides a scalable and secure solution for managing database access. In the article, you learned how to:

• Centralize user management through Microsoft Entra Security Groups.
• Automate the provisioning of Azure SQL Server and databases.
• Simplify user access control with T-SQL commands.

This approach reduces operational overhead, enhances security, and ensures consistent role assignments across environments.