Les Managed Service Account (MSA et gMSA) : se simplifier la vie pour gérer ses comptes de service SQL Server

Pour faire tourner son service SQL Server sous Windows, il y a différentes écoles. Certains veulent un compte de service créé dans l’Active Directory, afin d’y appliquer des GPO et bien les identifier avec un nom explicite. D’autres préfèrent garder les choses simples et laissent les Virtual Service Account (NT Service\MSSQLSERVER).

Le problème avec le passage sur un compte de service dédié est le risque de mauvaise administration et de s’en servir pour ouvrir une session interactive, combiné avec l’éventualité que le mot de passe soit récupéré par un utilisateur malicieux. On peut donc mettre en place une stratégie d’expiration de mot de passe, mais alors la rotation des mots de passe dans le parc peut vite devenir infernale !

C’est là qu’intervient une solution assez méconnue : les Managed Service Account. L’une des principales raisons pour laquelle cette solution est méconnue est qu’elle nécessite que le niveau fonctionnel de la forêt Active Directory soit au minimum au niveau fonctionnel de Windows 2012.

Maintenant que nous sommes en 2023, on peut supposer que la plupart des infrastructures Active Directory sont dans des niveaux supérieurs.

Les MSA étaient le premier nom donné à ce type de compte, et ne fonctionnait que pour les service qui tournaient sur une seule machine. Peu de temps après a été ajouté la possibilité de fonctionner sur un cluster avec la notion de Group Managed Service Account. Mais les commandes restent les mêmes.

Pré-requis à l’utilisation d’un Managed Service Account pour SQL Server (MSA/gMSA) :

Comme indiqué, le premier pré-requis est au niveau de l’Active Directory qui doit être au niveau fonctionnel 2012 ou supérieur.

Pour SQL Server, si c’est pour travailler avec une instance “standalone” , il faudra un SQL Server 2014. Pour de l’AlwaysOn et du Failover Cluster Instance (FCI), cela nécessitant la couche cluster, et donc un gMSA, il faudra un SQL Server 2016.

Pour créer un (g)MSA, il faudra soit être administrateur du domaine ou bien disposer du privilège de création des objets de type “msDS-GroupManagedServiceAccount”.

Un accès PowerShell avec l’extension Active Directory (disponible par exemple en installant le feature Windows “Remote Management”) doit être présent sur les machines SQL Server.

Afin de faire fonctionnaire les MSA, ils est également nécessaire qu’une infrastructure KDS soit présente sur le domaine. Si vous ne savez pas si vous en avez une, vous pouvez interrogez votre domaine avec la commande PowerShell Get-KDSRootKey :

On voit ici qu’une clé a été créée le 29/11/2023.

Si jamais vous n’en avez pas, il faut la créer avec la commande :

Add-KdsRootKey -EffectiveImmediately

Bien que le paramètre EffectiveImmediately permette son usage immédiatement, j’ai rencontré des délais avant que mes machines SQL Server puissent utiliser les MSA, il se peut donc qu’il faille attendre également chez vous. Par ailleurs, si vous avez plusieurs contrôleurs de domaine, le temps de réplication peut atteindre 10 heures.

Création d’un gMSA pour SQL Server :

Notre but ici est de créer un compte de service managé pour un SQL Server 2022 avec un groupe de disponibilité. Nous sommes dans le scénario le plus compliqué, où SQL Server a déjà été installé et le groupe de disponibilité est déjà présent.

Notre configuration est telle que nous avons deux serveurs LAB1SQL1 et LAB1SQL2 avec un listener pour le groupe de disponibilité LAB1_LSTN. Nous voulons un compte de service commun pour nos deux serveurs SQL que l’on appellera LAB1_gMSA. Nous voulons également que les SPN soient enregistrés correctement sans intervention supplémentaire.

Création d’un groupe AD pour les machines autorisées à utiliser le gMSA :

Dans la console Active Directory Users and Computers, on va créer un groupe de sécurité et y ajouter les deux comptes ordinateurs de notre groupe de disponibilité :

Création du gMSA :

La création du gMSA se fait avec la commande PowerShell “New-ADServiceAccount”. Elle n’est pas possible par l’interface graphique “Active Directory Users and Computers”.

 
New-ADServiceAccount -Name LAB1_gMSA -DNSHostName LAB1SQL1.LAB1.local -ManagedPasswordIntervalInDays 90 -PrincipalsAllowedToRetrieveManagedPassword LAB1_SQL_Group -ServicePrincipalNames MSSQLSvc/LAB1SQL1.lab1.local, MSSQLSvc/LAB1SQL1.lab1.local:1433, MSSQLSvc/LAB1_LSTN.lab1.local, MSSQLSvc/LAB1_LSTN.lab1.local:1434 -Enabled $true

On aura donc un groupe de disponibilité avec les paramètres suivants:

• Son nom sera LAB1_gMSA
• La rotation automatique des mots de passe du compte aura lieu tous les 90 jours (par défaut 30)
• La liste des machines autorisées à utiliser le gMSA se trouve dans le groupe LAB1_SQL_Group
• Les SPN créés seront : MSSQLSvc/LAB1SQL1.lab1.local, MSSQLSvc/LAB1SQL1.lab1.local:1433, MSSQLSvc/LAB1_LSTN.lab1.local et MSSQLSvc/LAB1_LSTN.lab1.local:1434
• Il sera actif dès sa création

On peut désormais le voir dans la console “Active Directory Users and Computers” :

Déploiement du gMSA sur les machines SQL :

Une fois compte créé, il faut donc le déployer sur les machines SQL. On va se connecter en PowerShell sur les machines SQL et utiliser la commande “Install-ADServiceAccount”. Pour rappel, il faut avoir le composant Remote Management pour pouvoir exécuter cette commande. Si jamais vous ne l’avez pas, vous pouvez le déployer rapidement avec la commande PowerShell

Add-WindowsFeature RSAT-AD-PowerShell

Après on peut donc activer notre compte LAB1_gMSA :

Install-ADServiceAccount -Identity LAB1_gMSA

Après avoir réalisé ça sur nos deux machines, on peut les spécifier dans notre configuration SQL Server.

Configuration du gMSA dans SQL Server :

Dans la console “SQL Server Configuration Manager”, dans la section “SQL Server Services”, on doit aller dans l’onglet “Log On” des propriétés du service SQL Server (et de son agent éventuellement).

Comme un objet ordinateur, un gMSA s’écrit avec un $ à la fin.

Dans SQL Server, on n’a plus qu’à lui donner les privilèges suffisants :

CREATE LOGIN [LAB1\LAB1_gMSA$] FROM WINDOWS ; 
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [LAB1\LAB1_gMSA$] ;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [LAB1\LAB1_gMSA$]
GO

Si on regarde dans les logs, on verra désormais les lignes suivantes :

The service account is 'LAB1\LAB1_gMSA$'. This is an informational message; no user action is required.
[...]
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/LAB1SQL1.lab1.local ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/LAB1SQL1.lab1.local:1433 ] for the SQL Server service.

Ca y est ! vous avez configuré le gMSA de votre SQL Server. Mais vous pouvez (devriez ?) utiliser également ça pour vos applications qui utilisent un compte de service spécifique.

Continuez votre lecture sur le blog :

• Elastic Job Agent : l’Agent SQL Server pour le PaaS Azure (Capdata team) [AzureSQL Server]
• Sauvegardes SQL Server dans un Azure Blob Storage (Capdata team) [AzureSQL Server]
• Les nouveautés de SQL Server 2022 (Capdata team) [SQL Server]
• AWS : Backup Restore SQL Server RDS vers une EC2 ou On-Premise et vice versa ! (Emmanuel RAMI) [AWSSQL Server]
• Export d’une VM d’un ESX vers une EC2 AWS (Emmanuel RAMI) [AWS]