Configuring Microsoft Entra Access for Specific User Groups to Azure SQL Database

Question

Hello,

I require assistance with configuring Microsoft Entra access for specific user groups to my Azure SQL Database setup. My current environment consists of the following:

• A primary Azure SQL server with a primary SQL database replica.
• A secondary Azure SQL server with a read-only geo-replica of the primary database.

My goal is to grant access to specific user groups only to the read-only replica while denying access to the primary replica.

Here are the specifics of my setup and requirements:

From the Azure SQL Server perspective, I do not create login accounts for the Microsoft Entra groups. Instead, users are created in the primary SQL database replica and are automatically copied to the read-only replica. This setup allows users in the specified groups to access both the primary and read-only replicas.

CREATE USER [sqlreader_usergroup] FROM EXTERNAL PROVIDER;

My question is as follows:

Is it possible to deny access (disallow login) for a Microsoft Entra user group to the primary replica, while allowing access only to the read-only replica?

Thank you for your support.

Best regards,

Zakhar Makarevich

Answer 1

Zakhar Makarevich

Thank you for reaching out.You will need to

1. Create an Entra login on the primary and on the geo-secondary servers for your Entra group.
2. Create a user for this login in each database on the primary server and grant this user the necessary permissions in each database.
3. Drop the login on the primary server.

Please be aware that in case of a geo-failover, the Entra group will have access to the new primary.

 For more information about Entra logins, they should see Microsoft Entra server principals - Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn.

Regards,

Oury

Answer 2

Verify if that users from the Entra ID group are created in the primary database, they will be automatically replicated to the read-only replica.

 CREATE USER [sqlreader_usergroup] FROM EXTERNAL PROVIDER;

Assign the necessary read-only permissions to the Entra ID user group in the read-only replica.

 ALTER ROLE db_datareader ADD MEMBER [sqlreader_usergroup];

To deny access to the primary database, you need to deny the CONNECT permission to the Entra ID user group on the primary database. This will prevent users in the group from logging in to the primary database.

 DENY CONNECT TO [sqlreader_usergroup];

Since permissions changes might need to be replicated or consistently applied, consider using automation scripts or Azure Automation to ensure that any new user in the sqlreader_usergroup is automatically denied access to the primary database.