Edit

Directory Readers role in Microsoft Entra ID for Azure SQL

  • Article

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Microsoft Entra ID (formerly Azure Active Directory) has introduced using groups to manage role assignments. This allows for Microsoft Entra 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 Microsoft Entra for Azure SQL.

When enabling a managed identity for Azure SQL Database, Azure SQL Managed Instance, or Azure Synapse Analytics, the Microsoft Entra ID 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 Microsoft Entra users.

The Directory Readers role can be used as the server or instance identity to help:

  • Create Microsoft Entra logins for SQL Managed Instance
  • Impersonate Microsoft Entra users in Azure SQL
  • Migrate SQL Server users that use Windows authentication to SQL Managed Instance with Microsoft Entra authentication (using the ALTER USER (Transact-SQL) command)
  • Change the Microsoft Entra admin for SQL Managed Instance
  • Allow service principals (Applications) to create Microsoft Entra users in Azure SQL

Note

Microsoft Entra ID is the new name for Azure Active Directory (Azure AD). We are updating documentation at this time.

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 the managed instance identity before you can set up a Microsoft Entra 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 a Microsoft Entra admin for the logical server. However, to enable Microsoft Entra object creation in SQL Database or Azure Synapse on behalf of a Microsoft Entra application, the Directory Readers role is required. If the role isn't assigned to the logical server identity, creating Microsoft Entra users in Azure SQL will fail. For more information, see Microsoft Entra service principal with Azure SQL.

Granting the Directory Readers role to a Microsoft Entra group

You can now have a Global Administrator or Privileged Role Administrator create a Microsoft Entra 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, Microsoft Entra users who are owners of this group are allowed to assign new members for this group, including identities of the 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 Microsoft Entra 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 Microsoft Entra tenant.

Next steps

Tutorial: Assign Directory Readers role to a Microsoft Entra group and manage role assignments