Sysadmin Central

Privileged Identity Management (PIM) in Azure offers a robust solution for managing, controlling, and monitoring access to important resources in your organisation. By configuring PIM Groups to manage access to an Azure SQL Database, you can dramatically enhance your security posture, ensuring that elevated access is granted only when necessary and under tightly controlled conditions.

PIM allows you to ensure that access requests are logged, that access must be approved by a defined group of individuals and that you can confidently review historical access to reduce access where it is no longer be required.

Prerequisites

• Ensure you have the necessary permissions in Entra ID to configure PIM
• An existing Azure SQL Database
• Azure AD Premium P2 Licence

Create a Security Group

For each level of access required within the database, create an Entra ID Security Group. These groups should initially contain no members and should clearly indicate the level of access granted in their titles. A recommended naming convention is:

AZ_DB_Environment_Database_AccessLevel

For example:

AZ_DB_Dev_Storage_Reader

Configure the Group in PIM

Once you have configured the required Security Groups, the next step is to discover these groups so that they can be managed in PIM.

1. Navigate to Privileged Identity Management in Azure.
2. Select Groups in the Manage tab.
3. Select Discovery groups.
4. Search for and select any Groups you wish to manage via PIM, then select Manage Groups.

This process may take several minutes. Once completed, the groups will be available for management in PIM. Navigate back to the Groups page in PIM.

For each group, configure the following:

Settings:

• Set the maximum activation duration.
• Specify if MFA is required for activation.
• Determine if the process requires an approval mechanism and/or justification for access requests.
• Configure the maximum eligible and permanent assignment times.
• It is recommended to set Notifications to Critical emails only to reduce email spam unless otherwise needed.

Assignments:

• Select Add assignments
• Add members who should be able to request the level of access, ensuring that Select role is set to member and that the Assignment type is set to Eligible.

Configure the Group in Azure SQL

Now that the Groups are created and properly integrated with PIM, we need to assign access to those groups in Azure SQL. This configuration will determine the access level granted to users when their requests are approved.

1. Connect to the Azure SQL Instance: Ensure you authenticate using an Azure Entra ID User.
2. Open a new Query: Select the Database you wish to provide access to.
3. Run the SQL Query: Use the following command to create a user from the external provider. Replace [username@domain.com] with your group.
   • CREATE USER [username@domain.com] FROM EXTERNAL PROVIDER;
4. Assign a Role to the Group: Once the group is added, assign a role using the SQL query below. Replace [db_owner] and [username@domain.com] with the appropriate role and group.
   • ALTER ROLE db_owner ADD MEMBER [username@domain.com]

Requesting Access as a User

Now that everything is configured, users with assigned groups will now be able to navigate to Privileged Identity Management > My roles > Groups. From there, they can then select Activate on the relevant group to become a member, thereby gaining access to the database.

---

By following the steps outlined in this post, you can effectively set up PIM Groups for your Azure SQL Database. This process not only enhances security by ensuring that only authorized personnel have access but also streamlines management by automating and controlling access requests. As your organization continues to evolve and grow, leveraging Azure PIM will help maintain a secure and compliant environment, giving you peace of mind and allowing you to focus on your core business objectives.