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

Article
09/06/2024

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

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.

Partager via

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

Ressources supplémentaires