Directory Readers role in Azure Active Directory for Azure SQL
APPLIES TO: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics
Azure Active Directory (Azure AD) has introduced using Azure AD groups to manage role assignments. This allows for Azure AD roles to be assigned to groups.
When enabling a managed identity for Azure SQL Database, Azure SQL Managed Instance, or Azure Synapse Analytics, the Azure AD Directory Readers role must be assigned to the identity to allow read access to the Azure AD Graph API. The managed identity of SQL Database and Azure Synapse is referred to as the server identity. The managed identity of SQL Managed Instance is referred to as the managed instance identity, and is automatically assigned when the instance is created. For more information on assigning a server identity to SQL Database or Azure Synapse, see Enable service principals to create Azure AD users.
The Directory Readers role is necessary to:
- Create Azure AD logins for SQL Managed Instance
- Impersonate Azure AD users in Azure SQL
- Migrate SQL Server users that use Windows authentication to SQL Managed Instance with Azure AD authentication (using the ALTER USER (Transact-SQL) command)
- Change the Azure AD admin for SQL Managed Instance
- Allow service principals (Applications) to create Azure AD users in Azure SQL
Assigning the Directory Readers role
In order to assign the Directory Readers role to an identity, a user with Global Administrator or Privileged Role Administrator permissions is needed. Users who often manage or deploy SQL Database, SQL Managed Instance, or Azure Synapse may not have access to these highly privileged roles. This can often cause complications for users that create unplanned Azure SQL resources, or need help from highly privileged role members that are often inaccessible in large organizations.
For SQL Managed Instance, the Directory Readers role must be assigned to managed instance identity before you can set up an Azure AD admin for the managed instance.
Assigning the Directory Readers role to the server identity isn't required for SQL Database or Azure Synapse when setting up an Azure AD admin for the logical server. However, to enable an Azure AD object creation in SQL Database or Azure Synapse on behalf of an Azure AD application, the Directory Readers role is required. If the role isn't assigned to the SQL logical server identity, creating Azure AD users in Azure SQL will fail. For more information, see Azure Active Directory service principal with Azure SQL.
Granting the Directory Readers role to an Azure AD group
You can now have a Global Administrator or Privileged Role Administrator create an Azure AD group and assign the Directory Readers permission to the group. This will allow access to the Azure AD Graph API for members of this group. In addition, Azure AD users who are owners of this group are allowed to assign new members for this group, including identities of the Azure SQL logical servers.
This solution still requires a high privilege user (Global Administrator or Privileged Role Administrator) to create a group and assign users as a one time activity, but the Azure AD group owners will be able to assign additional members going forward. This eliminates the need to involve a high privilege user in the future to configure all SQL Databases, SQL Managed Instances, or Azure Synapse servers in their Azure AD tenant.