Edit

Directory Readers role in Azure Active Directory for Azure SQL

  • Article
  • 3 minutes to read

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.

Note

With Microsoft Graph support for Azure SQL, the Directory Readers role can be replaced with using lower level permissions. For more information, see User-assigned managed identity in Azure AD for Azure SQL.

When enabling a managed identity for Azure SQL Database, Azure SQL Managed Instance, or Azure Synapse Analytics, the Azure AD Directory Readers role can be assigned to the identity to allow read access to the Microsoft 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 can be used as the server or instance identity to help:

  • 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 Microsoft 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.

Next steps

Tutorial: Assign Directory Readers role to an Azure AD group and manage role assignments