Tutoriel : Créer et utiliser des connexions au serveur Microsoft Entra

  • Article

S’applique à :Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (pool SQL dédié uniquement)

Cet article vous guide dans la création et l’utilisation de connexions Microsoft Entra ID (anciennemnt Azure Active Directory) dans la base de données master virtuelle d’Azure SQL.

Dans ce tutoriel, vous allez apprendre à :

  • créer une connexion Microsoft Entra dans la base de données master virtuelle avec la nouvelle extension de syntaxe pour Azure SQL Database
  • créer un utilisateur mappé à une connexion Microsoft Entra dans la base de données master virtuelle
  • Accorder des rôles serveur à un utilisateur Microsoft Entra
  • Désactiver une connexion Microsoft Entra

Remarque

Les principaux de serveur Microsoft Entra (connexion) sont actuellement en préversion publique pour Azure SQL Database. Azure SQL Managed Instance peut déjà utiliser des connexions Microsoft Entra.

Prérequis

Connexion une connexion Microsoft Entra

  1. Créez une connexion Azure SQL Database pour un compte Microsoft Entra. Dans notre exemple, nous allons utiliser bob@contoso.com qui existe dans notre domaine Microsoft Entra nommé contoso. Une connexion peut également être créée à partir d’un groupe Microsoft Entra ou d’un principal de service (applications). Par exemple, mygroup il s’agit d’un groupe Microsoft Entra composé de comptes Microsoft Entra qui sont membres de ce groupe. Pour plus d'informations, consultez CREATE LOGIN (Transact-SQL).

    Remarque

    La première connexion Microsoft Entra doit être créée par l’administrateur Microsoft Entra. L’administrateur Microsoft Entra peut être un utilisateur ou un groupe Microsoft Entra. Une connexion SQL ne peut pas créer de connexions Microsoft Entra.

  2. À l’aide de SQL Server Management Studio (SSMS), connectez-vous à votre SQL Database avec le compte Administrateur Microsoft Entra configuré pour le serveur.

  3. Exécutez la requête suivante :

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

  4. Vérifiez la connexion créée dans sys.server_principals. Exécutez la requête suivante :

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

    Vous verrez un résultat similaire à ce qui suit :

    Name                            type_desc       type   is_disabled 
bob@contoso.com                 EXTERNAL_LOGIN  E      0

  5. La connexion bob@contoso.com a été créée dans la base de données master virtuelle.

Créer un utilisateur test à partir d’une connexion Microsoft Entra

  1. Maintenant que nous avons créé une connexion Microsoft Entra, nous pouvons créer un utilisateur Microsoft Entra au niveau de la base de données mappé à la connexion Microsoft Entra dans la base de données virtuelle master. Nous allons continuer à utiliser notre exemple, bob@contoso.com pour créer un utilisateur dans la base de données master virtuelle, car nous voulons démontrer l’ajout de l’utilisateur à des rôles spéciaux. Seul un administrateur Microsoft Entra ou serveur SQL peut créer des utilisateurs dans la base de données master virtuelle.

  2. Nous utilisons la base de données master virtuelle, mais vous pouvez basculer vers une base de données de votre choix si vous souhaitez créer des utilisateurs dans d’autres bases de données. Exécutez la requête suivante.

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

    Conseil

    Bien qu’il ne soit pas obligatoire d’utiliser des alias d’utilisateur Microsoft Entra (par exemple, bob@contoso.com), il est recommandé d’utiliser le même alias pour les utilisateurs Azure AD et les connexions Microsoft Entra.

  3. Vérifiez l’utilisateur créé dans sys.database_principals. Exécutez la requête suivante :

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

    Vous verrez un résultat similaire à ce qui suit :

    Name                            type_desc       type
bob@contoso.com                 EXTERNAL_USER   E

Remarque

La syntaxe existante permettant de créer un utilisateur Microsoft Entra sans connexion Microsoft Entra est toujours prise en charge. L’exécution de la syntaxe suivante crée un utilisateur contenu dans la base de données spécifique à laquelle vous êtes connecté. Il est important de noter que cet utilisateur n’est associé à aucune connexion, même si une connexion du même nom existe dans la base de données virtuelle master.

Par exemple : CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER.

Accorder des rôles au niveau du serveur aux connexions Microsoft Entra

Vous pouvez ajouter des connexions aux rôles fixes au niveau du serveur, tels que le rôle ##MS_DefinitionReader##, ##MS_ServerStateReader## ou ##MS_ServerStateManager##.

Remarque

Les rôles au niveau du serveur mentionnés ici ne sont pas pris en charge pour les groupes Microsoft Entra.

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];

Les autorisations ne sont pas effectives jusqu’à ce que l’utilisateur se reconnecte. Videz également le cache DBCC :

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

Pour vérifier les connexions Microsoft Entra faisant partie des rôles au niveau du serveur, exécutez la requête suivante :

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;

Accorder des rôles spéciaux pour les utilisateurs de Microsoft Entra

Des rôles spéciaux pour SQL Database peuvent être attribués aux utilisateurs dans la base de données master virtuelle.

Pour accorder l’un des rôles de base de données spéciaux à un utilisateur, celui-ci doit exister dans la base de données master virtuelle.

Pour ajouter un utilisateur à un rôle, vous pouvez exécuter la requête suivante :

ALTER ROLE [dbmanager] ADD MEMBER [AzureAD_object]

Pour supprimer un utilisateur d’un rôle, exécutez la requête suivante :

ALTER ROLE [dbmanager] DROP MEMBER [AzureAD_object]

AzureAD_object peut être un utilisateur, un groupe ou un principal de service Microsoft Entra dans l’ID Microsoft Entra.

Dans notre exemple, nous avons créé l’utilisateur bob@contoso.com. Donnez à l’utilisateur les rôles dbmanager et loginmanager.

  1. Exécutez la requête suivante :

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

  2. Vérifiez l’attribution de rôle de base de données en exécutant la requête suivante :

    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%'

    Vous verrez un résultat similaire à ce qui suit :

    DatabaseRoleName       DatabaseUserName 
dbmanager              bob@contoso.com
loginmanager           bob@contoso.com

Facultatif - désactiver une connexion

La syntaxe DDL ALTER LOGIN (Transact-SQL) peut être utilisée pour activer ou désactiver une connexion Microsoft Entra dans Azure SQL Database.

ALTER LOGIN [bob@contoso.com] DISABLE

Pour que les modifications DISABLE ou ENABLE prennent effet immédiatement, le cache d’authentification et le cache TokenAndPermUserStore doivent être effacés à l’aide des commandes T-SQL suivantes :

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

Vérifiez que la connexion a été désactivée en exécutant la requête suivante :

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

Un cas d’usage est d’autoriser l’accès en lecture seule sur les géo-réplicas, mais de refuser la connexion sur un serveur principal.

Voir aussi

Pour plus d’informations et d’exemples, consultez :