SQL Server Denali – Contained Databases

Tous ceux qui ont planché sur le database mirroring, ou plus simplement des problématiques de transfert de bases de données entre serveurs ont un jour ou l’autre rencontré le problème : pourquoi mon utilisateur n’arrive pas à se connecter ???

Revenons au bases de la sécurité dans SQL Server. Jusqu’à présent (SQL Server 2008 R2), pour se connecter à SQL Server il faut avoir un “Login”, une connexion. Suivant ce qui est paramétré lors de l’installation de l’instance (et qui peut être changé à postériori), soit des utilisateurs du monde Windows seulement peuvent se connecter, ou bien on s’ouvre à tout type de client en autorisant le mode mixte.

Pour une connexion du monde Windows, l’authentification est réalisée par un contrôleur de domaine, ou par la base locale si le serveur est en mode autonome. Le SID de la connexion (dans  syslogins), que ce soit un groupe ou un utilisateur  est alors celui issu du domaine (ou domaine local).

Pour une connexion de type SQL Server, l’authentification est réalisée par SQL Server lui-même. Il doit donc stocker le mot de passe et créer un SID.

Ensuite, un certain nombre de droits sont accordés à cette connexion. Droit de niveau serveur ou bien droit dans une ou plusieurs bases de données.

Lorsqu’un utilisateur est autorisé à travailler dans une base de données en particulier, un User ou utilisateur (sysusers) est alors créé dans cette base. Des droits sont ensuite octroyés à cet utilisateur de base de données (rôles fixes de bases de données, rôles de bases de données ou bien droits spécifiques).

Tout se complique lorsque l’on doit transférer une base d’un serveur à l’autre … Pour le connexions de type Windows, il n’y a pas de problèmes, les logins sont recrées avec le même SID, l’utilisateur retrouve les droits qu’il avait auparavant. Pour une connexion de type SQL Server, lorsque l’on recréé simplement le login, un nouveau SID est généré, qui ne correspond pas à celui présent sur l’ancien serveur. Le login a donc le droit de se connecter mais ne parvient pas à travailler … Il existe plusieurs méthodes pour recréer les logins avec un SID identique. Il est aussi possible de resynchroniser les SID. Mais ce n’est pas le but de cet article.

Cette problématique devenait gênante pour des bases mises en miroir, ou lorsque le nombre de connexions SQL Server devenait élevée : oubli le logins, mauvaise synchronisation de SID …

SQL Server 11, aka Denali, apporte une réponse élégante à la mobilité de base de données : les CDB, pour Contained DataBases.

Plus besoin de faire le distinguo entre login et user … On ne va créer que des utilisateurs de bases de données. Avec leur mot de passe présent dans la base. Donc plus de problème lors des transfert de bases ou lors de sessions de mise en miroir.

Démo (avec un simple backup restore …) :

Tout d’abord il convient d’activer l’authentification dans les bases contenues :

sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

 

Ensuite, créons une base de donnée de test :

CREATE DATABASE TestCDB
CONTAINMENT = PARTIAL;
go

Tous les paramètres sont gardés avec leurs valeurs par défaut, sauf CONTAINMENT, bien sur.

Créons ensuite un utilisateur contenu dans la base :

USE TestCDB;
go

CREATE USER DBContained_User
WITH PASSWORD = 'unSuperMotDePasse';
go

On note au passage qu’aucun login n’a été créé.

image

Mais il existe bien un user dans la base en question.

image

Attention à ne pas confondre avec l’instruction “Create user xxxxx without login”.

On voit aussi clairement que le mot de passe est bien stocké avec le user, l’interface graphique reflète bien la nouveauté :

image

Tout comme les informations de sys.database_principals

SELECT [name]
               ,[type_desc]
               ,[default_schema_name]
               ,[authentication_type_desc]
FROM   [TestCDB].[sys].[database_principals]

image

Nous allons maintenant donner des droits (basiques : db_datareader ) cet utilisateur et créer une table de test.

USE [TestCDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DBContained_User]
GO
CREATE TABLE TestTable
(   id  INT IDENTITY(1,1),
	val CHAR(8000) DEFAULT 'x'
)
GO


INSERT INTO TestTable DEFAULT VALUES;
GO 1000

Testons a présent l’authentification contenue dans la base TestCDB :

image

On renseigne le bon login et le bon mot de passe, et ….

image

… zut, cela ne fonctionne pas. Et j’ai bien mis le bon mot de passe super dur à trouver.

Regardons donc du côté de la base de donnée par défaut, car, dans les version antérieures à SQL 11, un problème de connexion sur la  base par défaut entrainait un erreur de connexion a l’instance SQL.

Sauf, que pour un utilisateur contenu dans une BD, il n’y a pas de BD par défaut !

Et lorsque l’on veut lister les bases pour choisir la BD par défaut on se retrouve confronté au même problème de login :

image

C’est logique, cette information est accessible sur master, et on n’est pas connu de la base master. Il faut donc saisir à la main la base de donnée par défaut, ce qui sera a faire dans les chaines de connexions pour les applications clientes.

image

Et cette fois ci la connexion se passe bien :

image

Quelle séquence est utilisée pour authentifier l’utilisateur ?

  • Est ce que la chaine de connexion spécifie la base de donnée par défaut ?
    • Pas de BD par défaut, on bascule sur un mode d’authentification niveau serveur (master).
    • Il y a une BD par défaut, est-ce que cette BD est contenue ?
      • la base n’est pas contained, on bascule de nouveau sur le mode d’authentification serveur.
      • la base est contenue, quel est le type d’authentification ?
        • Windows : Niveau serveur, le groupe ou le login existe t’il ?
          • Oui, on authentifie niveau serveur
          • Non, on essaie d’authentifier niveau base de donnée
        • SQL Server : un user existe t’il dans la base de données ?
          • non : on bascule sur le mode d’authentification niveau serveur
          • oui : on teste le password pour donner accès ou bien renvoyer une erreur de login

Si un login (dans master) et un utilisateur contenu dans la base ont exactement le même nom et que l’on spécifie explicitement la base de donnée lors de la connexion, on utilisera systématiquement l’authentification contenue. Il faut alors spécifier master ou tout autre base “non contenue” pour s’authentifier.

On laisse la théorie de côté pour valider que ma base une fois restaurée sur mon autre instance Denali permet à cet utilisateur de se connecter …

Sur l’nstance DENALICTP1 :

backup database TestCDB
to disk = 'c:\temp\TestCDB.bak'
with init

Sur l’instance DENALICTP2 :

sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

restore database TestCDB
from disk = '\\denalictp1\temp\TestCDB.bak'
go

La base est bien restaurée :

image

Testons l’accès :

image

Tout est OK, la base est bien accessible via l’utilisateur contenu.

La fonctionnalité de CDB est un grand pas en avant vers la portabilité et la mobilité des bases de données sous SQL Server. Cela va de pair avec l’évolution du database mirroring (fonctionnalité de groupe de disponibilité) qui rend accessible plus simplement la haute disponibilité, le disaster recovery et la répartition de charge.

Bon CDB …

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans Non classé, SQL Server, est tagué , , . Ajoutez ce permalien à vos favoris.

3 commentaires pour SQL Server Denali – Contained Databases

  1. Ping : Denali – Comptes de service | Christophe LAPORTE – Consultant SQL Server

  2. Ping : Denali – plusieurs groupes de disponibilité | Christophe LAPORTE – Consultant SQL Server

  3. Ping : DotMim » Transférer les logins SQL SERVER par SIDS

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s