Rendre votre base de données portable en utilisant des bases de données autonomes

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Base de données SQL dans Microsoft Fabric

Faites appel à des utilisateurs de base de données autonome pour authentifier les connexions SQL Server et Azure SQL Database au niveau de la base de données. Une base de données autonome est une base de données qui est isolée des autres bases de données et de l'instance SQL Server ou SQL Database (et de la base de données master) qui héberge la base de données.

SQL Server prend en charge les utilisateurs de base de données autonome pour Windows et l’authentification SQL Server. Si vous utilisez SQL Database, associez les utilisateurs de base de données autonome à des règles de pare-feu au niveau de la base de données.

Cet article examine les avantages liés à l'utilisation du modèle de base de données autonome par rapport au modèle traditionnel de connexion/utilisateur et aux règles de pare-feu Windows ou au niveau du serveur. Le recours au modèle traditionnel de connexion/utilisateur et aux règles de pare-feu au niveau du serveur peut encore s'avérer nécessaire pour mettre en œuvre une logique métier d'application, des critères de facilité de gestion ou des scénarios spécifiques.

Connexion traditionnelle et modèle utilisateur

Dans le modèle traditionnel de connexion, les utilisateurs Windows ou les membres des groupes Windows se connectent au moteur de base de données en fournissant des informations d'identification utilisateur ou groupe authentifiées par Windows. Ou les utilisateurs peuvent fournir un nom et un mot de passe et vous connecter par le biais de l’authentification SQL Server. Dans les deux cas, la base de données MASTER doit avoir une connexion qui correspond aux informations d'identification de connexion.

Après que le moteur de base de données a confirmé les informations d'identification de l'authentification Windows ou a authentifié les informations d'identification de l'authentification SQL Server, la connexion tente généralement de se connecter à une base de données utilisateur. Pour vous connecter à une base de données utilisateur, la connexion doit être mappée (autrement dit, associée) à un utilisateur de base de données de la base de données utilisateur. La chaîne de connexion peut également spécifier la connexion à une base de données spécifique, facultative dans SQL Server, mais obligatoire dans SQL Database.

Le principe important est que la connexion (de la base de données master) et l'utilisateur (de la base de données utilisateur) doivent exister et être liés entre eux. La connexion à la base de données utilisateur est dépendante de la connexion dans la base de données master. Cette dépendance limite la capacité de la base de données à être déplacée vers une autre instance SQL Server d’hébergement ou un autre serveur Azure SQL Database.

Si une connexion à la base de données master n’est pas disponible (par exemple, un basculement est en cours), le temps de connexion global augmente ou la connexion peut expirer. Une connexion non disponible peut réduire la scalabilité des connexions.

Modèle utilisateur de base de données autonome

Dans le modèle utilisateur de la base de données autonome, la connexion de la base de données master n'est pas présente. Au lieu de cela, le processus d’authentification se produit sur la base de données utilisateur. L’utilisateur de la base de données dans la base de données utilisateur ne dispose pas d’une connexion associée dans la base de données master.

Le modèle utilisateur de base de données autonome prend en charge l'authentification Windows et l'authentification SQL Server. Vous pouvez l’utiliser à la fois dans SQL Server et SQL Database.

Pour vous connecter en tant qu'utilisateur de base de données autonome, la chaîne de connexion doit toujours contenir un paramètre de la base de données utilisateur. Le moteur de base de données utilise ce paramètre pour savoir quelle base de données est responsable de la gestion du processus d’authentification.

L’activité de l’utilisateur de base de données autonome est limitée à la base de données d’authentification. Le compte d’utilisateur de base de données doit être créé indépendamment dans chaque base de données dont l’utilisateur a besoin. Pour modifier les bases de données, les utilisateurs de SQL Database doivent créer une connexion. Les utilisateurs de base de données autonome dans SQL Server peuvent modifier les bases de données si un même utilisateur est présent dans une autre base de données.

Dans Azure, SQL Database et Azure Synapse Analytics prennent en charge les identités de Microsoft Entra ID (anciennement Azure Active Directory) en tant qu’utilisateurs de base de données autonome. SQL Database prend en charge les utilisateurs de base de données autonomes qui utilisent l’authentification SQL Server, mais Azure Synapse Analytics ne le fait pas. Pour plus d’informations, consultez Connexion à SQL Database avec l’authentification Microsoft Entra.

Lorsque vous utilisez l’authentification Microsoft Entra, les utilisateurs peuvent établir des connexions à partir de SQL Server Management Studio à l’aide de l’authentification universelle Microsoft Entra. Les administrateurs peuvent configurer une authentification universelle pour exiger l’authentification multifacteur, qui vérifie l’identité à l’aide d’un appel téléphonique, d’un SMS, d’une carte intelligente avec code PIN ou d’une notification d’application mobile. Pour plus d’informations, consultez Utilisation de l’authentification multifacteur Microsoft Entra.

Pour SQL Database et Azure Synapse Analytics, le nom de la base de données est toujours requis dans le chaîne de connexion. Aucune modification n'est donc requise sur la chaîne de connexion lorsque vous passez du modèle traditionnel au modèle utilisateur de base de données autonome. Pour les connexions SQL Server, le nom de la base de données doit être ajoutée à la chaîne de connexion, s’il n'est pas déjà présent.

Important

Lorsque vous utilisez le modèle traditionnel, les rôles de niveau serveur et les autorisations de niveau serveur peuvent limiter l'accès à toutes les bases de données. Lorsque vous utilisez le modèle de base de données autonome, les propriétaires et les utilisateurs de base de données ayant l'autorisation ALTER ANY USER peuvent accorder l'accès à la base de données. Cette autorisation réduit le contrôle d'accès des connexions serveur dotées de privilèges élevés et étend le contrôle d'accès pour inclure les utilisateurs de base de données à privilèges élevés.

Pare-feux

SQL Server

Pour SQL Server, les règles de pare-feu Windows s'appliquent à toutes les connexions et ont les mêmes effets sur les connexions (connexions de modèle traditionnel) et les utilisateurs de base de données autonome. Pour plus d'informations sur le pare-feu Windows, consultez Configurer le pare-feu Windows pour accéder au moteur de base de données.

Pare-feu de base de données SQL

SQL Database permet des règles de pare-feu distinctes pour les connexions au niveau serveur (connexions) et pour les connexions au niveau base de données (utilisateurs de base de données autonome). Quand SQL Database se connecte à une base de données utilisateur, elle contrôle en premier les règles de pare-feu au niveau de la base de données. Si aucune règle n'autorise l'accès à la base de données, SQL Database contrôle les règles de pare-feu au niveau serveur. La vérification des règles de pare-feu au niveau du serveur nécessite l’accès à la base de données master du serveur SQL Database.

L'association des règles de pare-feu au niveau de la base de données et des utilisateurs de base de données autonome peut éliminer la nécessité d'accéder à la base de données master du serveur pendant la connexion. Le résultat est une scalabilité de connexion améliorée.

Pour plus d'informations sur les règles de pare-feu de SQL Database, consultez les rubriques suivantes :

Différences de syntaxe

Modèle traditionnel Modèle utilisateur de base de données autonome
Si vous êtes connecté à la base de données master :

CREATE LOGIN login_name WITH PASSWORD = 'strong_password';

Ensuite, quand vous êtes connecté à une base de données utilisateur :

CREATE USER 'user_name' FOR LOGIN 'login_name';		 Quand vous êtes connecté à une base de données utilisateur :

CREATE USER user_name WITH PASSWORD = 'strong_password';
Modèle traditionnel Modèle utilisateur de base de données autonome
Pour modifier un mot de passe dans le contexte de la base de données master :

ALTER LOGIN login_name WITH PASSWORD = 'strong_password';		 Pour modifier un mot de passe dans le contexte de la base de données utilisateur :

ALTER USER user_name WITH PASSWORD = 'strong_password';

SQL Managed Instance

Azure SQL Managed Instance se comporte comme SQL Server localement dans le contexte des bases de données autonomes. Veillez à changer le contexte de votre base de données, de base de données Master en base de données utilisateur, lorsque vous créez l’utilisateur de base de données autonome. De plus, il ne doit y avoir aucune connexion active à la base de données utilisateur lors de la définition de l’option d’autonomie. Inspirez-vous du code suivant.

Avertissement

L’exemple de script suivant utilise une instruction kill pour fermer tous les processus utilisateur sur la base de données. Assurez-vous que vous comprenez les conséquences de ce script et qu’il convient à votre entreprise avant de l’exécuter. Vérifiez également qu’aucune autre connexion n’est active sur votre base de données SQL Managed Instance, car le script interrompt d’autres processus qui s’exécutent sur la base de données.

USE master;

SELECT * FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test')

DECLARE @kill_string varchar(8000) = '';
SELECT @kill_string = @kill_string + 'KILL ' + str(session_id) + '; '  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test') and is_user_process = 1;

EXEC(@kill_string);
GO

sp_configure 'contained database authentication', 1;  
GO
 
RECONFIGURE;  
GO 

SELECT * FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test')

ALTER DATABASE Test
SET containment=partial

USE Test;  
GO 

CREATE USER Carlo  
WITH PASSWORD='Enterpwdhere*'  

SELECT containment_desc FROM sys.databases
WHERE name='Test'

Notes

  • Les utilisateurs de base de données autonome doivent être activés pour chaque instance de SQL Server. Pour plus d’informations, consultez Authentification de la base de données autonome (option de configuration de serveur).
  • Les connexions et les utilisateurs de base de données autonome sans chevauchement de noms peuvent coexister dans vos applications.
  • Supposons qu’une connexion dans la base de données master porte le nom name1. Si vous créez un utilisateur de base de données autonome nommé nom1, lorsqu'un nom de base de données est fourni dans la chaîne de connexion, le contexte de l'utilisateur de base de données est choisi à la place du contexte de connexion lors de la connexion à la base de données. Autrement dit, les utilisateurs de base de données autonome sont prioritaires sur les connexions portant le même nom.
  • Dans SQL Database, le nom de l'utilisateur de base de données autonome ne peut pas être le même que celui du compte d'administrateur du serveur.
  • Le compte d'administrateur du serveur SQL Database ne peut jamais être un utilisateur de base de données autonome. L'administrateur du serveur dispose d'autorisations suffisantes pour créer et gérer les utilisateurs de base de données autonome. L'administrateur du serveur peut accorder des autorisations aux utilisateurs de base de données autonome sur les bases de données utilisateur.
  • Étant donné que les utilisateurs de base de données autonome sont des principaux au niveau de la base de données, vous devez créer ces utilisateurs dans chaque base de données où vous souhaitez les utiliser. L’identité est limitée à la base de données. L'identité est indépendante (en tous points) de l'utilisateur possédant un nom et un mot de passe identiques dans une autre base de données située sur le même serveur.
  • Définissez des mots de passe avec un niveau de force semblable à celui des mots de passe utilisés normalement pour les connexions.

Contenu connexe