Rendre votre base de données portable à l’aide de bases de données autonomes

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Utilisez 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 isolée d’autres bases de données et de l’instance de SQL Server ou sql Database (et de la master base de données) 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. Lorsque vous utilisez SQL Database, combinez des utilisateurs de base de données autonome avec des règles de pare-feu au niveau de la base de données.

Cet article examine les avantages de l’utilisation du modèle de base de données autonome par rapport au modèle de connexion/utilisateur traditionnel et aux règles de pare-feu au niveau du serveur ou Windows. Des scénarios spécifiques, une facilité de gestion ou une logique métier d’application peuvent encore nécessiter l’utilisation des règles de pare-feu traditionnelles de connexion/utilisateur et de pare-feu au niveau du serveur.

Modèle de connexion et d’utilisateur traditionnel

Dans le modèle de connexion traditionnel, les utilisateurs Windows ou les membres des groupes Windows se connectent au Moteur de base de données en fournissant des informations d’identification d’utilisateur ou de groupe authentifiées par Windows. Ou les utilisateurs peuvent fournir à la fois un nom et un mot de passe et se connecter à l’aide 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.

Une fois que le Moteur de base de données confirme les informations d’identification Authentification Windows ou authentifie les informations d’identification d’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 à (c’est-à-dire associée à) un utilisateur de base de données dans la base de données utilisateur. Le chaîne de connexion peut également spécifier la connexion à une base de données spécifique, facultative dans SQL Server, mais requise dans SQL Database.

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

Si une connexion à la master base de données 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 base de données autonome, la connexion dans la master base de données 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 de l’utilisateur ne dispose pas d’une connexion associée dans la master base de données.

Le modèle utilisateur de base de données autonome prend en charge l’authentification Authentification Windows et 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, l’chaîne de connexion doit toujours contenir un paramètre pour 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 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 utilisateur identique 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 Connecter à SQL Database à l’aide de 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. Administration istrateurs peuvent configurer l’authentification universelle pour exiger l’authentification multifacteur, qui vérifie l’identité à l’aide d’un appel téléphonique, d’un sms, d’un carte intelligent avec un code confidentiel 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. Vous n’avez donc pas besoin de modifier l’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é au chaîne de connexion, s’il n’est pas déjà présent.

Important

Lorsque vous utilisez le modèle traditionnel, les rôles au niveau du serveur et les autorisations au niveau du 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 de base de données et les utilisateurs de base de données disposant de 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 de serveur hautement privilégiés et étend le contrôle d’accès pour inclure des utilisateurs de base de données hautement privilégié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 traditionnelles) 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 Moteur de base de données accès.

Pare-feu SQL Database

SQL Database autorise des règles de pare-feu distinctes pour les connexions au niveau du serveur (connexions) et pour les connexions au niveau de la base de données (utilisateurs de base de données autonome). Quand SQL Database se connecte à une base de données utilisateur, il case activée d’abord des règles de pare-feu de base de données. S’il n’existe aucune règle qui autorise l’accès à la base de données, SQL Database case activée les règles de pare-feu au niveau du serveur. La vérification des règles de pare-feu au niveau du serveur nécessite l’accès à la base de données du master serveur SQL Database.

Les règles de pare-feu au niveau de la base de données, combinées à des utilisateurs de base de données autonome, peuvent éliminer la nécessité d’accéder à la master base de données 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 SQL Database, consultez les rubriques suivantes :

Différences de syntaxe

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

CREATE LOGIN login_name WITH PASSWORD = 'strong_password';

Ensuite, lorsque 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 master base de données :

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 à modifier le contexte de votre base de données de la base de données master vers la base de données utilisateur lorsque vous créez votre utilisateur autonome. En outre, il ne doit y avoir aucune connexion active à la base de données utilisateur lorsque vous définissez l’option de confinement. Utilisez le code suivant comme guide.

Avertissement

L’exemple de script suivant utilise une kill instruction 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 l’authentification de base de données autonome (option de configuration de serveur).
  • Les utilisateurs de base de données autonomes et les connexions avec des noms qui ne se chevauchent pas peuvent coexister dans vos applications.
  • Supposons qu’une connexion dans la master base de données porte le nom1. Si vous créez un utilisateur de base de données autonome nommé name1, lorsqu’un nom de base de données est fourni dans le chaîne de connexion, le contexte de l’utilisateur de base de données est choisi sur le contexte de connexion pour la connexion à la base de données. Autrement dit, l’utilisateur de base de données autonome est prioritaire 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 identique au nom du compte d’administrateur de 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 des utilisateurs de base de données autonomes dans chaque base de données où vous les utiliseriez. L’identité est limitée à la base de données. L’identité est indépendante (dans tous les aspects) d’un utilisateur qui a le même nom et le même mot de passe dans une autre base de données sur le même serveur.
  • Utilisez la même force de mots de passe que vous utiliseriez normalement pour les connexions.

Contenu connexe