Comment faire pour transférer des noms d'accès et des mots de passe entre instances de SQL Server

Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Workgroup EditionMicrosoft SQL Server 2005 Developer Edition

INTRODUCTION


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

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

Pour plus d’informations sur la façon de transférer des informations de connexion et des mots de passe entre instances d’autres versions de SQL Server, cliquez sur le numéro d'article ci-dessous pour afficher l’article correspondant dans la Base de connaissances Microsoft :

246133 Comment faire pour transférer des connexions et des mots de passe entre instances de SQL Server qui exécutent des versions antérieures de SQL Server

Informations supplémentaires


Dans cet article, le serveur A et le serveur B sont des serveurs différents. 
 
Après le déplacement d’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 ne peuvent peut-être pas se connecter à la base de données sur le serveur B. De plus, le message d’erreur suivant peut s’afficher pour les utilisateurs :
Échec de la connexion pour l'utilisateur « Mon_utilisateur ». (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.

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

Méthode 1 : Réinitialisez le mot de passe sur l’ordinateur de destination de SQL Server (Serveur B)

Pour résoudre ce problème, réinitialisez le mot de passe dans l’ordinateur 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 : Transfert de connexions et de mots de passe vers le serveur de destination (Serveur A) à l’aide de scripts générés sur le serveur source (Serveur B)

Pour créer un journal dans un script qui possède un mot de passe vide, procédez comme suit :
  1. Sur le serveur A, démarrez SQL Server Management Studio, puis connectez-vous à l'instance de SQL Server à partir de laquelle vous avez déplacé la base de données.
  2. Ouvrez une Nouvelle fenêtre d'éditeur de requête, puis 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


    Remarque : ce script crée deux procédures stockées dans la base de données principale. Les procédures sont nommées sp_hexadecimal et sp_help_revlogin.
  3. Exécutez l’instruction suivante dans la même fenêtre de requête ou une nouvelle fenêtre de requête : 
    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. Le script d'ouverture de session d'origine crée les noms d'accès qui comportent l'identificateur de sécurité (SID) d'origine et le mot de passe d'origine.

 Étapes sur le serveur de destination (Serveur B) :

  1. Sur le serveur B, démarrez SQL Server Management Studio, puis connectez-vous à l'instance de SQL Server vers laquelle vous avez déplacé la base de données.

    Important : avant de passer à l’étape 2, lisez les informations de la section « Remarques » ci-dessous.
  2. Ouvrez une nouvelle fenêtre Éditeur de requête, puis exécutez le script de sortie généré à l’étape 2 de la procédure précédente.

Remarques

Lisez les informations suivantes avant d'exécuter le script de sortie sur l'instance sur le serveur B :

  • Le mot de passe peut être haché selon les façons suivantes :
    • VERSION_SHA1 : Ce hachage est généré à l’aide de l’algorithme SHA1 et est utilisé depuis SQL Server 2000 jusqu'à 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 versions ultérieures.
  • Lisez attentivement le script de sortie. Si le serveur A et le serveur B figurent dans des domaines différents, vous devez modifier le script de sortie. Vous devez ensuite remplacer le nom de domaine d’origine par le nouveau nom de domaine dans les déclarations CREATE LOGIN. Les noms d'accès intégrés qui disposent de droits d'accès dans le nouveau domaine n'ont pas le même SID que les noms d'accès du domaine d'origine. Par conséquent, les utilisateurs se retrouvent orphelins de ces noms d’accès. Pour plus d'informations sur la façon de résoudre ce problème d'utilisateurs orphelins, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.

    240872 Comment résoudre les problèmes d’autorisation lorsque vous déplacez une base de données exécutant SQL Server

    Si le serveur A et le serveur B figurent 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 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 de serveur fixe sysadmin n’accorde les autorisations nécessaires aux utilisateurs, ces derniers ne peuvent pas créer ou exécuter le script de sortie.
  • Les étapes de cet article ne permettent pas de transférer les informations de base de données par défaut pour un nom d'accès particulier. Ceci 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 pour un nom d'accès, utilisez l'instruction ALTER LOGIN en transmettant le nom d'accès et la base de données par défaut comme arguments.
  • Ordres de tri sur les serveurs source et de destination :
    • Serveur A insensible à la casse et serveur B sensible à la casse : L’ordre de tri du serveur A peut être insensible à la casse et celui du serveur B peut être sensible à 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.
    • Le serveur A sensible à la casse et le serveur B insensible à la casse : L’ordre de tri du serveur A peut être sensible à la casse et celui du serveur B peut être insensible à 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 avez transférés à l’instance sur le serveur B, à moins que l’une des conditions suivantes soit vérifiée :
      • Les mots de passe d'origine ne contiennent pas de lettres.
      • Toutes les lettres des mots de passe d’origine sont en majuscules.
    • Sensible ou insensible à 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 du tri des serveurs A et B peut être insensible à la casse. Dans tous ces cas, les utilisateurs ne rencontrent aucun problème.
  • Un nom d’accès qui figure déjà sur l’instance sur le serveur B doit avoir le même nom que celui qui se trouve 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 sur le serveur B :
    Msg 15025, Niveau 16, État 1, Ligne 1
    Le serveur principal « MonNomAccès » existe déjà.
    De même, un nom d'accès qui figure déjà sur l'instance sur le serveur B doit avoir le même SID que celui qui se trouve 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 sur le 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. Lisez attentivement le script de sortie.
    2. Examinez le contenu de la vue sys.server_principals dans l’instance sur le serveur B.
    3. Résolvez ces messages d’erreur comme il convient.

      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. Un nom d’accès peut avoir deux SID différents dans des bases de données différentes sur un serveur. Dans ce cas, le nom d’accès peut uniquement accéder à la base de données qui contient le SID qui correspond 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 deux serveurs différents. Pour résoudre ce problème, supprimez manuellement le nom d'accès de la base de données qui contient un SID non concordant à l'aide de l'instruction DROP USER. Ajoutez ensuite de nouveau le nom d'accès à l'aide de l'instruction CREATE USER.
  • Si vous essayez de créer une nouvelle connexion SQL Server 2012 à l’aide d’une connexion Server 2000 pré-SQL scriptée, vous recevrez le message d’erreur suivant :

    Msg 15021, Niveau 16, État 2, Ligne 1
    Valeur non valide donnée pour le paramètre MOT DE PASSE. Spécifiez une valeur de paramètre valide.

    Remarque : vous recevez cette erreur dans SQL Server 2012 en raison du hachage de mot de passe 16 octets fourni pour les instructions CREATE LOGIN et ALTER LOGIN.

    Pour résoudre ce problème sur un serveur qui exécute SQL Server 2012, créez un identifiant qui possède un mot de passe vide. Pour cela, exécutez le script suivant :
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
    Après avoir créé la connexion avec mot de passe vide, l’utilisateur peut modifier le mot de passe lors de la prochaine tentative de connexion.

Méthode 3 : Connectez-vous à l’aide du mot de passe pré-SQL Server 2000

Remarque : cette méthode n’est pertinente que si vous migrez SQL Server 2000 vers une version plus récente de SQL Server.

Dans ce cas, demandez à l’utilisateur de se connecter au serveur qui exécute SQL Server à l’aide de la connexion pré-SQL Server 2000.

Remarque : le hachage de mot de passe est mis à jour automatiquement lorsque l’utilisateur se connecte à l’aide du mot de passe pré-SQL Server 2000.

Références


Pour plus d’informations sur le dépannage des utilisateurs orphelins, consultez le site web Dépanner les utilisateurs orphelins Microsoft Developer Network (MSDN).

Pour plus d’informations sur l’instruction CREATE LOGIN, consultez le site web CREATE LOGIN (Transact-SQL) MSDN.

Pour plus d’informations sur l’instruction ALTER LOGIN, consultez le site web MSDN ALTER LOGIN (Transact-SQL).