Edit

Tutorial: Create and utilize Azure Active Directory server logins

  • Article
  • 5 minutes to read

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

Note

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

This article guides you through creating and utilizing Azure Active Directory (Azure AD) principals (logins) in the virtual master database of Azure SQL.

In this tutorial, you learn how to:

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

Prerequisites

Create Azure AD login

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

    Note

    The first Azure AD login must be created by the Azure Active Directory admin. The Azure AD admin can be an Azure AD user or group. A SQL login cannot create Azure AD logins.

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

  3. Run the following query:

    Use master
CREATE LOGIN [bob@contoso.com] 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 
bob@contoso.com                 EXTERNAL_LOGIN  E      0

  5. The login bob@contoso.com has been created in the virtual master database.

Create user from an Azure AD login

  1. Now that we've created an Azure AD login, we can create a database-level Azure AD user that is mapped to the Azure AD 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 an Azure AD 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 [bob@contoso.com] FROM LOGIN [bob@contoso.com]

    Tip

    Although it is not required to use Azure AD user aliases (for example, bob@contoso.com), it is a recommended best practice to use the same alias for Azure AD users and Azure AD 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 an Azure AD user without an Azure AD login is still supported, and requires the creation of a contained user inside SQL Database (without login).

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

Grant server-level roles to Azure AD logins

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

Note

The server-level roles mentioned here are not supported for Azure AD 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 Azure AD 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 Azure AD 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 an Azure AD user, group, or service principal in Azure AD.

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 an Azure AD 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.

See also

For more information and examples, see: