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 programu Microsoft SQL Server uruchomionym w systemie Windows. Wystąpienia mogą znajdować się na tym samym serwerze lub na różnych serwerach, a ich wersje mogą się różnić.

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

Dlaczego warto przenieść identyfikatory logowania między wystąpieniami programu SQL Server?

W tym artykule serwer A i serwer B są serwerami.

Po przeniesieniu bazy danych z wystąpienia programu SQL Server na serwerze A do wystąpienia programu SQL Server na serwerze B użytkownicy mogą nie zalogować się do serwera 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ż identyfikatory logowania z wystąpienia programu SQL Server na serwerze A nie istnieją w wystąpieniu programu SQL Server na serwerze B.

Pamiętaj, że błąd 18456 występuje z wielu innych powodów. Aby uzyskać więcej informacji na temat tych przyczyn i ich rozwiązań, zobacz MSSQLSERVER_18456.

Procedura przenoszenia identyfikatorów logowania między wystąpieniami programu SQL Server

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

Metoda 1. Generowanie skryptów za pośrednictwem programu SSMS na serwerze źródłowym i ręczne resetowanie haseł dla identyfikatorów logowania programu SQL Server na serwerze docelowym

Skrypty logowania można wygenerować w programie SQL Server Management Studio (SSMS) przy użyciu opcji Generuj skrypty dla bazy danych.

Aby wygenerować skrypty za pośrednictwem programu SSMS na serwerze źródłowym i ręcznie zresetować hasła logowania programu SQL Server na serwerze docelowym, wykonaj następujące kroki:

1. Połącz się z serwerem A hostujący źródłowy program SQL Server.

2. Rozwiń węzeł Bazy danych.

3. Kliknij prawym przyciskiem myszy dowolną bazę danych użytkownika i wybierz pozycję Zadania>Generuj skrypty.

4. Zostanie otwarta strona Wprowadzenie . Wybierz przycisk Dalej , aby otworzyć stronę Wybierz obiekty . Wybierz pozycję Skrypt całej bazy danych i wszystkich obiektów bazy danych.

5. Wybierz przycisk Dalej , aby otworzyć stronę Ustaw opcje skryptów.

6. Wybierz przycisk Zaawansowane w obszarze Opcje logowania skryptu.

7. Na liście Zaawansowane znajdź pozycję Identyfikatory logowania skryptu, ustaw opcję True i wybierz przycisk OK.

8. Wróć do pozycji Ustaw opcje skryptów w obszarze Wybierz sposób zapisywania skryptów i wybierz pozycję Otwórz w nowym oknie zapytania.

9. Wybierz przycisk Dalej dwa razy, a następnie wybierz pozycję Zakończ.

10. Znajdź sekcję w skrycie zawierającym identyfikatory logowania. Zazwyczaj wygenerowany skrypt zawiera tekst z następującym komentarzem na początku tej sekcji:

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

    Uwaga 16.

    Oznacza to, że identyfikatory logowania uwierzytelniania programu SQL Server są generowane przy użyciu losowego hasła i są domyślnie wyłączone. Musisz zresetować hasło i ponownie włączyć te identyfikatory logowania na serwerze docelowym.

11. Zastosuj skrypt logowania z większego wygenerowanego skryptu do docelowego programu SQL Server.

12. W przypadku wszystkich logowań uwierzytelniania programu SQL Server zresetuj hasło w docelowym programie SQL Server i ponownie włącz te identyfikatory logowania.

Metoda 2. Przenoszenie identyfikatorów logowania i haseł na serwer docelowy (serwer B) przy użyciu skryptów wygenerowanych 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('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
   

   Uwaga 16.

   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.

5. Przejrzyj i postępuj zgodnie z informacjami w sekcji Dodatkowe zagadnienia dotyczące przenoszenia identyfikatorów logowania programu SQL Server przed przystąpieniem do implementowania kroków na serwerze docelowym.

6. Po zaimplementowaniu wszelkich odpowiednich kroków z sekcji Dodatkowe zagadnienia dotyczące przenoszenia identyfikatorów logowania programu SQL Server nawiąż połączenie z serwerem docelowym B przy użyciu dowolnego narzędzia klienckiego (takiego jak program SSMS).

7. Uruchom skrypt wygenerowany jako dane wyjściowe sp_helprevlogin z serwera A.

Dodatkowe zagadnienia dotyczące przenoszenia identyfikatorów logowania programu SQL Server

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

Zrozumienie haszowania haseł w transferach logowania w SQL Server

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

• 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 argumencie PASSWORD jest już skrótem.

Obsługa zmian domeny podczas transferów logowania programu SQL Server

Czy serwery źródłowe i docelowe znajdują się w różnych domenach? 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 instrukcjach CREATE LOGIN . Zintegrowane identyfikatory logowania, którym udzielono dostępu w nowej domenie, nie mają tego samego identyfikatora SID co 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 rozwiązywania problemów z tymi oddzielonych użytkownikami, zobacz Rozwiązywanie problemów z oddzielonych użytkownikami (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.

Wymagane uprawnienia do wyświetlania i wybierania identyfikatorów logowania programu SQL Server

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 przyznaje użytkownikom niezbędnych uprawnień, użytkownicy nie mogą tworzyć ani uruchamiać skryptu wyjściowego.

Domyślne ustawienie bazy danych nie jest skryptowe i przesyłane

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.

Zarządzanie różnicami kolejności sortowania w transferach logowania programu SQL Server

Mogą istnieć różnice w kolejności sortowania między serwerami źródłowymi i docelowymi lub mogą być takie same. Oto jak można rozwiązać każdy scenariusz:

• Bez uwzględniania wielkości liter serwer A i serwer z uwzględnieniem wielkości liter B: kolejność sortowania serwera A może być bez uwzględniania wielkości liter, a kolejność sortowania serwera B może mieć 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 uwzględniający wielkość liter A i serwer bez uwzględniania wielkości liter B: kolejność sortowania serwera A może mieć wielkość liter, a kolejność sortowania serwera B może być bez uwzględniania wielkości liter. W takim przypadku użytkownicy nie mogą logować się 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 zarówno serwera A, jak i serwera B może mieć wielkość liter, lub kolejność sortowania zarówno serwera A, jak i serwera B, może mieć wielkość liter. W takich przypadkach użytkownicy nie mają problemu.

Rozwiąż konflikty z istniejącymi loginami SQL Server na serwerze docelowym

Skrypt został zaprojektowany tak, aby sprawdzić, czy logowanie istnieje na serwerze docelowym i utworzyć identyfikator logowania tylko wtedy, gdy nie. Jeśli jednak podczas uruchamiania skryptu wyjściowego na wystąpieniu na serwerze B zostanie wyświetlony następujący komunikat o błędzie, należy go ręcznie rozwiązać, wykonując kroki opisane w tej sekcji.

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

Podobnie identyfikator logowania, który znajduje się już w wystąpieniu na serwerze B, może mieć identyfikator SID, który jest taki sam jak identyfikator SID w skry skryptzie wyjściowym. 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.

Aby ręcznie rozwiązać ten problem, wykonaj następujące kroki:

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.

Począwszy od programu SQL Server 2005, identyfikator SID logowania służy do zarządzania dostępem na poziomie bazy danych. Czasami identyfikator logowania może mieć różne identyfikatory SID, gdy są mapowane na użytkowników w różnych bazach danych. Ten problem może wystąpić, jeśli bazy danych są ręcznie łączone z różnych serwerów. W takich przypadkach identyfikator logowania może uzyskać dostęp tylko do bazy danych, w której identyfikator SID podmiotu zabezpieczeń bazy danych jest zgodny z identyfikatorem sys.server_principals SID w widoku. Aby rozwiązać ten problem, ręcznie usuń użytkownika bazy danych z niezgodnym identyfikatorem SID przy użyciu instrukcji DROP USER . Następnie ponownie dodaj użytkownika za pomocą instrukcji CREATE USER i zamapuj go na poprawną nazwę logowania (podmiot zabezpieczeń serwera).

Aby uzyskać więcej informacji i odróżnić serwery od podmiotów zabezpieczeń bazy danych, zobacz CREATE USER (TWORZENIE UŻYTKOWNIKA ) i CREATE LOGIN (TWORZENIE IDENTYFIKATORA LOGOWANIA).

Informacje

• Rozwiązywanie problemów związanych z użytkownikami oddzielonymi
• UTWÓRZ LOGOWANIE (Transact-SQL)
• ALTER LOGIN (Transact-SQL)
• Obiekty skryptów w programie SQL Server Management Studio