Exporter les connexions SQL Server

Ce document explique comment exporter des identifiants SQL Server, des identifiants de sécurité (SID) et des hachages de mot de passe depuis une instance Cloud SQL pour SQL Server à l'aide de la procédure stockée sp_help_revlogin.

Lorsque vous migrez des bases de données, configurez la synchronisation des bases de données ou gérez des instances répliquées avec accès en lecture et des instances répliquées de reprise après sinistre (DR), vous devez recréer les connexions utilisateur sur l'instance de destination avec les SID et les hachages de mot de passe correspondants. Cela permet de s'assurer que les utilisateurs de la base de données restent mappés à leurs connexions au serveur correspondantes et conservent leurs autorisations.

Cloud SQL pour SQL Server fournit la procédure stockée sp_help_revlogin dans la base de données msdb pour générer des scripts Transact-SQL (T-SQL) afin de recréer les connexions utilisateur.

Avant de commencer

Rôles requis

Pour obtenir l'autorisation nécessaire pour configurer des indicateurs de base de données, demandez à votre administrateur de vous accorder les rôles IAM suivants sur le projet :

• Administrateur Cloud SQL (roles/cloudsql.admin)
• Éditeur Cloud SQL (roles/cloudsql.editor)

Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

Ce rôle prédéfini contient l'autorisation cloudsql.instances.update, qui est requise pour configurer les indicateurs de base de données.

Vous pouvez également obtenir cette autorisation avec des rôles personnalisés ou d'autres rôles prédéfinis.

Autorisations d'accès à la base de données

Assurez-vous d'avoir accès au rôle d'utilisateur SQL Server sqlserver par défaut.

Activer l'option de base de données

Pour installer la procédure stockée sp_help_revlogin dans la base de données msdb, activez l'indicateur de base de données cloud sql enable sp_help_revlogin sur votre instance.

gcloud sql instances patch INSTANCE_NAME \
    --database-flags="cloud sql enable sp_help_revlogin=on"

Se connecter à l'aide d'un outil client compatible

La procédure stockée sp_help_revlogin génère des scripts CREATE LOGIN à l'aide d'instructions T-SQL PRINT (messages d'information) plutôt que d'ensembles de résultats tabulaires (instructions SELECT).

Connectez-vous à votre instance Cloud SQL à l'aide de l'un des outils suivants :

• SQL Server Management Studio (SSMS) : connectez-vous à l'instance, exécutez la procédure et affichez les scripts générés dans l'onglet Messages. Vous pouvez également appuyer sur Ctrl+T pour passer en mode de sortie d'exécution Résultats au format texte avant d'exécuter la requête.
• Visual Studio Code : connectez-vous à l'aide de l'extension MSSQL, exécutez la procédure et affichez les scripts générés dans l'onglet Messages.

• Utilitaire sqlcmd : connectez-vous à l'instance et générez les scripts générés directement dans un fichier SQL :

  sqlcmd -S INSTANCE_IP \
      -U USERNAME \
      -P PASSWORD -d msdb \
      -Q "EXEC dbo.sp_help_revlogin" -o output_logins.sql
  

  Remplacez les éléments suivants :

  • INSTANCE_IP : adresse IP de votre instance Cloud SQL.
  • USERNAME : nom d'utilisateur de votre base de données d'administration (par exemple, sqlserver).
  • PASSWORD : mot de passe de l'utilisateur de votre base de données.

Exporter des connexions à l'aide de sp_help_revlogin

Connectez-vous à la base de données msdb et exécutez la procédure stockée sp_help_revlogin.

• Pour exporter toutes les connexions client :

  EXEC msdb.dbo.sp_help_revlogin;
  

• Pour exporter un identifiant spécifique :

  EXEC msdb.dbo.sp_help_revlogin
      @login_name = 'LOGIN_NAME';
  

  Remplacez LOGIN_NAME par le nom de la connexion à exporter.

Recréer les connexions sur l'instance de destination

1. Copiez les instructions CREATE LOGIN générées à partir du résultat de la requête.
2. Connectez-vous à votre instance SQL Server de destination.
3. Exécutez les instructions générées dans une fenêtre de requête ou à l'aide de sqlcmd.

Les instructions générées créent les connexions sur l'instance de destination avec leurs SID d'origine, leurs bases de données par défaut et leurs hachages de mot de passe. Pour en savoir plus sur les points à prendre en compte lors du transfert de connexions entre instances, consultez la documentation Microsoft sur le transfert de connexions et de mots de passe entre instances SQL Server.

Synchroniser les connexions avec les instances répliquées avec accès en lecture

Lorsque Cloud SQL crée une instance répliquée avec accès en lecture ou une instance répliquée de reprise après sinistre (DR), il copie les identifiants existants de l'instance principale. Toutefois, les connexions créées sur l'instance principale après la création de l'instance répliquée ne sont pas automatiquement répliquées.

Bien que les bases de données utilisateur d'une instance dupliquée avec accès en lecture soient en lecture seule, vous pouvez vous connecter à une instance dupliquée avec accès en lecture et exécuter les instructions CREATE LOGIN générées par sp_help_revlogin dans la base de données master de l'instance dupliquée.

Nous vous recommandons d'exporter régulièrement les nouvelles connexions de votre instance principale et de les recréer sur vos instances répliquées avec accès en lecture et vos instances répliquées de reprise après sinistre. La synchronisation de routine garantit que les utilisateurs peuvent s'authentifier auprès des instances répliquées avec accès en lecture et que les applications peuvent se reconnecter immédiatement après une commutation, un basculement d'instance répliquée ou une promotion d'instance répliquée.

Limites et connexions exclues

sp_help_revlogin exclut automatiquement les types de connexions suivants de l'exportation :

• Google Cloud comptes de service et comptes de gestion internes.
• Comptes système SQL Server internes (identifiants précédés de ##).
• Connexions attribuées au rôle serveur fixe sysadmin.
• Connexions attribuées à des rôles de serveur d'administration restreints.

Désactiver l'indicateur de base de données

Si vous n'avez plus besoin de la procédure stockée, définissez l'option sur off (ou supprimez-la de l'instance) :

gcloud sql instances patch INSTANCE_NAME \
    --database-flags="cloud sql enable sp_help_revlogin=off"

Lorsqu'il est défini sur off ou supprimé, Cloud SQL supprime automatiquement dbo.sp_help_revlogin de la base de données msdb.

Étapes suivantes

• Pour en savoir plus sur les utilisateurs SQL Server, consultez Utilisateurs SQL Server.
• Pour en savoir plus sur la configuration des indicateurs de base de données, consultez Configurer des indicateurs de base de données.
• Pour en savoir plus sur les procédures stockées compatibles, consultez Procédures stockées SQL Server.
• Pour en savoir plus sur le transfert des identifiants et des mots de passe, consultez Transférer des identifiants et des mots de passe entre des instances SQL Server.