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

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

Résumé

Cet article explique comment transférer des connexions et des mots de passe SQL Server entre des instances de Microsoft SQL Server s’exécutant sur Windows. Utilisez ces procédures pendant SQL Server migration, restauration ou scénarios de haute disponibilité pour conserver l’authentification utilisateur intacte et éviter les utilisateurs de base de données orphelins sur l’instance de destination. Les instances source et de destination peuvent se trouver sur le même serveur ou sur différents serveurs, et leurs versions peuvent différer.

Pourquoi transférer des connexions entre des instances de SQL Server

Lorsque vous déplacez une base de données vers un nouveau serveur, par exemple lors d’une migration ou d’une restauration, les utilisateurs de la base de données se déplacent avec la base de données, mais les connexions au niveau du serveur correspondantes peuvent ne pas exister sur la nouvelle instance. Cette incompatibilité produit des utilisateurs orphelins. Le transfert de connexions et de mots de passe conserve l’authentification utilisateur intacte et empêche les interruptions de connexion après le déplacement d’une base de données.

Après avoir déplacé une base de données d’une instance SQL Server sur le serveur A vers une instance SQL Server sur le serveur B, les utilisateurs peuvent ne pas pouvoir se connecter au serveur de 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, car les connexions de l’instance SQL Server sur le serveur A n’existent pas dans l’instance SQL Server sur le serveur B.

L’erreur 18456 peut également se produire pour plusieurs autres raisons. Pour plus d’informations sur les différentes causes et leurs résolutions, consultez MSSQLSERVER_18456.

Méthodes pour transférer des connexions entre des instances SQL Server

Pour transférer des connexions, utilisez l’une des méthodes suivantes, en fonction de votre situation.

Générer des scripts de connexion dans SSMS et réinitialiser les mots de passe sur la destination

Vous pouvez générer des scripts de connexion dans SQL Server Management Studio (SSMS) à l’aide de l’option Générer des scripts pour une base de données.

Pour générer des scripts via SSMS sur le serveur source et réinitialiser manuellement les mots de passe pour les connexions SQL Server sur le serveur de destination, procédez comme suit :

  1. Connectez-vous au serveur A qui héberge le serveur SQL Server source.

  2. Développez le nœud Bases de données .

  3. Sélectionnez et maintenez la touche enfoncée (ou cliquez avec le bouton droit) sur n’importe quelle base de données utilisateur, puis sélectionnez Tâches>générer des scripts.

  4. La page Introduction s’ouvre. Sélectionnez Suivant pour ouvrir la page Choisir des objets . Sélectionnez Générer un script de la base de données entière et de tous les objets de la base de données.

  5. Sélectionnez Suivant pour ouvrir la page Définir les options de script.

  6. Sélectionnez le bouton Avancé pour les options de connexion de script.

  7. Dans la liste Avancé, recherchez Connexions par script, définissez l’option sur True et sélectionnez OK.

  8. Revenez à Définir les options de script, sous Sélectionner la façon dont les scripts doivent être enregistrés , sélectionnez Ouvrir dans la nouvelle fenêtre de requête.

  9. Sélectionnez Suivant deux fois, puis sélectionnez Terminer.

  10. Recherchez la section du script qui contient des identifiants de connexion. En règle générale, le script généré contient du texte avec le commentaire suivant au début de cette section :

    /* For security reasons the login is created disabled and with a random password. */

    Note

    Ce commentaire indique que les connexions d’authentification SQL Server sont générées avec un mot de passe aléatoire et sont désactivées par défaut. Vous devez réinitialiser le mot de passe et réactiver ces connexions sur le serveur de destination.

  11. Appliquez le script de connexion à partir du script généré plus grand au serveur SQL Server de destination.

  12. Pour toutes les connexions d’authentification SQL Server, réinitialisez le mot de passe sur le serveur SQL Server de destination et réactivez ces connexions.

Transférer des connexions et des mots de passe à l’aide de sp_help_revlogin

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

    USE [master]
GO
IF OBJECT_ID('dbo.sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE dbo.sp_hexadecimal
GO
CREATE PROCEDURE dbo.sp_hexadecimal
    @binvalue [varbinary](256)
    ,@hexvalue [nvarchar] (514) OUTPUT
AS
BEGIN
    DECLARE @i [smallint]
    DECLARE @length [smallint]
    DECLARE @hexstring [nchar](16)
    SELECT @hexvalue = N'0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = N'0123456789ABCDEF'
    WHILE (@i < =  @length)
    BEGIN
        DECLARE @tempint   [smallint]
        DECLARE @firstint  [smallint]
        DECLARE @secondint [smallint]
        SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
        SELECT @firstint = FLOOR(@tempint / 16)
        SELECT @secondint = @tempint - (@firstint * 16)
        SELECT @hexvalue = @hexvalue
            + SUBSTRING(@hexstring, @firstint  + 1, 1)
            + SUBSTRING(@hexstring, @secondint + 1, 1)
        SELECT @i = @i + 1
    END
END
GO
IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE dbo.sp_help_revlogin
GO
CREATE PROCEDURE dbo.sp_help_revlogin
    @login_name [sysname] = NULL
AS
BEGIN
    DECLARE @name                  [sysname]
    DECLARE @type                  [nvarchar](1)
    DECLARE @hasaccess             [int]
    DECLARE @denylogin             [int]
    DECLARE @is_disabled           [int]
    DECLARE @PWD_varbinary         [varbinary](256)
    DECLARE @PWD_string            [nvarchar](514)
    DECLARE @SID_varbinary         [varbinary](85)
    DECLARE @SID_string            [nvarchar](514)
    DECLARE @tmpstr                [nvarchar](4000)
    DECLARE @is_policy_checked     [nvarchar](3)
    DECLARE @is_expiration_checked [nvarchar](3)
    DECLARE @Prefix                [nvarchar](4000)
    DECLARE @defaultdb             [sysname]
    DECLARE @defaultlanguage       [sysname]
    DECLARE @tmpstrRole            [nvarchar](4000)
    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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] NOT LIKE '##%'
            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 for ' + QUOTENAME(@login_name) + N'. */'
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN - 1
    END
    SET @tmpstr = N'/* sp_help_revlogin script
** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
*/'
    PRINT @tmpstr
    WHILE (@@fetch_status <> - 1)
    BEGIN
        IF (@@fetch_status <> - 2)
        BEGIN
            PRINT ''
            SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
            PRINT @tmpstr
            SET @tmpstr = N'IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE [name] = N''' + @name + N'''
    )
BEGIN'
            PRINT @tmpstr
            IF @type IN ('G','U') -- NT-authenticated Group/User
            BEGIN -- NT authenticated account/group 
                SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
    FROM WINDOWS
    WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
            END
            ELSE
            BEGIN -- SQL Server authentication
                -- obtain password and sid
                SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
    WITH PASSWORD = ' + @PWD_string + N' HASHED
        ,SID = ' + @SID_string + N'
        ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)

                IF @is_policy_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_POLICY = ' + @is_policy_checked
                END

                IF @is_expiration_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_EXPIRATION = ' + @is_expiration_checked
                END
            END
            IF (@denylogin = 1)
            BEGIN -- login is denied access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
            END
            SET @Prefix =
                NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
            SET @tmpstrRole = N''
            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
            FROM (
                SELECT
                    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
            IF @tmpstr <> '' PRINT @tmpstr
            IF @tmpstrRole <> '' 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

    Note

    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.

  2. Dans l’éditeur de requête SSMS, sélectionnez l’option Résultats en texte .

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

    EXEC sp_help_revlogin

  4. 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.

  5. Passez en revue et suivez les informations dans les considérations supplémentaires lors du transfert de la section connexions SQL Server avant de poursuivre l’implémentation des étapes sur le serveur de destination.

  6. Après avoir effectué toutes les étapes applicables de la section Considérations supplémentaires lors du transfert des connexions SQL Server, connectez-vous au serveur de destination B à l’aide de n’importe quel outil client (comme SSMS).

  7. Exécutez le script généré comme sortie du sp_helprevlogin serveur A.

Considérations supplémentaires lors du transfert de connexions SQL Server

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

Comprendre le hachage de mot de passe dans les transferts de connexion SQL Server

SQL Server hache les mots de passe de la façon suivante :

  • VERSION_SHA1: utilise l’algorithme SHA1. SQL Server 2000 à SQL Server 2008 R2 utilisent ce hachage. Ces versions ne bénéficient plus du support standard ni du support étendu ; vous ne devriez donc rencontrer des valeurs de hachage VERSION_SHA1 que lors de la migration à partir d’anciennes instances.
  • VERSION_SHA2: utilise l’algorithme SHA2-512. SQL Server 2012 et les versions ultérieures, y compris les versions actuellement prises en charge, utilisent ce hachage.

Le script de sortie crée les connexions à l’aide du mot de passe chiffré. L’argument HASHED de l’instruction CREATE LOGIN provoque ce comportement. Cet argument indique que le mot de passe entré après que l’argument PASSWORD est déjà haché.

Gérer les modifications de domaine pendant les transferts de connexion SQL Server

Si vos serveurs source et de destination se trouvent dans différents domaines, passez en revue attentivement le script de sortie. Modifiez le script pour remplacer le nom de domaine d’origine par le nouveau nom de domaine dans les CREATE LOGIN instructions. Les connexions intégrées accordées à l’accès dans le nouveau domaine ne partagent pas le même SID que les connexions dans le domaine d’origine, de sorte que les utilisateurs deviennent orphelins à partir de ces connexions. Pour corriger les utilisateurs orphelins, consultez Résoudre les problèmes liés aux 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, les utilisateurs ne sont pas orphelins.

Autorisations requises pour afficher et sélectionner des connexions SQL Server

Par défaut, seuls les membres du rôle serveur fixe sysadmin peuvent exécuter une SELECT instruction sur la sys.server_principals vue. Sauf si un administrateur système accorde les autorisations nécessaires à d’autres utilisateurs, ces utilisateurs ne peuvent pas créer ou exécuter le script de sortie.

Le paramètre de base de données par défaut n’est pas scripté et transféré

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. Cette limitation existe, car 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’instruction ALTER LOGIN en transmettant le nom de connexion et la base de données par défaut en tant qu’arguments.

Gérer les différences d’ordre de tri dans les transferts de connexion SQL Server

Les serveurs source et de destination peuvent avoir des ordres de tri différents, ou ils peuvent utiliser le même ordre de tri. Voici comment résoudre chaque scénario :

  • Serveur A insensible à la casse et serveur B sensible à la casse : l'ordre de tri du serveur A est insensible à la casse, et l'ordre de tri du serveur B est 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.

  • Serveur sensible à la casse A et serveur insensible à la casse B : L’ordre de tri du serveur A est sensible à la casse et l’ordre de tri du serveur B est insensible à 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 l’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.

  • Sensible à la casse ou insensible à la casse sur les deux serveurs : les ordres de tri des serveurs A et B sont sensibles à la casse, ou ils sont tous deux insensibles à la casse. Dans ces cas, les utilisateurs ne rencontrent pas de problème.

Résoudre les conflits avec les connexions existantes sur le serveur de destination

Le script vérifie si la connexion existe sur le serveur de destination et crée une connexion uniquement si elle n’existe pas. Toutefois, si vous recevez le message d’erreur suivant lorsque vous exécutez le script de sortie sur l’instance sur le serveur B, vous devez résoudre manuellement le conflit en suivant les étapes décrites dans cette section.

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 l’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. Le paramètre sid fourni est déjà utilisé.

Pour résoudre le conflit manuellement, procédez comme suit :

  1. Examinez attentivement le script de sortie.
  2. Examinez le contenu de la sys.server_principals vue dans l’instance sur le serveur B.
  3. Effectuez l’action appropriée pour chaque erreur, par exemple en supprimant ou en renommant la connexion en conflit sur le serveur B, ou en supprimant l’instruction dupliquée CREATE LOGIN du script de sortie avant de la réexécuter.

Dans SQL Server, le SID d’une connexion régit l’accès au niveau de la base de données. Une connexion peut avoir différents SID lorsqu’elle est mappée aux utilisateurs dans différentes bases de données, ce qui peut se produire si vous combinez manuellement des bases de données à partir de différents serveurs. Dans ce cas, la connexion peut utiliser uniquement la base de données où le SID du principal de la base de données correspond au SID dans la sys.server_principals vue. Pour résoudre ce problème, supprimez l’utilisateur de base de données qui a le SID incompatible à l’aide de l’instruction DROP USER . Ensuite, ajoutez à nouveau l’utilisateur avec l’instruction CREATE USER et mappez-la à la connexion correcte (principal du serveur).

Pour plus d’informations sur les principaux de serveur et de base de données, consultez CREATE USER et CREATE LOGIN.

Scénarios avancés et résolution des problèmes

Si vous continuez à voir les échecs de connexion après le transfert des connexions, vérifiez les éléments suivants :

Contenu connexe

  • Last updated on