Transferowanie identyfikatorów logowania i haseł między wystąpieniami SQL Server

W tym artykule opisano sposób przenoszenia identyfikatorów logowania i haseł między różnymi wystąpieniami SQL Server uruchomionych w systemie Windows.

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 918992, 246133

Wprowadzenie

W tym artykule opisano sposób przenoszenia identyfikatorów logowania i haseł między różnymi wystąpieniami usługi Microsoft SQL Server.

Więcej informacji

W tym artykule serwery A i serwer B są różnymi serwerami.

Po przeniesieniu bazy danych z wystąpienia SQL Server na serwerze A do wystąpienia SQL Server na serwerze B użytkownicy mogą nie być w stanie zalogować się do bazy danych na serwerze B. Ponadto użytkownicy mogą otrzymać następujący komunikat o błędzie:

Logowanie użytkownika 'MyUser' nie powiodło się. (System Microsoft SQL Server Compact, błąd: 18456)

Ten problem występuje, ponieważ nie przetransferowaliśmy identyfikatorów logowania i haseł z wystąpienia SQL Server na serwerze A do wystąpienia SQL Server na serwerze B.

W celu przeniesienia loginów zastosuj jedną z następujących metod zależnie od systemu operacyjnego:

• Metoda 1. Resetowanie hasła na komputerze docelowym SQL Server (serwer B).

  Aby rozwiązać ten problem, zresetuj hasło na SQL Server komputerze, a następnie wyprowadź skrypt logowania.

  Uwaga

  Podczas resetowania hasła używany jest algorytm wyznaczania skrótu hasła.

• Metoda 2. Transferowanie identyfikatorów logowania i haseł na serwer docelowy (serwer B) przy użyciu skryptów generowanych na serwerze źródłowym (serwer A).

  1. Utwórz procedury składowane, które pomogą wygenerować skrypty niezbędne do transferu identyfikatorów logowania i ich haseł. W tym celu połącz się z serwerem A przy użyciu SQL Server Management Studio (SSMS) lub dowolnego innego narzędzia klienckiego i uruchom następujący skrypt:

       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
     

     Uwaga

     Ten skrypt tworzy dwie procedury składowane w głównej bazie danych. Procedury są nazywane sp_hexadecimal i sp_help_revlogin.

  2. W edytorze zapytań SSMS wybierz opcję Wyniki do tekstu.

  3. Uruchom następującą instrukcję w tym samym lub nowym oknie zapytania:

     EXEC sp_help_revlogin
     

  4. Skrypt wyjściowy generowany przez procedurę składowaną sp_help_revlogin to skrypt logowania. Ten skrypt logowania tworzy identyfikatory logowania z oryginalnym identyfikatorem zabezpieczeń (SID) i oryginalnym hasłem.

Kroki na serwerze docelowym (serwer B)

Połącz się z serwerem B przy użyciu dowolnego narzędzia klienckiego (takiego jak SSMS), a następnie uruchom skrypt wygenerowany w kroku 4 (dane wyjściowe sp_helprevlogin) z serwera A.

Uwagi

Przed uruchomieniem skryptu wyjściowego na wystąpieniu na serwerze B przejrzyj następujące informacje:

• Hasło może być skrótem w następujący sposób:

  • VERSION_SHA1: Ten skrót jest generowany przy użyciu algorytmu SHA1 i jest używany w SQL Server od 2000 do SQL Server 2008 R2.
  • VERSION_SHA2: Ten skrót jest generowany przy użyciu algorytmu SHA2 512 i jest używany w wersjach SQL Server 2012 i nowszych.

• Dokładnie przejrzyj skrypt wyjściowy. Jeśli serwer A i serwer B znajdują się w różnych domenach, musisz zmienić skrypt wyjściowy. Następnie należy zastąpić oryginalną nazwę domeny przy użyciu nowej nazwy domeny w CREATE LOGIN instrukcjach. Zintegrowane identyfikatory logowania, którym udzielono dostępu w nowej domenie, nie mają takiego samego identyfikatora SID jak identyfikatory logowania w oryginalnej domenie. W związku z tym użytkownicy są oddzieleni od tych identyfikatorów logowania. Aby uzyskać więcej informacji na temat sposobu rozwiązywania problemów z tymi oddzielonym użytkownikami, zobacz Rozwiązywanie problemów z oddzielonym użytkownikiem (SQL Server) i ALTER USER.
  Jeśli serwer A i serwer B znajdują się w tej samej domenie, jest używany ten sam identyfikator SID. Dlatego jest mało prawdopodobne, że użytkownicy zostaną oddzieleni.

• W skrypcie danych wyjściowych nazwy logowania są tworzone przy użyciu zaszyfrowanego hasła. Wynika to z argumentu HASHED w instrukcji CREATE LOGIN. Ten argument określa, że hasło wprowadzone po utworzeniu argumentu PASSWORD jest już skrótem.

• Domyślnie tylko członek stałej roli serwera sysadmin może uruchamiać instrukcję SELECT z widoku sys.server_principals. Jeśli członek stałej roli serwera sysadmin nie przyzna użytkownikom niezbędnych uprawnień, użytkownicy nie mogą tworzyć ani uruchamiać skryptu wyjściowego.

• Kroki opisane w tym artykule nie przesyłają domyślnych informacji o bazie danych dla określonego identyfikatora logowania. Dzieje się tak, ponieważ domyślna baza danych może nie zawsze istnieć na serwerze B. Aby zdefiniować domyślną bazę danych logowania, użyj ALTER LOGIN instrukcji , przekazując nazwę logowania i domyślną bazę danych jako argumenty.

• Sortuj zamówienia na serwerach źródłowych i docelowych:

  • Bez uwzględniania wielkości liter serwer A i serwer z rozróżnianiem wielkości liter B: kolejność sortowania serwera A może być uwzględniana wielkością liter, a kolejność sortowania serwera B może uwzględniać wielkość liter. W takim przypadku użytkownicy muszą wpisać hasła we wszystkich wielkich literach po przeniesieniu identyfikatorów logowania i haseł do wystąpienia na serwerze B.

  • Serwer Z rozróżnianiem wielkości liter A i bez uwzględniania wielkości liter B: Kolejność sortowania serwera A może uwzględniać wielkość liter, a kolejność sortowania serwera B może być bez uwzględniania wielkości liter. W takim przypadku użytkownicy nie mogą się zalogować przy użyciu identyfikatorów logowania i haseł przesyłanych do wystąpienia na serwerze B, chyba że spełniony jest jeden z następujących warunków:

    • Oryginalne hasła nie zawierają żadnych liter.
    • Wszystkie litery w oryginalnych hasłach są wielkimi literami.

  • Uwzględniana wielkość liter lub wielkość liter na obu serwerach: kolejność sortowania serwera A i serwera B może uwzględniać wielkość liter lub kolejność sortowania serwera A i serwera B może być uwzględniana wielkością liter. W takich przypadkach użytkownicy nie doświadczają problemu.

• Identyfikator logowania, który jest już w wystąpieniu na serwerze B, może mieć taką samą nazwę jak nazwa w skryptze danych wyjściowych. W takim przypadku podczas uruchamiania skryptu wyjściowego na wystąpieniu na serwerze B zostanie wyświetlony następujący komunikat o błędzie:

  Komunikat 15025, Poziom 16, Stan 1, Wiersz 1
  Jednostka serwera 'MyLogin' już istnieje.

  Podobnie logowanie, które już znajduje się w wystąpieniu na serwerze B, może mieć identyfikator SID, który jest taki sam jak identyfikator SID w skryptze danych wyjściowych. W takim przypadku podczas uruchamiania skryptu wyjściowego na wystąpieniu na serwerze B zostanie wyświetlony następujący komunikat o błędzie:

  Komunikat 15433, Poziom 16, Stan 1, Wiersz 1 Podany identyfikator sid parametru jest w użyciu.

  Z tego powodu należy wykonać następujące czynności:

  1. Dokładnie przejrzyj skrypt wyjściowy.

  2. Sprawdź zawartość sys.server_principals widoku w wystąpieniu na serwerze B.

  3. W razie potrzeby rozwiąż te komunikaty o błędach.

     W systemie SQL Server 2005 identyfikator SID logowania jest używany do implementowania dostępu na poziomie bazy danych. Identyfikatory logowania mogą mieć różne identyfikatory SID w różnych bazach danych na serwerze. W takim przypadku logowanie może uzyskać dostęp tylko do bazy danych, która ma identyfikator SID zgodny z identyfikatorem SID w widoku sys.server_principals. Ten problem może wystąpić, jeśli dwie bazy danych są łączone z różnych serwerów. Aby rozwiązać ten problem, ręcznie usuń identyfikator logowania z bazy danych z niezgodnością identyfikatora SID przy użyciu instrukcji DROP USER. Następnie dodaj ponownie identyfikator logowania przy użyciu instrukcji CREATE USER.

Informacje

• Rozwiązywanie problemów z oddzielonymi użytkownikami

• CREATE LOGIN (Transact-SQL)

• ALTER LOGIN (Transact-SQL)