Dans ce blog post, nous allons explorer une des nouveautés (actuellement en Preview) de SQL Azure : l’authentification avec Azure Active Directory. Cette fonctionnalité est utilisable uniquement avec SQL Azure V12.

Pour utiliser un exemple concret, nous allons partir du principe que nous possédons une architecture Azure AD relativement classique dans laquelle nous possédons plusieurs groupes « AdminSQL », « Developer », « Marketing », … Nous allons ainsi voir comment « manager » des bases de données SQL Azure en utilisant des identités et groupes Azure AD.

Les différentes opérations seront exécutées en PowerShell. De plus pour utiliser l’authentification Azure AD, il est nécessaire d’installer SSMS (SQL Server Management Studio) 2016 disponible ici.

Un petit mot sur Azure Active Directory

Dans l’écosystème Azure, il est de plus en plus fréquent d’utiliser Azure Active Directory comme « gestionnaire d’identité dans le cloud ».

Ce gestionnaire d’identité peut être utilisé uniquement en mode « cloud » c’est-à-dire que les entités qui y sont référencées, ne sont présentes que dans le cloud Azure ou en mode « hybride », c’est-à-dire que les identités qui y sont référencées sont synchronisées avec un Active Directory « OnPremise ». Cette seconde option permet aux utilisateurs d’accéder aux applications OnPremise et aux applications / services hébergés dans Azure avec les même Login/password de façon totalement transparente. Cette synchronisation peut être effectuée par l’intermédiaire d’un logiciel Microsoft nommée « Microsoft Azure AD Connect ».

SQL Azure et les « Contained User »

Pour utiliser une identité Azure AD comme utilisateur d’une base de données SQL Azure (V12), cet utilisateur doit être créé en tant que « contained user » au sein de la base données. Ce « nouveau » type d’utilisateur est apparu avec la notion de « base données portable », l’idée est de pouvoir transférer facilement une base de données (SQL Serveur ou SQL Database) d’un serveur à un autre. Un des points de difficulté pour effectuer ce transfert est la gestion des utilisateurs puisque par défaut un utilisateur (au sens SQL serveur) est présent dans la base de données et possède une référence (son login) dans la base de données « MASTER », ce qui implique donc de transférer à la fois la base de données MASTER lors du transfert d’une base d’un serveur à un autre !

Pour créer un utilisateur « classique » avec un login et un « contained user », la syntaxe est légèrement différente :

Création d’un utilisateur classique en deux temps :

1. Création du login dans la base « Master »

CREATE LOGIN login_thibaut WITH PASSWORD = 'pass@worD76!';

2. Création d’un utilisateur dans une base de données liée au Login

CREATE USER 'thibaut' FOR LOGIN 'login_thibaut';

Création d’un « contained user » :

CREATE USER thibaut WITH PASSWORD = 'pass@worD76!';

Création d’un serveur SQL Azure avec un Template ARM

Pour créer une base de données rapidement et simplement, rien de tel qu’un template ARM (Azure Resource Manager), le template utilisé ici déploie un serveur SQL (contenant une base de données nommée « contact ») au sein d’un groupe de ressources nommé « contact-apps-rg».

Le templates ARM sont disponibles sur le repository suivant : https://github.com/ThibautRanise/arm-template-sqlazurev12

Une fois le déploiement exécuté et réalisé avec succès, on retrouve le groupe de ressources contenant le serveur SQL et la base de données « contacts » dans le portail Azure :

Création d’un Administrateur Azure AD

Maintenant que le serveur SQL Azure est mis en place, il est temps de créer un administrateur Azure AD qui va pouvoir créer des bases de données et y ajouter des utilisateurs et groupes Azure AD.

Cet administrateur Azure AD peut être un utilisateur Azure AD ou faire partie d’un groupe, dans notre cas nous allons définir que tous les utilisateurs présents dans le groupe « AdminSQL » seront un administrateur du serveur SQL créé précédemment. Pour qu’un utilisateur présent dans le groupe « AdminSQL » puisse être considéré comme Administrateur Azure AD, il doit posséder le rôle « Administrateur Général » :

Une seule ligne de PowerShell suffit pour définir le groupe « AdminSQL » en tant qu’administrateur du server « apps-sql » :

Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName "contact-apps-rg" -ServerName "apps-sql" -DisplayName "AdminSQL"

Après avoir exécuté cette commande, on retrouve dans les propriétés du Server SQL Azure que le groupe « AdminSQL » est bien administrateur du serveur.

*** En utilisant un administrateur Azure AD, le serveur SQL possèdera donc deux types d’administrateur :

• un administrateur SQL Azure « Classique » 
• un administrateur Azure AD

Ces deux types d’administrateur possèdent exactement les mêmes droits cependant l’administrateur Azure AD peut en plus créer des utilisateurs de base données basés sur des utilisateurs et groupes Azure AD.

Connexion au Serveur SQL

Maintenant que tous les utilisateurs du groupe « AdminSQL » sont administrateurs du serveur SQL Azure « apps-sql », tous ces utilisateurs peuvent s’y connecter. Pour cela, nous allons utiliser SSMS 2016, ce dernier possède une nouvelle option dans l’interface de connexion pour se connecter à un serveur SQL Azure en utilisant une identité Azure AD. Nous utilisons ici un utilisateur Azure AD possédant un rôle « Administrateur général » nommé « dba@traniseinfinitesquare.onmicrosoft.com » qui fait partie du groupe « AdminSQL » :

Nous accédons ainsi au serveur SQL Azure « apps-sql » :

Ce processus de connexion est identique pour toute entité Azure AD autorisée à accéder au serveur.

Gestion des groupes et utilisateurs Azure AD

Une fois connecté au serveur « apps-sql » en tant qu’administrateur, il est possible de créer des bases de données et des utilisateurs.

Par défaut, les administrateurs Azure AD d’un serveur SQL possèdent deux rôles :

- « dbmanager » : permet de créer / supprimer des bases de données

- « loginmanager » : permet d’ajouter / supprimer des utilisateurs

Pour ajouter un utilisateur Azure AD au niveau du server SQL, il faut utiliser la syntaxe de création d’un « contained user », en spécifiant l’adresse email de l’utilisateur ou le nom du groupe Azure AD ainsi que la clause « FROM EXTERNAL PROVIDER » :

--Add an Azure AD User 
CREATE USER "myUser@mydomain.com" FROM EXTERNAL PROVIDER; 

--Add an Azure AD Group 
CREATE USER "myGroupName" FROM EXTERNAL PROVIDER;

* On remarque qu’aucun mot de passe n’est spécifié car ce sont les mots de passes des identités Azure AD qui sont utilisés lors de l’authentification

Par défaut tout nouvel utilisateur SQL créé à partir d’une identité Azure AD ne possède qu’une autorisation CONNECT et le rôle PUBLIC, les autorisations de ce rôle sont limitées ! Il est donc nécessaire d’attribuer des autorisations et / ou rôles à ces nouveaux utilisateurs.

Nous voulons que l’ensemble des utilisateurs présents dans le groupe Azure AD « developpeur » aient la possibilité de créer des bases de données, sans pour autant avoir le droit de créer d’autres utilisateurs. Nous attribuons donc au goupe Azure AD « Developer » le role « dbmanager ». De même nous voulons que le groupe « Marketing » ait seulement un accès en lecture sur la base de donnée « contact » créée avec le template ARM.

Au niveau du serveur SQL Azure :

-- Add the Developer group to give them access to the server
CREATE USER "Developer" FROM EXTERNAL PROVIDER; 

-- Give the role “dbmanager” to all user in Developer’s group
ALTER ROLE dbmanager ADD MEMBER Developer;

-- Add the marketing group to give them access to the server
CREATE USER “marketing” FROM EXTERNAL PROVIDER;

Au niveau de la base données « CONTACTS » :

-- Add the Developer group as contained user of the CONTACTS database
CREATE USER "Developer" FROM EXTERNAL PROVIDER; 

-- Attribute the 'db_owner' role for the Developer group on the CONTACTS database
EXEC sp_addrolemember N'db_owner', N'Developer'

-- Add the Markting group as contained user of the CONTACTS database
CREATE USER "marketing" FROM EXTERNAL PROVIDER; 

-- Attribute the 'db_datareader' role for the Developer group on the CONTACTS database
EXEC sp_addrolemember N'db_datareader', N'marketing'

Une fois connecté au serveur avec un utilisateur du groupe « Developer », il est possible :

• De créer et administrer de nouvelles bases de données (grâce au role « dbmanager »)
• D’administrer la base de données « contacts » (grâce au role « db_owner »)

Une fois connecté au serveur avec un utilisateur présent dans le groupe « Marketing » il est possible :

• D’effectuer des requêtes SELECT sur toutes les tables et les vues de la base données « contacts »

Happy Coding