Tutorial: Create and utilize Microsoft Entra server logins

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (dedicated SQL pools only)

This article guides you through creating and utilizing logins backed by Microsoft Entra ID (formerly Azure Active Directory) within the virtual master database of Azure SQL.

In this tutorial, you learn how to:

Prerequisites

• A SQL Database or SQL Managed Instance with a database. See Quickstart: Create an Azure SQL Database single database if you haven't already created an Azure SQL Database, or Quickstart: Create an Azure SQL Managed Instance.
• Microsoft Entra authentication set up for SQL Database or SQL Managed Instance. For more information, see Configure and manage Microsoft Entra authentication with Azure SQL.
• This article instructs you on creating a Microsoft Entra login and user within the virtual master database. Only a Microsoft Entra admin can create a user within the virtual master database, so we recommend you use the Microsoft Entra admin account when going through this tutorial. A Microsoft Entra principal with the loginmanager role can create a login, but not a user within the virtual master database.

Create Microsoft Entra login

1. Create an Azure SQL Database login for a Microsoft Entra account. In our example, we'll use [email protected] that exists in our Microsoft Entra domain called contoso. A login can also be created from a Microsoft Entra group or service principal (applications). For example, mygroup that is a Microsoft Entra group consisting of Microsoft Entra accounts that are a member of that group. For more information, see CREATE LOGIN (Transact-SQL).

   Note

   The first Microsoft Entra login must be created by the Microsoft Entra admin. The Microsoft Entra admin can be a Microsoft Entra user or group. A SQL login cannot create Microsoft Entra logins.

2. Using SQL Server Management Studio (SSMS), log into your SQL Database with the Microsoft Entra admin account set up for the server.

3. Run the following query:

   Use master
   CREATE LOGIN [[email protected]] FROM EXTERNAL PROVIDER
   GO
   

4. Check the created login in sys.server_principals. Execute the following query:

   SELECT name, type_desc, type, is_disabled
   FROM sys.server_principals
   WHERE type_desc like 'external%'
   

   You would see a similar output to the following:

   Name                            type_desc       type   is_disabled
   [email protected]                 EXTERNAL_LOGIN  E      0
   

5. The login [email protected] has been created in the virtual master database.

Create user from a Microsoft Entra login

1. Now that we've created a Microsoft Entra login, we can create a database-level Microsoft Entra user that is mapped to the Microsoft Entra login in the virtual master database. We'll continue to use our example, [email protected] to create a user in the virtual master database, as we want to demonstrate adding the user to special roles. Only a Microsoft Entra admin or SQL server admin can create users in the virtual master database.

2. We're using the virtual master database, but you can switch to a database of your choice if you want to create users in other databases. Run the following query.

   Use master
   CREATE USER [[email protected]] FROM LOGIN [[email protected]]
   

   Tip

   Although it is not required to use Microsoft Entra user aliases (for example, [email protected]), it is a recommended best practice to use the same alias for Microsoft Entra users and Microsoft Entra logins.

3. Check the created user in sys.database_principals. Execute the following query:

   SELECT name, type_desc, type
   FROM sys.database_principals
   WHERE type_desc like 'external%'
   

   You would see a similar output to the following:

   Name                            type_desc       type
   [email protected]                 EXTERNAL_USER   E
   

Grant server-level roles to Microsoft Entra logins

You can add logins to the fixed server-level roles, such as the ##MS_DefinitionReader##, ##MS_ServerStateReader##, or ##MS_ServerStateManager## role.

ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [AzureAD_object];

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [AzureAD_object];

ALTER SERVER ROLE ##MS_ServerStateManager## ADD MEMBER [AzureAD_object];

Permissions aren't effective until the user reconnects. Flush the DBCC cache as well:

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

To check which Microsoft Entra logins are part of server-level roles, run the following query:

SELECT roles.principal_id AS RolePID,roles.name AS RolePName,
       server_role_members.member_principal_id AS MemberPID, members.name AS MemberPName
       FROM sys.server_role_members AS server_role_members
       INNER JOIN sys.server_principals AS roles
       ON server_role_members.role_principal_id = roles.principal_id
       INNER JOIN sys.server_principals AS members
       ON server_role_members.member_principal_id = members.principal_id;

Grant special roles for Microsoft Entra users

Special roles for SQL Database can be assigned to users in the virtual master database.

In order to grant one of the special database roles to a user, the user must exist in the virtual master database.

To add a user to a role, you can run the following query:

ALTER ROLE [dbmanager] ADD MEMBER [AzureAD_object]

To remove a user from a role, run the following query:

ALTER ROLE [dbmanager] DROP MEMBER [AzureAD_object]

AzureAD_object can be a Microsoft Entra user, group, or service principal in Microsoft Entra ID.

In our example, we created the user [email protected]. Let's give the user the dbmanager and loginmanager roles.

1. Run the following query:

   ALTER ROLE [dbmanager] ADD MEMBER [[email protected]]
   ALTER ROLE [loginmanager] ADD MEMBER [[email protected]]
   

2. Check the database role assignment by running the following query:

   SELECT DP1.name AS DatabaseRoleName,
     isnull (DP2.name, 'No members') AS DatabaseUserName
   FROM sys.database_role_members AS DRM
   RIGHT OUTER JOIN sys.database_principals AS DP1
     ON DRM.role_principal_id = DP1.principal_id
   LEFT OUTER JOIN sys.database_principals AS DP2
     ON DRM.member_principal_id = DP2.principal_id
   WHERE DP1.type = 'R'and DP2.name like 'bob%'
   

   You would see a similar output to the following:

   DatabaseRoleName       DatabaseUserName
   dbmanager              [email protected]
   loginmanager           [email protected]
   

Optional - Disable a login

The ALTER LOGIN (Transact-SQL) DDL syntax can be used to enable or disable a Microsoft Entra login in Azure SQL Database.

ALTER LOGIN [[email protected]] DISABLE

For the DISABLE or ENABLE changes to take immediate effect, the authentication cache and the TokenAndPermUserStore cache must be cleared using the following T-SQL commands:

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

Check that the login has been disabled by executing the following query:

SELECT name, type_desc, type
FROM sys.server_principals
WHERE is_disabled = 1

A use case for this would be to allow read-only on geo-replicas, but deny connection on a primary server.

Related content

• Microsoft Entra server principals
• CREATE LOGIN (Transact-SQL)
• CREATE USER (Transact-SQL)