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

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

Сводка

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

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

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

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

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

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

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

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

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

Создавайте сценарии с помощью 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 и повторно включите эти имена входа.

Передача имен входа и паролей на целевой сервер (Сервер 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. Просмотрите и следуйте инструкциям в разделе "Дополнительные рекомендации по передаче имен входа SQL Server ", прежде чем продолжить реализацию шагов на целевом сервере.

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

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

Дополнительные рекомендации при передаче имен входа SQL Server

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

Понимание хэширования паролей при передаче данных входа в SQL Server

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

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

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

Обработка изменений домена во время передачи имени входа SQL Server

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

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

Необходимые разрешения для просмотра и выбора учетных записей SQL Server

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

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

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

Управление различиями порядка сортировки при передаче логинов в SQL Server

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

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

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

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

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

Устранение конфликтов с существующими именами входа SQL Server на целевом сервере

Скрипт проверяет, существует ли имя входа на целевом сервере и создает имя входа только в том случае, если он не существует. Однако если при запуске скрипта вывода на сервере 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)
• ИЗМЕНИТЬ ВХОД (Transact-SQL)
• Объекты скриптов в СРЕДЕ SQL Server Management Studio