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

Article
10/17/2023

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

Version du produit d’origine : SQL Server
Numéro de l’article d’origine dans la base de connaissances : 918992, 246133

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.

Remarque

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, les serveurs A et B sont des serveurs différents.

Après avoir déplacé une base de données de la instance de SQL Server sur le serveur A vers la instance de SQL Server sur le serveur B, les utilisateurs peuvent 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 pour l’utilisateur « Mon_utilisateur ». (Microsoft SQL Server, Erreur : 18456)

Ce problème se produit parce que vous n’avez pas transféré les connexions et les mots de passe de la instance de SQL Server sur le serveur A vers le instance de SQL Server sur le serveur B.

Remarque

Le message d’erreur 18456 se produit également pour d’autres raisons. Pour plus d’informations sur ces causes et sur les solutions potentielles, consultez MSSQLSERVER_18456.

Pour transférer les noms d’accès, appliquez 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 de l’ordinateur de SQL Server, puis créez un script pour la connexion.

Remarque

L’algorithme de hachage du mot de passe est utilisé lorsque vous réinitialisez le mot de passe.

Méthode 2 : Transférer des connexions et des mots de passe vers le serveur de destination (serveur B) à l’aide de scripts générés sur le serveur source (serveur A).

Créez des procédures stockées qui vous permettront de générer les scripts nécessaires pour transférer les noms d’accès 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 [dbo].[sp_hexadecimal]
  (
      @binvalue varbinary(256),
      @hexvalue varchar (514) OUTPUT
  )
  AS
  BEGIN
      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
  END
  go
  IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
  GO
  CREATE PROCEDURE [dbo].[sp_help_revlogin]   
  (
      @login_name sysname = NULL 
  )
  AS
  BEGIN
      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 @Prefix                   VARCHAR(255)
      DECLARE @defaultdb                SYSNAME
      DECLARE @defaultlanguage          SYSNAME     
      DECLARE @tmpstrRole               VARCHAR (1024)

  IF (@login_name IS NULL)
  BEGIN
      DECLARE login_curs CURSOR 
      FOR 
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
          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'
          AND p.name not like '##%'
          ORDER BY p.name
  END
  ELSE
          DECLARE login_curs CURSOR 
          FOR 
              SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
              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
              ORDER BY p.name

          OPEN login_curs 
          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
          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

                  SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
                  BEGIN'
                  Print @tmpstr 

                  IF (@type IN ( 'G', 'U'))
                  BEGIN -- NT authenticated account/group 
                    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
                  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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'

                          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 

          SET @Prefix = '
          EXEC master.dbo.sp_addsrvrolemember @loginame='''

          SET @tmpstrRole=''

          SELECT @tmpstrRole = @tmpstrRole
              + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
              + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
              + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
              + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
              + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
              + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
              + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
              + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
            FROM (
                      SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
                              sysadmin,
                              securityadmin,
                              serveradmin,
                              setupadmin,
                              processadmin,
                              diskadmin,
                              dbcreator,
                              bulkadmin
                      FROM sys.syslogins
                      WHERE (       sysadmin<>0
                              OR    securityadmin<>0
                              OR    serveradmin<>0
                              OR    setupadmin <>0
                              OR    processadmin <>0
                              OR    diskadmin<>0
                              OR    dbcreator<>0
                              OR    bulkadmin<>0
                          ) 
                          AND name=@name 
                ) L 

              PRINT @tmpstr
              PRINT @tmpstrRole
              PRINT 'END'
          END 
          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
      END
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN 0
  END

Remarque

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

Dans l’éditeur de requête SSMS, sélectionnez l’option Résultats en texte.
Exécutez l’instruction suivante dans la même fenêtre de requête ou dans une nouvelle fenêtre :
```
EXEC sp_help_revlogin
```
Le script de sortie généré par la procédure stockée sp_help_revlogin est le script de connexion. Ce script de connexion permet de créer les noms d’accès qui comportent l’identificateur de sécurité (SID) d’origine et le mot de passe d’origine.

Importante

Passez en revue les informations de la section Remarques suivante avant de procéder à l’implémentation des étapes sur le serveur de destination.

Étapes sur le serveur de destination (serveur B)

Connectez-vous au serveur B à l’aide d’un outil client (comme SSMS), puis exécutez le script généré à l’étape 4 (sortie de ) à partir du sp_helprevloginserveur A.

Remarques

Vérifiez les informations suivantes avant d’exécuter le script de sortie sur l’instance du serveur B :

Un mot de passe peut être haché selon les manières suivantes :
- VERSION_SHA1 : ce hachage est généré à l’aide de l’algorithme SHA1, puis est utilisé dans SQL Server 2000 à SQL Server 2008 R2.
- VERSION_SHA2 : ce hachage est généré à l’aide de l’algorithme SHA2 512, puis est utilisé dans SQL Server versions 2012 et ultérieures.
Examinez attentivement le script de sortie. Si les serveurs A et B se trouvent sur différents domaines, vous devez modifier le script de sortie. Ensuite, vous devez remplacer le nom de domaine d’origine en utilisant le nouveau nom de domaine dans les CREATE LOGIN instructions. 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 se retrouvent orphelins de ces noms d’accès. Pour plus d’informations sur la résolution de ces utilisateurs orphelins, consultez Résoudre les problèmes d’utilisateurs orphelins (SQL Server) et ALTER USER.
Si les serveurs A et 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 noms d’accès sont créés à l’aide du mot de passe chiffré. Ceci est dû à l’argument HASHED figurant dans l’instruction CREATE LOGIN. Cet argument indique que le mot de passe qui est entré après l’argument PASSWORD est déjà haché.
Par défaut, seul un membre du rôle de serveur fixe sysadmin peut exécuter une instruction SELECT à partir de la vue sys.server_principals. À moins qu’un membre du rôle serveur fixe sysadmin 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. Cela est dû au fait 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 d’une connexion, utilisez l’instruction ALTER LOGIN en passant le nom de connexion et la base de données par défaut en tant qu’arguments.
Ordres de tri sur les serveurs source et de destination :
- Serveur A et B respectant la casse : l’ordre de tri du serveur A peut ne pas respecter la casse et l’ordre de tri du serveur B peut respecter la casse. Dans ce cas, les utilisateurs doivent taper toutes les lettres des mots de passe en majuscules après le transfert des noms d’accès et des mots de passe à l’instance sur le serveur B.
- Serveur A respectant la casse et serveur B ne respectant pas la casse : L’ordre de tri du serveur A peut respecter la casse et l’ordre de tri du serveur B peut ne pas respecter la casse. Dans ce cas, les utilisateurs ne peuvent pas se connecter à l’aide des connexions et des mots de passe que vous transférez vers le instance sur le serveur B, sauf si l’une des conditions suivantes est remplie :
  - Les mots de passe d’origine ne contiennent aucune lettre.
  - Toutes les lettres des mots de passe d’origine sont des lettres majuscules.
- Respect de la casse ou non sur les deux serveurs : l’ordre de tri des serveurs A et B peut respecter la casse, ou l’ordre de tri des serveurs A et B peut ne pas respecter la casse. Dans ce cas, les utilisateurs ne rencontrent pas de problème.
Une connexion qui se trouve déjà dans le instance sur le serveur B peut avoir un nom identique à un nom dans le script de sortie. Dans ce cas, le message d’erreur suivant s’affiche lorsque vous exécutez le script de sortie sur l’instance du serveur B :

Msg 15025, Niveau 16, État 1, Ligne 1
Le serveur principal « MonNomAccès » existe déjà.

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

Msg 15433, Niveau 16, État 1, Ligne 1 La valeur du paramètre sid est utilisée.

Par conséquent, vous devez effectuer les opérations suivantes :
1. Examinez attentivement le script de sortie.
2. Examinez le contenu de la sys.server_principals vue dans le instance sur le serveur B.
3. Traitez ces messages d’erreur en fonction des besoins.
  
  Dans SQL Server 2005, le SID d’un nom d’accès est utilisé pour implémenter l’accès de niveau base de données. Une connexion peut avoir des SID différents dans différentes bases de données sur un serveur. Dans ce cas, le nom d’accès peut uniquement accéder à la base de données qui contient le SID correspondant au SID de la vue sys.server_principals. 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 le nom d’accès de la base de données contenant un SID non concordant à l’aide de l'instruction DROP USER. Rajoutez ensuite le nom d’accès à l’aide de l’instruction CREATE USER.

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

Introduction

Plus d’informations

Étapes sur le serveur de destination (serveur B)

Remarques

References

Commentaires

Commentaires

Ressources supplémentaires