Muokkaa

Jaa

Tutorial: Create and utilize Microsoft Entra server logins

  • Artikkeli

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:

  • Create a Microsoft Entra login in the virtual master database with the new syntax extension for Azure SQL Database
  • Create a user mapped to a Microsoft Entra login in the virtual master database
  • Grant server roles to a Microsoft Entra user
  • Disable a Microsoft Entra login

Note

Microsoft Entra server principals (logins) are currently in public preview for Azure SQL Database. Azure SQL Managed Instance can already utilize Microsoft Entra logins.

Prerequisites

Create Microsoft Entra login

  1. Create an Azure SQL Database login for a Microsoft Entra account. In our example, we'll use bob@contoso.com 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. Expand Databases > System Databases. Right-click the master database and selct New Query to open a new query window in the context of the master database.

  4. Run the following query:

    CREATE LOGIN [bob@contoso.com] FROM EXTERNAL PROVIDER
GO

  5. 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
bob@contoso.com                 EXTERNAL_LOGIN  E      0

  6. The login bob@contoso.com 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, bob@contoso.com 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 still using the virtual master database, but you can reconnect to a database of your choice if you want to create users in other databases. Run the following query.

    CREATE USER [bob@contoso.com] FROM LOGIN [bob@contoso.com]

    Tip

    Although it is not required to use Microsoft Entra user aliases (for example, bob@contoso.com), 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
bob@contoso.com                 EXTERNAL_USER   E

Note

The existing syntax to create a Microsoft Entra user without a Microsoft Entra login is still supported. Executing the following syntax creates a database contained user inside the specific database you are connected to. Importantly, this user is not associated to any login, even if a login of the same name exists in the virtual master database.

For example, CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER.

You can create a Microsoft Entra login using a service principal with a nonunique display name. For more information, see Microsoft Entra logins and users with nonunique display names

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 in the master database.

Note

The server-level roles mentioned here are not supported for Microsoft Entra groups.

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 bob@contoso.com. Let's give the user the dbmanager and loginmanager roles.

  1. Run the following query:

    ALTER ROLE [dbmanager] ADD MEMBER [bob@contoso.com]
ALTER ROLE [loginmanager] ADD MEMBER [bob@contoso.com]

  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              bob@contoso.com
loginmanager           bob@contoso.com

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 [bob@contoso.com] 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