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

Article
01/17/2025

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 base de connaissances d’origine : 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. Les instances 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 SQL Server ?

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

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.

Gardez à l’esprit que l’erreur 18456 se produit pour de nombreuses autres raisons. Pour plus d’informations sur ces causes et leurs résolutions, consultez MSSQLSERVER_18456.

Étapes de transfert des connexions

Pour transférer les noms d’accès, appliquez l’une des méthodes suivantes, selon votre situation.

Méthode 1 : 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

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 :

Connectez-vous au serveur A qui héberge le serveur SQL Server source.
Développez le nœud Bases de données .
Cliquez avec le bouton droit sur n’importe quelle base de données utilisateur et sélectionnez Tâches>générer des scripts.
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 base de données.
Sélectionnez Suivant pour ouvrir la page Définir les options de script.
Sélectionnez le bouton Avancé pour les options de connexion de script.
Dans la liste Avancé , recherchez les connexions de script, définissez l’option Sur True et sélectionnez OK.
Revenez à Définir les options de script sous Sélectionner comment les scripts doivent être enregistrés et sélectionner Ouvrir dans la nouvelle fenêtre de requête.
Sélectionnez Suivant deux fois, puis sélectionnez Terminer.
Recherchez la section dans le script qui contient des connexions. 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

Cela 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.
Appliquez le script de connexion à partir du script généré plus grand au serveur SQL Server de destination.
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.

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('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.

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.
Passez en revue et suivez les informations de la section Remarques avant de poursuivre l’implémentation des étapes sur le serveur de destination.
Une fois que vous avez implémenté toutes les étapes applicables de la section Remarques , connectez-vous au serveur de destination B à l’aide de n’importe quel outil client (comme SSMS).
Exécutez le script généré comme sortie du sp_helprevlogin serveur A.

Notes

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

Informations de hachage de mot de passe

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.
Dans le script de sortie, les noms d’accès sont créés à l’aide du mot de passe chiffré. Cela est dû à l’argument HASHED de l’instruction CREATE LOGIN . Cet argument spécifie que le mot de passe entré après que l’argument PASSWORD est déjà haché.

Comment gérer la modification des domaines

Vos serveurs source et de destination se trouvent-ils dans différents domaines ? 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 à l’aide du nouveau nom de domaine dans les CREATE LOGIN instructions. Les connexions intégrées qui sont accordées à l’accès 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 des 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.

Autorisations pour afficher et sélectionner toutes les connexions

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. Sauf si 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.

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

Comment gérer différents ordres de tri entre les serveurs source et de destination

Il peut y avoir des différences dans les ordres de tri entre les serveurs source et de destination, ou ils peuvent être identiques. Voici comment chaque scénario peut être traité :

Serveur non respectant la casse A et serveur respectant la casse B : l’ordre de tri du serveur A peut être sensible à la casse et l’ordre de tri 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.
Serveur sensible à la casse A et serveur respectant la casse B : l’ordre de tri du serveur A peut être sensible à la casse, et l’ordre de tri du serveur B peut être sensible à 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.
Respect de la casse ou non respectant 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 être sensible à la casse. Dans ces cas, les utilisateurs ne rencontrent pas de problème.

Comment gérer les connexions déjà existantes sur le serveur de destination

Le script est conçu pour vérifier si la connexion existe sur le serveur de destination et créer une connexion uniquement si ce n’est pas le cas. 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 le résoudre manuellement 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 La valeur du paramètre sid est utilisée.

Pour résoudre manuellement le problème, procédez comme suit :

Examinez attentivement le script de sortie.
Examinez le contenu de la sys.server_principals vue dans l’instance sur le serveur B.
Traitez ces messages d’erreur en fonction des besoins.

À compter de SQL Server 2005, le SID d’une connexion est utilisé pour gérer l’accès au niveau de la base de données. Parfois, une connexion peut avoir des SID différents lorsqu’ils sont mappés aux utilisateurs dans différentes bases de données. Ce problème peut se produire si les bases de données sont combinées manuellement à partir de différents serveurs. Dans ce cas, la connexion peut uniquement accéder à 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 manuellement l’utilisateur de base de données avec 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 et pour distinguer les serveurs des principaux de base de données, consultez CREATE USER et CREATE LOGIN.

Partager via