Передача имен входа и паролей между экземплярами SQL Server

В этой статье описывается, как передавать имена входа и пароли между различными экземплярами SQL Server под управлением Windows.

Исходная версия продукта: SQL Server
Исходный номер базы знаний: 918992, 246133

Введение

В этой статье описывается способ передачи имен входа и паролей между различными экземплярами Microsoft SQL Server. Экземпляры могут находиться на одном сервере или на разных серверах, а их версии могут отличаться.

Зачем передавать имена входа между экземплярами SQL Server?

В этой статье сервер A и сервер B являются серверами.

После перемещения базы данных из экземпляра SQL Server на сервер A на экземпляр SQL Server на сервер B пользователи могут не входить на сервер базы данных на сервере B. Кроме того, пользователи могут получить следующее сообщение об ошибке:

Сбой входа для пользователя "MyUser". (Microsoft SQL Server, ошибка: 18456)

Эта проблема возникает из-за того, что имена входа из экземпляра SQL Server на сервере A не существуют в экземпляре SQL Server на сервере B.

Помните, что ошибка 18456 возникает по многим другим причинам. Дополнительные сведения об этих причинах и их решениях см . в MSSQLSERVER_18456.

Действия по передаче имен входа

Чтобы передать имена входа, воспользуйтесь одним из описанных ниже способов в зависимости от ситуации.

Метод 1. Создание скриптов с помощью SSMS на исходном сервере и сброс паролей для входа SQL Server на целевом сервере вручную

Скрипты входа можно создать в SQL Server Management Studio (SSMS) с помощью параметра "Создать скрипты" для базы данных.

Чтобы создать скрипты с помощью SSMS на исходном сервере и вручную сбросить пароли для входа SQL Server на целевом сервере, выполните следующие действия.

1. Подключитесь к серверу A, на котором размещен исходный SQL Server.

2. Разверните узел Базы данных .

3. Щелкните правой кнопкой мыши любую пользовательную базу данных и выберите "Задачи>создания скриптов".

4. Откроется страница Введение. Нажмите кнопку "Рядом", чтобы открыть страницу "Выбор объектов". Выберите Внести в скрипт всю базу данных целиком вместе со всеми объектами.

5. Нажмите кнопку Далее, чтобы открыть страницу Задание параметров скриптов.

6. Нажмите кнопку "Дополнительно" для параметров входа в скрипт.

7. В списке "Дополнительно" найдите имена входа скриптов, установите значение True и нажмите кнопку "ОК".

8. Вернитесь в раздел " Настройка параметров скриптов" в разделе "Выбор способа сохранения скриптов" и нажмите кнопку "Открыть" в новом окне запроса.

9. Дважды нажмите кнопку "Далее ", а затем нажмите кнопку "Готово".

10. Найдите раздел в скрипте, который содержит имена входа. Как правило, созданный скрипт содержит текст со следующим комментарием в начале этого раздела:

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

    Примечание.

    Это означает, что имена входа проверки подлинности SQL Server создаются случайным паролем и отключены по умолчанию. Необходимо сбросить пароль и повторно включить эти имена входа на целевом сервере.

11. Примените скрипт входа из более крупного созданного скрипта к целевому SQL Server.

12. Для всех имен входа проверки подлинности SQL Server сбросьте пароль в целевом SQL Server и повторно включите эти имена входа.

Метод 2. Передача имен входа и паролей на конечный сервер (Сервер B) с помощью скриптов, созданных на исходном сервере (Сервер A)

1. Создайте хранимые процедуры, которые помогут создать необходимые сценарии для передачи имен входа и паролей. Для этого подключитесь к серверу A с помощью SQL Server Management Studio (SSMS) или любого другого клиентского средства и запустите следующий сценарий:

   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
   

   Примечание.

   Этот сценарий создает две хранимые процедуры в главной базе данных. Эти процедуры называются sp_hexadecimal и sp_help_revlogin.

2. В редакторе запросов SSMS выберите параметр Результаты в текст.

3. Выполните следующую инструкцию в том же или новом окне запроса:

   EXEC sp_help_revlogin
   

4. Сценарий вывода, который создает хранимая процедура sp_help_revlogin, является сценарием входа. Этот сценарий входа создает имена входа с исходным идентификатором безопасности (SID) и исходным паролем.

5. Просмотрите и следуйте сведениям в разделе "Примечания" , прежде чем продолжить реализацию шагов на целевом сервере.

6. После реализации любых применимых шагов из раздела "Примечания " подключитесь к целевому серверу B с помощью любого клиентского средства (например, SSMS).

7. Запустите скрипт, созданный в качестве выходных sp_helprevlogin данных сервера A.

Замечания

Перед запуском сценария вывода на экземпляре на сервере B просмотрите следующие сведения:

Хэширование паролей

• Хэширование пароля может выполняться следующими способами:

  • VERSION_SHA1: этот хэш создается с помощью алгоритма SHA1 и используется в SQL Server, начиная с версии от 2000 до 2008 R2.
  • VERSION_SHA2: этот хэш создается с помощью алгоритма SHA2 512 и используется в SQL Server 2012 и более поздних версиях.

• В сценарии вывода имена входа создаются с помощью зашифрованного пароля. Это связано с аргументом HASHED в инструкции CREATE LOGIN . Этот аргумент указывает, что пароль, введенный после PASSWORD хэшированного аргумента.

Обработка изменений доменов

Являются ли исходные и целевые серверы в разных доменах? Внимательно просмотрите сценарий вывода. Если сервер A и сервер B находятся в разных доменах, необходимо изменить сценарий вывода. Затем необходимо заменить исходное доменное имя с помощью нового доменного имени в CREATE LOGIN инструкциях. Интегрированные имена входа, которым предоставлен доступ в новом домене, не имеют того же идентификатора безопасности, что и имена входа в исходном домене. Таким образом, пользователи будут потеряны из-за этих имен входа. Дополнительные сведения о том, как устранить этих потерянных пользователей, см. в разделе "Устранение неполадок потерянных пользователей" (SQL Server) и ALTER USER.

Если сервер A и сервер B находятся в одном домене, используется один и тот же идентификатор безопасности. Поэтому пользователи вряд ли будут потеряны.

Разрешения для просмотра и выбора всех имен входа

По умолчанию только член предопределенной роли сервера sysadmin может выполнять инструкцию SELECT из представления sys.server_principals. Если член предопределенной роли сервера sysadmin не предоставляет пользователям необходимые разрешения, пользователи не могут создать или запустить выходной скрипт.

Параметр базы данных по умолчанию не выполняется скриптом и не передается

Действия, описанные в этой статье, не передают сведения о базе данных по умолчанию для определенного имени входа. Это связано с тем, что база данных по умолчанию может не всегда существовать на сервере B. Чтобы определить базу данных по умолчанию для входа, используйте ALTER LOGIN инструкцию, передав имя входа и базу данных по умолчанию в качестве аргументов.

Как работать с различными заказами сортировки между исходными и целевыми серверами

Могут быть различия в заказах сортировки между исходными и целевыми серверами, или они могут быть одинаковыми. Вот как можно решить каждый сценарий:

• Нечувствительный к регистру сервер A и сервер с учетом регистра B: порядок сортировки сервера A может быть не учитывает регистр, а порядок сортировки сервера B может быть учитывает регистр. В этом случае пользователи должны ввести пароли заглавными буквами после передачи имен входа и паролей экземпляру на сервере B.

• Сервер С учетом регистра А и нечувствительный к регистру сервер B: порядок сортировки сервера A может быть учитывает регистр, а порядок сортировки сервера B может быть не учитывает регистр. В этом случае пользователи не могут войти с помощью имен входа и паролей, передаваемых экземпляру на сервере B, если только одно из следующих условий не имеет значения true:

  • Исходные пароли не содержат букв.
  • Исходные пароли содержат только прописные буквы.

• Учитывает регистр или не учитывает регистр на обоих серверах: порядок сортировки сервера А и сервера B может быть учитывает регистр, или порядок сортировки сервера A и сервера B может быть нечувствительным к регистру. В таких случаях пользователи не сталкиваются с проблемой.

Как работать с именами входа, уже существующими на целевом сервере

Скрипт предназначен для проверки наличия имени входа на целевом сервере и создания имени входа только в том случае, если это не так. Однако если при запуске скрипта вывода на сервере B вы получите следующее сообщение об ошибке, необходимо вручную устранить его, выполнив действия, описанные в этом разделе.

Сообщение 15025, уровень 16, состояние 1, строка 1
Субъект-сервер "MyLogin" уже существует.

Аналогичным образом, имя входа, которое уже находится в экземпляре на сервере B, может иметь идентификатор безопасности, который совпадает с идентификатором безопасности в выходном скрипте. В этом случае при запуске сценария вывода на экземпляре на сервере B отобразится следующее сообщение об ошибке:

Сообщение 15433, уровень 16, состояние 1, строка 1, «Предоставленный параметр для идентификатора безопасности уже используется».

Чтобы устранить проблему вручную, выполните следующие действия.

1. Внимательно просмотрите сценарий вывода.
2. Проверьте содержимое sys.server_principals представления в экземпляре на сервере B.
3. Устраните эти сообщения об ошибках соответствующим образом.

Начиная с SQL Server 2005, идентификатор безопасности для входа используется для управления доступом на уровне базы данных. Иногда при сопоставлении с пользователями в разных базах данных имена входа могут иметь разные идентификаторы безопасности. Эта проблема может возникнуть, если базы данных объединяются вручную с разных серверов. В таких случаях имя входа может получить доступ только к базе данных, где идентификатор безопасности субъекта базы данных соответствует идентификатору безопасности в представлении sys.server_principals . Чтобы устранить эту проблему, вручную удалите пользователя базы данных с несогласованным идентификатором безопасности с помощью инструкции DROP USER . Затем снова добавьте пользователя с инструкцией CREATE USER и сопоставите его с правильным именем входа (субъект сервера).

Дополнительные сведения и различия серверов от субъектов базы данных см. в статье CREATE USER и CREATE LOGIN.

Ссылки

• Устранение неполадок из-за потерянных пользователей
• CREATE LOGIN (Transact-SQL)
• ALTER LOGIN (Transact-SQL)
• Объекты скриптов в СРЕДЕ SQL Server Management Studio