Comment créer un accès non privilégié à une base de données Azure SQL Server géo-répliquée

Dans le cadre d’une instance SQL Server hébergée sur Azure, l’accès par défaut est logiquement un accès administrateur. Mais, malheureusement, il n’est pas possible d’en créer de supplémentaires (non privilégiés) via quelques clics depuis le portail Azure et pas davantage à l’aide de l’outil SQL Server Management Studio (qui n’est, d’ailleurs, disponible que pour Windows). Pourtant, cela peut s’avérer utile (voire nécessaire) afin de connecter, par exemple, un ETL ou un outil d’informatique décisionnelle (business intelligence, BI). Heureusement, il suffit d’exécuter quelques requêtes T-SQL pour y remédier depuis un outil tel que Azure Data Studio (qui, lui, est cross-plateforme et fonctionne parfaitement aussi sur Windows que sur MacOS et Linux).

Voyons ce qu’il en est en imaginant que vous souhaitez donner un accès en lecture seule à une base géo-répliquée à un utilisateur powerbi afin d’y connecter l’outil éponyme.

Création d’un login

Serveur primaire

Tout d’abord, connectez-vous à la base de données master et créez un login powerbi en jouant cette requête :

CREATE LOGIN [powerbi] WITH PASSWORD = '<login password>'

Récupérez ensuite l’identifiant système du login fraichement créé en exécutant cette nouvelle requête :

SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'SQL_Login' AND [name] = 'powerbi'

Consignez le SID quelque part.

Serveur de réplication

La base de données système master n’étant pas répliquée, connectez-vous y sur le serveur de réplication et exécutez la requête ci-après pour créer également un login powerbi qui a le même mot de passe et le même identifiant système que celui du serveur primaire :

CREATE LOGIN [powerbi]
WITH PASSWORD = '<login password>', SID = <login SID>

Veillez à bien recopier le SID récupéré à l’étape précédente et à renseigner le même mot de passe que celui précédemment fourni.

Remarque : il est souhaitable que le mot de passe soit renseigné à l’identique en anticipation d’une potentielle bascule entre le serveur primaire et celui de réplication suite à une défaillance que connaîtrait le serveur principal.

Création d’un utilisateur lié au login

Serveur primaire

Connectez-vous maintenant à la base de données applicative à laquelle vous souhaitez donner accès et créez, cette fois-ci, un utilisateur powerbi en jouant la requête que voici :

CREATE USER [powerbi]
FOR LOGIN [powerbi]
WITH DEFAULT_SCHEMA = dbo;

Puis ajoutez-le au groupe de ceux n’ayant qu’un droit de lecture sur la base de données en exécutant cette requête :

ALTER ROLE db_datareader 
ADD MEMBER [powerbi];

Serveur de réplication

La base de données applicative étant répliquée et l’utilisateur lui étant lié, celui-ci se retrouve donc automatiquement créé sur la base géo-répliquée. Aucune opération supplémentaire n’est donc nécessaire !

Désactivation du login sur le serveur primaire

Par mesure de sécurité et de préservation des performances, vous pouvez souhaiter désactiver le login sur le serveur primaire pour ne donner un accès qu’à la réplication. Pour cela, connectez-vous à la base de données master et exécutez simplement cette dernière requête :

ALTER LOGIN powerbi DISABLE

La base de données système master n’étant pas répliquée, le login restera donc actif sur le serveur de réplication 🙂

Références :

• https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases/
• https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-security-configure