Transférer des noms d’accès et des mots de passe entre instances de SQL Server

Cet article explique comment transférer les connexions et les mots de passe entre différentes instances de SQL Server s’exécutant sur Windows.

Version du produit d’origine :   SQL Server
Numéro de la base de connaissances initiale :   918992

Introduction

Cet article explique comment transférer des noms d’accès et des mots de passe entre différentes instances de Microsoft SQL Server.

Notes

Les instances peuvent se trouver sur le même serveur ou sur des serveurs différents, et leurs versions peuvent différer.

Plus d’informations

Dans cet article, le serveur A et le serveur B sont des serveurs différents.

Une fois que vous avez déplacé une base de données de l’instance de SQL Server sur le serveur A vers l’instance de SQL Server sur le serveur B, les utilisateurs risquent de ne pas pouvoir se connecter à la base de données sur le serveur B. en outre, les utilisateurs peuvent recevoir le message d’erreur suivant :

Échec de la connexion de l’utilisateur'myuser'. (Microsoft SQL Server, erreur : 18456)

Ce problème se produit car vous n’avez pas transféré les noms d’accès et les mots de passe de l’instance de SQL Server sur le serveur A vers l’instance de SQL Server sur le serveur B.

Notes

Le message d’erreur 18456 s’affiche également pour d’autres raisons. Pour plus d’informations sur ces causes et sur la révision des résolutions potentielles MSSQLSERVER_18456.

Pour transférer les connexions, utilisez l’une des méthodes suivantes, selon votre situation.

  • Méthode 1 : réinitialiser le mot de passe sur l’ordinateur SQL Server de destination (serveur B)

    Pour résoudre ce problème, réinitialisez le mot de passe dans l’ordinateur SQL Server, puis extrayez la connexion.

    Notes

    L’algorithme de hachage de mot de passe est utilisé lors de la réinitialisation du mot de passe.

  • Méthode 2 : transférer des noms d’accès et des mots de passe sur le serveur de destination (serveur B) à l’aide de scripts générés sur le serveur source (serveur A)

    1. Créez des procédures stockées qui vous aideront à générer des scripts nécessaires pour transférer les connexions et leurs mots de passe. Pour ce faire, connectez-vous au serveur A à l’aide de SQL Server Management Studio (SSMS) ou de tout autre outil client et exécutez le script suivant :

      USE master
      GO
      IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
      DROP PROCEDURE sp_hexadecimal
      GO
      CREATE PROCEDURE sp_hexadecimal
      @binvalue varbinary(256),
      @hexvalue varchar (514) OUTPUT
      AS
      DECLARE @charvalue varchar (514)
      DECLARE @i int
      DECLARE @length int
      DECLARE @hexstring char(16)
      SELECT @charvalue = '0x'
      SELECT @i = 1
      SELECT @length = DATALENGTH (@binvalue)
      SELECT @hexstring = '0123456789ABCDEF'
      WHILE (@i <= @length)
      BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
      SUBSTRING(@hexstring, @firstint+1, 1) +
      SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
      END
      
      SELECT @hexvalue = @charvalue
      GO
      
      IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
      DROP PROCEDURE sp_help_revlogin
      GO
      CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
      DECLARE @name sysname
      DECLARE @type varchar (1)
      DECLARE @hasaccess int
      DECLARE @denylogin int
      DECLARE @is_disabled int
      DECLARE @PWD_varbinary varbinary (256)
      DECLARE @PWD_string varchar (514)
      DECLARE @SID_varbinary varbinary (85)
      DECLARE @SID_string varchar (514)
      DECLARE @tmpstr varchar (1024)
      DECLARE @is_policy_checked varchar (3)
      DECLARE @is_expiration_checked varchar (3)
      
      DECLARE @defaultdb sysname
      
      IF (@login_name IS NULL)
      DECLARE login_curs CURSOR FOR
      
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
      sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
      ELSE
      DECLARE login_curs CURSOR FOR
      
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
      sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
      OPEN login_curs
      
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
      IF (@@fetch_status = -1)
      BEGIN
      PRINT 'No login(s) found.'
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN -1
      END
      SET @tmpstr = '/* sp_help_revlogin script '
      PRINT @tmpstr
      SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + '*/'
      PRINT @tmpstr
      PRINT ''
      WHILE (@@fetch_status <> -1)
      BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
      PRINT ''
      SET @tmpstr = '-- Login: ' + @name
      PRINT @tmpstr
      IF (@type IN ( 'G', 'U'))
      BEGIN -- NT authenticated account/group
      
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
      END
      ELSE BEGIN -- SQL Server authentication
      -- obtain password and sid
      SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256))
      EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
      EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
      
      -- obtain password policy state
      SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
      SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
      
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
      
      IF ( @is_policy_checked IS NOT NULL )
      BEGIN
      SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
      END
      IF ( @is_expiration_checked IS NOT NULL )
      BEGIN
      SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
      END
      END
      IF (@denylogin = 1)
      BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
      END
      ELSE IF (@hasaccess = 0)
      BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
      END
      IF (@is_disabled = 1)
      BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
      END
      PRINT @tmpstr
      END
      
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
      END
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN 0
      GO
      

      Notes

      Ce script crée deux procédures stockées dans la base de données Master. Les procédures sont nommées sp_hexadecimal et sp_help_revlogin .

    2. Dans l’éditeur de requête SSMS, sélectionnez l’option résultats dans le texte .

    3. Exécutez l’instruction suivante dans la même fenêtre de requête ou dans une nouvelle fenêtre de requête :

      EXEC sp_help_revlogin
      
    4. Le script de sortie généré par la sp_help_revlogin procédure stockée est le script de connexion. Ce script de connexion crée les connexions qui ont l’identificateur de sécurité (SID) d’origine et le mot de passe d’origine.

Important

Consultez les informations de la section Remarques ci-dessous avant de passer aux étapes d’implémentation sur le serveur de destination.

Étapes sur le serveur de destination (serveur B)

Connectez-vous au serveur B à l’aide de n’importe quel outil client (par exemple, SSMS), puis exécutez le script généré à l’étape 4 (sortie de sp_helprevlogin ) à partir du serveur A.

Remarques

Passez en revue les informations suivantes avant d’exécuter le script de sortie sur l’instance sur le serveur B :

  • Un mot de passe peut être haché comme suit :

    • VERSION_SHA1: Ce hachage est généré à l’aide de l’algorithme SHA1 et est utilisé dans SQL Server 2000 par le biais de SQL Server 2008 R2.
    • VERSION_SHA2: Ce hachage est généré à l’aide de l’algorithme SHA2 512 et est utilisé dans SQL Server 2012 et les versions ultérieures.
  • Examinez attentivement le script de sortie. Si le serveur A et le serveur B se trouvent dans des domaines différents, vous devez modifier le script de sortie. Ensuite, vous devez remplacer le nom de domaine d’origine à l’aide du nouveau nom de domaine dans les instructions CREATe LOGIN. Les connexions intégrées auxquelles l’accès est accordé dans le nouveau domaine n’ont pas le même SID que les connexions dans le domaine d’origine. Par conséquent, les utilisateurs sont orphelins de ces connexions. Pour plus d’informations sur la façon de résoudre ces utilisateurs orphelins, consultez la rubrique How to resolve permission Problems lors du déplacement d’une base de données entre des serveurs exécutant SQL Server.

    Si le serveur A et le serveur B se trouvent dans le même domaine, le même SID est utilisé. Par conséquent, il est peu probable que les utilisateurs soient orphelins.

  • Dans le script de sortie, les connexions d’accès sont créées à l’aide du mot de passe chiffré. Ceci est dû à l’argument HASHed dans l' CREATE LOGIN instruction. Cet argument spécifie que le mot de passe entré après l’argument PASSWORD est déjà haché.

  • Par défaut, seul un membre du rôle serveur fixe sysadmin peut exécuter une SELECT instruction à partir de la sys.server_principals vue. À moins qu’un membre du rôle serveur fixe sysadmin n’accorde les autorisations nécessaires aux utilisateurs, les utilisateurs ne peuvent pas créer ou exécuter le script de sortie.

  • Les étapes décrites dans cet article ne transfèrent pas les informations de base de données par défaut pour une connexion particulière. En effet, il est possible que la base de données par défaut n’existe pas toujours sur le serveur B. Pour définir la base de données par défaut pour une connexion, utilisez l' ALTER LOGIN instruction en transmettant le nom de connexion et la base de données par défaut en tant qu’arguments.

  • Trier les ordres sur les serveurs source et de destination :

    • Serveur A et sans respect de la casse serveur B: l’ordre de tri du serveur A peut être insensible à la casse, et l’ordre de tri du serveur b peut être sensible à la casse. Dans ce cas, les utilisateurs doivent taper les mots de passe en majuscules après le transfert des noms d’accès et des mots de passe vers l’instance sur le serveur B.

    • Serveur B sensible à la casse et serveur B non sensible à la casse : L’ordre de tri du serveur A peut être sensible à la casse, et l’ordre de tri du serveur B peut ne pas être sensible à la casse. Dans ce cas, les utilisateurs ne peuvent pas se connecter à l’aide des noms d’accès et des mots de passe que vous transférez à l’instance sur le serveur B sauf si l’une des conditions suivantes est vraie :

      • Les mots de passe d’origine ne contiennent pas de lettres.
      • Toutes les lettres des mots de passe d’origine sont des lettres majuscules.
    • Respecte ou non la casse sur les deux serveurs: l’ordre de tri du serveur a et du serveur b peut être sensible à la casse, ou l’ordre de tri du serveur a et du serveur b peut ne pas être sensible à la casse. Dans ce cas, les utilisateurs ne subissent pas de problème.

  • Une connexion qui existe déjà dans l’instance sur le serveur B peut avoir un nom identique à celui d’un nom dans le script de sortie. Dans ce cas, vous recevez le message d’erreur suivant lorsque vous exécutez le script de sortie sur l’instance sur le serveur B :

    MSG 15025, niveau 16, état 1, ligne 1
    Le principal du serveur «MyLogin» existe déjà.

    De même, une connexion qui se trouve déjà dans l’instance sur le serveur B peut avoir un SID identique à celui d’un SID dans le script de sortie. Dans ce cas, vous recevez le message d’erreur suivant lorsque vous exécutez le script de sortie sur l’instance sur le serveur B :

    MSG 15433, niveau 16, état 1, ligne 1 le SID de paramètre fourni est en cours d’utilisation.

    Par conséquent, vous devez effectuer les opérations suivantes :

    1. Examinez attentivement le script de sortie.

    2. Examinez le contenu de la vue de sys.server_principals dans l’instance sur le serveur B.

    3. Corrigez ces messages d’erreur comme il convient.

      Dans SQL Server 2005, le SID d’un compte de connexion est utilisé pour implémenter l’accès au niveau de la base de données. Une connexion peut avoir différents sid dans différentes bases de données sur un serveur. Dans ce cas, la connexion ne peut accéder qu’à la base de données dont l’identificateur de sécurité (SID) correspond au SID dans la sys.server_principals vue. Ce problème peut se produire si les deux bases de données sont combinées à partir de différents serveurs. Pour résoudre ce problème, supprimez manuellement la connexion à la base de données dont le SID ne correspond pas à l’aide de l’instruction DROP USER. Ensuite, rajoutez la connexion à l’aide de l' CREATE USER instruction.

Références