SQL Server 인스턴스 간에 로그인 및 암호 전송

아티클
09/06/2024

이 문서에서는 Windows에서 실행되는 여러 SQL Server 인스턴스 간에 로그인 및 암호를 전송하는 방법을 설명합니다.

원래 제품 버전: SQL Server
원본 KB 번호: 918992, 246133

소개

이 문서에서는 Microsoft SQL Server 여러 인스턴스 간에 로그인 및 암호를 전송하는 방법을 설명합니다.

참고

인스턴스가 동일한 서버 또는 다른 서버에 있을 수 있으며 해당 버전이 다를 수 있습니다.

추가 정보

이 문서에서 서버 A와 서버 B는 서로 다른 서버입니다.

서버 A의 SQL Server instance 데이터베이스를 서버 B의 SQL Server instance 이동한 후에는 사용자가 서버 B의 데이터베이스에 로그인하지 못할 수 있습니다. 또한 사용자에게 다음과 같은 오류 메시지가 표시될 수 있습니다.

'MyUser' 사용자가 로그인하지 못했습니다. (Microsoft SQL Server, 오류: 18456)

이 문제는 로그인 및 암호를 서버 A의 SQL Server instance 서버 B의 SQL Server instance 전송하지 않았기 때문에 발생합니다.

참고

18456 오류 메시지는 다른 이유로도 발생합니다. 이러한 원인 및 잠재적 해결 방법에 대한 자세한 내용은 MSSQLSERVER_18456 참조하세요.

로그인을 전송하려면 상황에 따라 다음 방법 중 하나를 사용합니다.

방법 1: 대상 SQL Server 컴퓨터(서버 B)의 암호를 다시 설정합니다.

이 문제를 해결하려면 SQL Server 컴퓨터에서 암호를 재설정한 다음 로그인을 스크립아웃합니다.

참고

암호 해시 알고리즘은 암호를 재설정할 때 사용됩니다.

방법 2: 원본 서버(서버 A)에서 생성된 스크립트를 사용하여 대상 서버(서버 B)로 로그인 및 암호를 전송합니다.

로그인 및 암호를 전송하는 데 필요한 스크립트를 생성하는 데 도움이 되는 저장 프로시저를 만듭니다. 이렇게 하려면 SSMS(SQL Server Management Studio) 또는 다른 클라이언트 도구를 사용하여 서버 A에 연결하고 다음 스크립트를 실행합니다.

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

SSMS 쿼리 편집기에서 텍스트로 결과 표시 옵션을 선택합니다.
같은 또는 새 쿼리 창에서 다음 문을 실행합니다.
```
EXEC sp_help_revlogin
```
sp_help_revlogin 저장 프로시저가 생성하는 출력 스크립트는 로그인 스크립트입니다. 이 로그인 스크립트는 원래 SID(보안 식별자) 및 원래 암호가 있는 로그인을 만듭니다.

중요

대상 서버에서 단계 구현을 진행하기 전에 다음 주의 섹션의 정보를 검토합니다.

대상 서버(서버 B)에서의 단계

클라이언트 도구(예: SSMS)를 사용하여 서버 B에 연결한 다음 서버 A에서 4단계(출력) sp_helprevlogin에서 생성된 스크립트를 실행합니다.

설명

서버 B의 인스턴스에서 출력 스크립트를 실행하기 전에 다음 정보를 확인합니다.

암호는 다음과 같은 방법으로 해시할 수 있습니다.
- VERSION_SHA1: 이 해시는 SHA1 알고리즘을 사용하여 생성되며 SQL Server 2000~SQL Server 2008 R2에서 사용됩니다.
- VERSION_SHA2: 이 해시는 SHA2 512 알고리즘을 사용하여 생성되며 SQL Server 2012 이상 버전에서 사용됩니다.
출력 스크립트를 신중하게 확인합니다. 서버 A와 서버 B가 서로 다른 도메인에 있는 경우 출력 스크립트를 변경해야 합니다. 그런 다음 문에서 새 도메인 이름을 사용하여 원래 도메인 이름을 CREATE LOGIN 바꿔야 합니다. 새 도메인에 액세스 권한이 부여된 통합 로그인에는 원래 도메인의 로그인과 동일한 SID가 없습니다. 따라서 사용자는 이러한 로그인에서 분리됩니다. 이러한 분리된 사용자를 resolve 방법에 대한 자세한 내용은 분리된 사용자(SQL Server) 및 ALTER USER 문제 해결을 참조하세요.
서버 A와 서버 B가 동일한 도메인에 있는 경우 동일한 SID가 사용됩니다. 따라서 사용자는 분리될 가능성이 낮습니다.
출력 스크립트에서 로그인은 암호화된 암호를 사용하여 생성됩니다. 이는 CREATE LOGIN 문의 HASHED 인수 때문입니다. 이 인수는 PASSWORD 인수가 이미 해시된 후에 입력된 암호를 지정합니다.
기본적으로 sysadmin 고정 서버 역할의 구성원만 sys.server_principals 보기에서 SELECT 문을 실행할 수 있습니다. sysadmin 고정 서버 역할의 멤버가 사용자에게 필요한 권한을 부여하지 않는 한 사용자는 출력 스크립트를 만들거나 실행할 수 없습니다.
이 문서의 단계에서는 특정 로그인에 대한 기본 데이터베이스 정보를 전송하지 않습니다. 이는 기본 데이터베이스가 서버 B에 항상 존재하지 않을 수 있기 때문입니다. 로그인에 대한 기본 데이터베이스를 정의하려면 로그인 이름과 기본 데이터베이스를 인수로 전달하여 문을 사용합니다 ALTER LOGIN .
원본 및 대상 서버에서 주문 정렬:
- 대/소문자를 구분하지 않는 서버 A 및 대/소문자 구분 서버 B: 서버 A의 정렬 순서는 대/소문자를 구분하지 않을 수 있으며 서버 B의 정렬 순서는 대/소문자를 구분할 수 있습니다. 이 경우 사용자는 로그인 및 암호를 서버 B의 인스턴스로 전송한 후 모두 대문자로 암호를 입력해야 합니다.
- 대/소문자를 구분하는 서버 A 및 대/소문자를 구분하지 않는 서버 B: 서버 A의 정렬 순서는 대/소문자를 구분할 수 있으며 서버 B의 정렬 순서는 대/소문자를 구분하지 않을 수 있습니다. 이 경우 사용자는 다음 조건 중 하나가 충족되지 않는 한 서버 B의 instance 전송하는 로그인 및 암호를 사용하여 로그인할 수 없습니다.
  - 원래 암호에는 문자가 없습니다.
  - 원래 암호의 모든 문자는 대문자입니다.
- 두 서버에서 대/소문자를 구분하거나 대/소문자를 구분하지 않음: 서버 A와 서버 B의 정렬 순서는 대/소문자를 구분하거나 서버 A와 서버 B의 정렬 순서가 대/소문자를 구분하지 않을 수 있습니다. 이러한 경우 사용자는 문제가 발생하지 않습니다.
서버 B의 instance 이미 있는 로그인에는 출력 스크립트의 이름과 동일한 이름이 있을 수 있습니다. 이 경우 서버 B의 인스턴스에서 출력 스크립트를 실행할 때 다음 오류 메시지가 표시됩니다.

메시지 15025, 수준 16, 상태 1, 줄 1
서버 보안 주체 'MyLogin'이 이미 있습니다.

마찬가지로 서버 B의 instance 이미 있는 로그인에는 출력 스크립트의 SID와 동일한 SID가 있을 수 있습니다. 이 경우 서버 B의 인스턴스에서 출력 스크립트를 실행할 때 다음 오류 메시지가 표시됩니다.

메시지 15433, 레벨 16, 상태 1, 줄 1 제공한 매개 변수 sid가 사용 중입니다.

따라서 다음을 실행해야 합니다.
1. 출력 스크립트를 신중하게 검토합니다.
2. 서버 B의 instance 보기의 sys.server_principals 내용을 검사합니다.
3. 이러한 오류 메시지를 적절하게 해결합니다.
  
  2005년 SQL Server 로그인에 대한 SID는 데이터베이스 수준 액세스를 구현하는 데 사용됩니다. 로그인에는 서버의 다른 데이터베이스에 다른 SID가 있을 수 있습니다. 이 경우 로그인은 sys.server_principals 보기의 SID와 일치하는 SID가 있는 데이터베이스에만 액세스할 수 있습니다. 이 문제는 두 데이터베이스가 서로 다른 서버에서 결합된 경우에 발생할 수 있습니다. 이 문제를 해결하려면 DROP USER 문을 사용하여 SID 불일치가 있는 데이터베이스에서 수동으로 로그인을 제거합니다. 그런 다음 CREATE USER 문을 사용하여 로그인을 다시 추가합니다.

다음을 통해 공유

SQL Server 인스턴스 간에 로그인 및 암호 전송

소개

추가 정보

대상 서버(서버 B)에서의 단계

설명

참조

피드백

추가 리소스