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

Статья
09/06/2024

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

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

Введение

В этой статье описывается способ передачи имен входа и паролей между различными экземплярами Microsoft 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. Сброс пароля на целевом SQL Server компьютере (сервер B).

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

Примечание.

Алгоритм хэширования паролей используется при сбросе пароля.

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

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

В редакторе запросов SSMS выберите параметр Результаты в текст.
Выполните следующую инструкцию в том же или новом окне запроса:
```
EXEC sp_help_revlogin
```
Сценарий вывода, который создает хранимая процедура sp_help_revlogin, является сценарием входа. Этот сценарий входа создает имена входа с исходным идентификатором безопасности (SID) и исходным паролем.

Важно!

Ознакомьтесь со сведениями в следующем разделе Примечания, прежде чем приступить к реализации действий на целевом сервере.

Действия на целевом сервере (сервер B)

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

Замечания

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

Хэширование пароля может выполняться следующими способами:
- VERSION_SHA1: этот хэш создается с помощью алгоритма SHA1 и используется в SQL Server, начиная с версии от 2000 до 2008 R2.
- VERSION_SHA2: этот хэш создается с помощью алгоритма SHA2 512 и используется в SQL Server 2012 и более поздних версиях.
Внимательно просмотрите сценарий вывода. Если сервер A и сервер B находятся в разных доменах, необходимо изменить сценарий вывода. Затем необходимо заменить исходное доменное имя новым доменным именем в CREATE LOGIN инструкциях . Интегрированные имена входа, которым предоставлен доступ в новом домене, не имеют тот же идентификатор безопасности, что и имена входа в исходном домене. Таким образом, пользователи будут потеряны из-за этих имен входа. Дополнительные сведения о том, как устранить проблему с этими потерянными пользователями, см. в разделах Устранение неполадок потерянных пользователей (SQL Server) и ALTER USER.
Если сервер A и сервер B находятся в одном домене, используется один и тот же идентификатор безопасности. Поэтому пользователи вряд ли будут потеряны.
В сценарии вывода имена входа создаются с помощью зашифрованного пароля. Это связано с аргументом HASHED в инструкции CREATE LOGIN. Этот аргумент указывает, что пароль, введенный после аргумента PASSWORD, уже хэширован.
По умолчанию только член предопределенной роли сервера sysadmin может выполнять инструкцию SELECT из представления sys.server_principals. Если член предопределенной роли сервера sysadmin не предоставляет пользователям необходимые разрешения, пользователи не смогут создать или запустить выходной скрипт.
Действия, описанные в этой статье, не передают сведения о базе данных по умолчанию для определенного имени входа. Это связано с тем, что база данных по умолчанию может существовать не всегда на сервере B. Чтобы определить базу данных по умолчанию для имени входа, используйте инструкцию ALTER LOGIN , передав имя входа и базу данных по умолчанию в качестве аргументов.
Порядок сортировки на исходном и целевом серверах:
- Сервер A без учета регистра и сервер B с учетом регистра. Порядок сортировки сервера A может быть без учета регистра, а порядок сортировки сервера B может быть с учетом регистра. В этом случае пользователи должны ввести пароли заглавными буквами после передачи имен входа и паролей экземпляру на сервере B.
- Сервер A с учетом регистра и сервер B без учета регистра: Порядок сортировки сервера A может учитывать регистр, а порядок сортировки сервера B — без учета регистра. В этом случае пользователи не могут войти в систему с помощью имен входа и паролей, передаваемых экземпляру на сервере B, если не выполняется одно из следующих условий:
  - Исходные пароли не содержат букв.
  - Исходные пароли содержат только прописные буквы.
- С учетом регистра или без учета регистра на обоих серверах. Порядок сортировки сервера A и сервера B может быть с учетом регистра, или порядок сортировки сервера A и сервера B может быть без учета регистра. В таких случаях у пользователей нет проблем.
Имя входа, которое уже находится в экземпляре на сервере 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.

Поделиться через

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

Введение

Дополнительные сведения

Действия на целевом сервере (сервер B)

Замечания

Ссылки

Обратная связь

Дополнительные ресурсы