Authentification SQL dans Azure Synapse Analytics

  • Article

Azure Synapse Analytics compte deux facteurs de forme SQL qui vous permettent de contrôler la consommation de ressources. Cet article explique comment les deux facteurs de forme contrôlent l’authentification utilisateur.

Pour autoriser l’accès à Synapse SQL, vous pouvez utiliser deux types d’autorisation :

  • Autorisation Microsoft Entra
  • Autorisation SQL

L’autorisation SQL permet aux applications héritées de se connecter à Azure Synapse SQL de façon familière. Toutefois, l’authentification Microsoft Entra vous permet de gérer de façon centralisée l’accès aux ressources Azure Synapse, telles que les pools SQL. Azure Synapse Analytics prend en charge la désactivation de l’authentification locale, telle que l’authentification SQL, pendant et après la création de l’espace de travail. Une fois désactivée, l’authentification locale peut être activée à tout moment par les utilisateurs autorisés. Pour plus d’informations sur l’authentification Microsoft Entra uniquement, consultez Désactivation de l’authentification locale dans Azure Synapse Analytics.

Comptes d’administration

Il existe deux comptes d’administration (Nom d’utilisateur de l’administrateur SQL et Administrateur SQL Active Directory) qui agissent en tant qu’administrateurs. Pour identifier ces comptes d’administrateur pour vos pools SQL, ouvrez le portail Azure et accédez à l’onglet Propriétés de votre espace de travail Synapse.

SQL Server Admins

  • Nom d’utilisateur de l’administrateur SQL

    Lorsque vous créez une instance Azure Synapse Analytics, vous devez nommer une connexion d’administrateur serveur. SQL Server crée ce compte comme une connexion dans la base de données master. Ce compte se connecte à l’aide de l’authentification SQL Server (nom d’utilisateur et mot de passe). Un seul de ces comptes peut exister.

  • Administrateur SQL Active Directory

    Un compte Microsoft Entra individuel ou de groupe de sécurité peut également être configuré en tant qu’administrateur. La configuration d’un administrateur Microsoft Entra est facultative, mais un administrateur Microsoft Entra doit être configuré si vous voulez utiliser les comptes Microsoft Entra pour vous connecter à Synapse SQL.

    • Le compte d’administrateur Microsoft Entra contrôle l’accès aux pools SQL dédiés, tandis que les rôles RBAC Synapse serveurs permettent de contrôler l’accès aux pools serverless, par exemple, avec les rôles Administrateur Synapse et Administrateur Synapse SQL.

Les comptes Nom d’utilisateur de l’administrateur SQL et Administrateur SQL Active Directory présentent les caractéristiques suivantes :

  • Ce sont les seuls comptes pouvant se connecter automatiquement à n’importe quelle base de données SQL sur le serveur. (Pour se connecter à une base de données utilisateur, vous devez disposer d’un compte de propriétaire de la base de données ou d’un compte d’utilisateur dans la base de données utilisateur.)
  • Ces comptes accèdent aux bases de données utilisateur en tant qu’dbo utilisateur et possèdent toutes les autorisations dans les bases de données utilisateur. (Le propriétaire d’une base de données utilisateur accède également à la base de données en tant qu’utilisateur dbo.)
  • Ces comptes n’accèdent pas à la base de données master en tant qu’utilisateur dbo et ils ont des autorisations limitées dans la base de données master.
  • Ces comptes ne sont pas membres du rôle serveur fixe SQL standard sysadmin, qui n’est pas disponible dans SQL Database.
  • Ces comptes peuvent créer, modifier et supprimer des bases de données, des connexions, des utilisateurs dans la base de données master, et des règles de pare-feu IP au niveau du serveur.
  • Ces comptes peuvent ajouter et supprimer des membres aux rôles dbmanager et loginmanager.
  • Ces comptes peuvent afficher la table système sys.sql_logins.

Notes

Si un utilisateur est configuré en tant qu’administrateur Active Directory et Administrateur Synapse, puis supprimé du rôle d’administrateur Active Directory, l’utilisateur perd l’accès aux pools SQL dédiés dans Synapse. Il doit être supprimé, puis ajoutés au rôle Administrateur Synapse pour récupérer l’accès aux pools SQL dédiés.

Pour gérer les utilisateurs ayant accès au pool SQL serverless, vous pouvez utiliser les instructions ci-dessous.

Pour créer une connexion à un pool SQL serverless, utilisez la syntaxe suivante :

CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
-- or
CREATE LOGIN [Mary@domainname.net] FROM EXTERNAL PROVIDER;

Lorsque la connexion existe, vous pouvez créer des utilisateurs dans les bases de données individuelles comprises dans le point de terminaison du pool SQL serverless et accorder les autorisations nécessaires à ces utilisateurs. Pour créer un utilisateur, vous pouvez vous servir de la syntaxe suivante :

CREATE USER Mary FROM LOGIN Mary;
-- or
CREATE USER Mary FROM LOGIN Mary@domainname.net;
-- or
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Dès la connexion et l’utilisateur créés, vous pouvez vous servir de la syntaxe SQL Server standard pour octroyer des droits.

Utilisateurs non administrateurs

En règle générale, les comptes non-administrateurs n’ont pas besoin d’accéder à la base de données master. Créez des utilisateurs de base de données autonome dans le niveau de base de données à l’aide de l’instruction CREATE USER (Transact-SQL).

L’utilisateur peut être un utilisateur de base de données autonome de l’authentification Microsoft Entra (si vous avez configuré votre environnement pour l’authentification Microsoft Entra), un utilisateur de base de données autonome de l’authentification SQL Server ou un utilisateur de l’authentification SQL Server basée sur une connexion d’authentification SQL Server (créé à l’étape précédente).

Pour créer des utilisateurs, connectez-vous à la base de données et exécutez des instructions similaires eux exemples suivants :

CREATE USER Mary FROM LOGIN Mary;
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Initialement, seul l’un des administrateurs ou le propriétaire de la base de données peut créer des utilisateurs. Pour autoriser des utilisateurs supplémentaires à créer des utilisateurs, accordez à l’utilisateur sélectionné l’autorisation ALTER ANY USER , en utilisant l’une des instructions suivantes :

GRANT ALTER ANY USER TO Mary;

Pour donner le contrôle total de la base de données aux utilisateurs supplémentaires, faites-les membres du rôle de base de données fixe db_owner.

Dans Azure SQL Database ou Synapse serverless, utilisez l’instruction ALTER ROLE.

ALTER ROLE db_owner ADD MEMBER Mary;

Dans un pool SQL dédié, utilisez EXEC sp_addrolemember.

EXEC sp_addrolemember 'db_owner', 'Mary';

Notes

La nécessité pour les utilisateurs d’accéder à plusieurs bases de données représente l’un des motifs courants de création d’un utilisateur de base de données reposant sur une connexion serveur. Étant donné que les utilisateurs de base de données autonome constituent des entités individuelles, chaque base de données gère un utilisateur et un mot de passe qui lui sont propres. Cela risque d’entraîner un surcroît de complexité dans la mesure où l’utilisateur doit mémoriser le mot de passe de chacune des bases de données, cette situation pouvant même devenir intenable lorsqu’il devient nécessaire de modifier plusieurs mots de passe pour un grand nombre de bases de données.

Groupes et rôles

Une gestion des accès efficace utilise les autorisations assignées aux groupes et aux rôles plutôt qu’aux utilisateurs individuels.

  • Lorsque vous utilisez l’authentification Microsoft Entra, placez les utilisateurs de Microsoft Entra dans un groupe Microsoft Entra. Créez un utilisateur de base de données autonome pour le groupe. Placez un ou plusieurs utilisateurs de base de données dans un rôle de base de données, puis attribuez des autorisations au rôle de base de données.

  • Lorsque vous utilisez l’authentification SQL Server, créez des utilisateurs de base de données autonome dans la base de données. Placez un ou plusieurs utilisateurs de base de données dans un rôle de base de données, puis attribuez des autorisations au rôle de base de données.

Les rôles de base de données peuvent être les rôles intégrés, tels que db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter et db_denydatareader. db_owner est couramment utilisé pour accorder toutes les autorisations à quelques utilisateurs seulement. Les autres rôles de base de données fixe sont utiles pour obtenir rapidement une base de données simple en développement, mais ne sont pas recommandés pour la plupart des bases de données de production.

Par exemple, le rôle de base de données fixe db_datareader accorde l’accès en lecture à toutes les tables de la base de données, ce qui est généralement plus que le minimum nécessaire.

Il est préférable d’utiliser l’instruction CREATE ROLE pour créer vos propres rôles de base de données définis par l’utilisateur et accorder soigneusement à chaque rôle les autorisations minimales nécessaires aux besoins de l’entreprise. Lorsqu’un utilisateur est membre de plusieurs rôles, toutes les autorisations sont agrégées.

Autorisations

Il existe plus de 100 autorisations qui peuvent être accordées ou refusées individuellement dans la base de données SQL. La plupart de ces autorisations sont imbriquées. Par exemple, l’autorisation UPDATE sur un schéma inclut l’autorisation UPDATE sur chaque table dans ce schéma. Comme dans la plupart des systèmes d’autorisation, le refus d’une autorisation remplace l’octroi.

En raison de la nature imbriquée et du nombre d’autorisations, la plus grande attention est requise pour concevoir un système d’autorisation approprié capable de protéger correctement votre base de données.

Démarrez avec la liste des autorisations sous Autorisations (moteur de base de données) et passez en revue le graphique de taille affiche des autorisations de moteur de base de données.

Considérations et restrictions

Prenez en compte les aspects suivants lors de la gestion des connexions et des utilisateurs dans SQL Database :

  • Vous devez être connecté à la base de données master lors de l’exécution des instructions CREATE/ALTER/DROP DATABASE.
  • L’utilisateur de base de données correspondant à la connexion d’administrateur de serveur ne peut pas être modifié ou supprimé.
  • L’administrateur de serveur est désactivé si l’authentification avec Microsoft Entra uniquement est activée.
  • L’anglais américain est la langue par défaut de la connexion d’administrateur de serveur.
  • Seuls les administrateurs (connexion d’administrateur de serveur ou administrateur Microsoft Entra) et les membres du rôle de base de données dbmanager dans la base de données master sont autorisés à exécuter les instructions CREATE DATABASE et DROP DATABASE.
  • Vous devez être connecté à la base de données master lors de l’exécution des instructions CREATE/ALTER/DROP LOGIN. L’utilisation de connexions est toutefois déconseillée. Préférez l’emploi d’utilisateurs de base de données autonome. Pour plus d’informations, consultez Utilisateurs de base de données autonome - Rendre votre base de données portable.
  • Pour vous connecter à une base de données utilisateur, vous devez renseigner le nom de la base de données dans la chaîne de connexion.
  • Seule la connexion du principal au niveau du serveur et les membres du rôle de base de données loginmanager dans la base de données master sont autorisés à exécuter les instructions CREATE LOGIN, ALTER LOGIN et DROP LOGIN.
  • Lors de l’exécution des instructions CREATE/ALTER/DROP LOGIN et CREATE/ALTER/DROP DATABASE dans une application ADO.NET, l’utilisation de commandes paramétrées est interdite. Pour plus d’informations, voir Commandes et paramètres.
  • Lors de l’exécution de l’instruction CREATE USER avec l’option FOR/FROM LOGIN, elle doit être la seule instruction du batch Transact-SQL.
  • Lors de l’exécution de l’instruction ALTER USER avec l’option WITH LOGIN, elle doit être la seule instruction du batch Transact-SQL.
  • Les instructions CREATE/ALTER/DROP LOGIN et CREATE/ALTER/DROP USER ne sont pas prises en charge lorsque l’authentification avec Microsoft Entra uniquement est activée pour l’espace de travail Azure Synapse.
  • Pour exécuter les instructions CREATE/ALTER/DROP, un utilisateur a besoin de l’autorisation ALTER ANY USER sur la base de données.
  • Lorsque le propriétaire d’un rôle de base de données tente d’ajouter un autre utilisateur de base de données dans ce rôle de base de données (ou de le supprimer de ce dernier), l’erreur suivante peut se produire : L’utilisateur ou le rôle « Nom » n’existe pas dans cette base de données. Cette erreur se produit parce que l’utilisateur n’est pas visible par le propriétaire. Pour résoudre ce problème, accordez au propriétaire du rôle l’autorisation VIEW DEFINITION sur l’utilisateur.

Étapes suivantes

Pour plus d’informations, voir Utilisateurs de base de données autonome - Rendre votre base de données portable.