Gérer des bases de données dans Azure SQL Database avec Azure Automation

Article
12/25/2023

Cet article décrit la procédure de connexion et de gestion des bases de données dans Azure SQL Database à l’aide de l’identité managée affectée par le système d’AzureAutomation. Azure Automation vous permet de gérer les bases de données Azure SQL Database à l'aide des dernières cmdlets Az PowerShell disponibles dans Azure Az PowerShell.

Azure Automation dispose dès le départ de ces cmdlets Azure Az PowerShell, de sorte que vous pouvez effectuer toutes les tâches de gestion de base de données SQL au sein du service. Dans Azure Automation, vous pouvez également associer ces cmdlets à des cmdlets d’autres services Azure afin d’automatiser des tâches complexes entre des services Azure et des systèmes tiers.

Azure Automation peut également émettre des commandes T-SQL (Transact SQL) sur les serveurs SQL à l’aide de PowerShell.

Pour exécuter les commandes sur la base de données, vous devez effectuer les opérations suivantes :

Vérifier que le compte Automation a une identité managée affectée par le système.
Fournir les autorisations appropriées à l’identité managée Automation.
Configurer le serveur SQL de façon à utiliser l’authentification Microsoft Entra.
Créer un utilisateur sur le serveur SQL server qui correspond à l’identité managée du compte Automation.
Créer un runbook pour vous connecter et exécuter les commandes.
(Facultatif) Si le serveur SQL est protégé par un pare-feu, créer un Runbook Worker hybride (HRW), installer les modules SQL sur ce serveur et ajouter l’adresse IP du HRW à la liste verte sur le pare-feu.

Se connecter à Azure SQL Database à l’aide de l’identité managée affectée par le système

Pour autoriser l’accès à partir de l’identité managée affectée par le système Automation à Azure SQL Database, effectuez ces étapes :

Si l’identité managée affectée par le système Automation est DÉSACTIVÉE, effectuez ces étapes :
1. Connectez-vous au portail Azure.
2. Accédez à votre compte Automation.
3. Dans la page du compte Automation, sous Paramètres du compte, sélectionnez Identité.
4. Sous l’onglet Affectée par le système, sélectionnez ACTIVÉE pour État.
Une fois l’identité managée affectée par le système activée, vous devez fournir au compte l’accès requis en effectuant ces étapes :
1. Dans la page Compte Automation | Identité, sous l’onglet Affectée par le système, sous Autorisations, sélectionnez Attributions de rôles Azure.
2. Dans la page Attributions de rôles Azure, sélectionnez + Ajouter une attribution de rôle (préversion).
3. Dans Ajouter une attribution de rôle (préversion), sélectionnez SQLcomme Étendue, sélectionnez l’Abonnement, la Ressource dans la liste déroulante et le Rôle en fonction des autorisations minimales requises, puis sélectionnez Enregistrer.
Configurez le serveur SQL pour l’authentification Active Directory en effectuant ces étapes :
1. Accédez à la page d’accueil du portail Azure et sélectionnez Serveurs SQL.
2. Dans la page SQL Server, sous Paramètres, sélectionnez Microsoft Entra ID.
3. Sélectionnez Définir l’administrateur afin de configurer le serveur SQL pour l’authentification AD.
Ajoutez l’authentification côté SQL en effectuant ces étapes :
1. Accédez à la page d’accueil du portail Azure et sélectionnez Serveurs SQL.
2. Dans la page SQL Server, sous Paramètres, sélectionnez Bases de données SQL.
3. Sélectionnez votre base de données pour accéder à la page de base de données SQL, puis sélectionnez Éditeur de requête (préversion) et exécutez les deux requêtes suivantes :
  - CREATE USER "AutomationAccount" FROM EXTERNAL PROVIDER WITH OBJECT_ID= ObjectID
  - EXEC sp_addrolemember db_owner, "AutomationAccount"
    - Compte Automation : à remplacer par le nom de votre compte Automation
    - Object ID : à remplacer par l’ID d’objet (principal) de votre principal d’identité managée affectée par le système (obtenu à l’étape 1).

Exemple de code

Connexion à Azure SQL Server

if ($($env:computerName) -eq "Client") {"Runbook running on Azure Client sandbox"} else {"Runbook running on " + $env:computerName}
Disable-AzContextAutosave -Scope Process
Connect-AzAccount -Identity
$Token = (Get-AZAccessToken -ResourceUrl https://database.windows.net).Token
Invoke-Sqlcmd -ServerInstance azuresqlserverxyz.database.windows.net -Database MyDBxyz -AccessToken $token -query 'select * from TableXYZ'

Vérifier les autorisations de compte côté SQL

SELECT roles.[name] as role_name, members.name as [user_name] 
from sys.database_role_members 
Join sys.database_principals roles on database_role_members.role_principal_id= roles.principal_id 
join sys.database_principals members on database_role_members.member_principal_id=members.principal_id 
Order By 
roles.[name], members.[name]

Remarque

Lorsqu’un serveur SQL s’exécute derrière un pare-feu, vous devez exécuter le runbook Azure Automation sur une machine de votre propre réseau. Vérifiez que vous configurez cette machine en tant que Runbook Worker hybride afin que l’adresse IP ou le réseau ne soit pas bloqué par le pare-feu. Pour plus d’informations sur la configuration d’une machine en tant que Worker hybride, consultez Créer un Worker hybride.

Utiliser un Worker hybride

Lorsque vous utilisez un Worker hybride, les modules utilisés par votre runbook doivent être installés localement à partir d’une invite PowerShell avec élévation de privilèges. Par exemple : - Install-module Az.Accounts and Install-module SqlServer. Pour rechercher les noms de module requis, exécutez une commande sur chaque cmdlet, puis vérifiez la source. Par exemple, pour vérifier le nom du module pour la cmdlet Connect-AzAccounts qui fait partie du module Az.Account, exécutez la commande suivante : get-command Connect-AzAccount

Remarque

Nous vous recommandons d’ajouter le code suivant en haut de tout runbook destiné à s’exécuter sur un Worker hybride : if ($($env:computerName) -eq "CLIENT") {"Runbook running on Azure CLIENT"} else {"Runbook running on " + $env:computerName}. Le code vous permet de voir le nœud sur lequel il s’exécute et, si vous l’exécutez accidentellement sur le cloud Azure au lieu du Worker hybride, cela permet de déterminer la raison pour laquelle un runbook n’a pas fonctionné.

Étapes suivantes

Pour plus d’informations sur l’utilisation des informations d’identification, consultez Gérer les informations d’identification dans Azure Automation.
Pour en savoir plus sur les modules, consultez Gérer les modules dans Azure Automation.
Si vous devez démarrer un runbook, consultez Démarrer un runbook dans Azure Automation.
Pour plus d’informations sur PowerShell, consultez la documentation PowerShell.

Partager via