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

아티클
01/17/2025

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

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

소개

이 문서에서는 Microsoft SQL Server 여러 인스턴스 간에 로그인 및 암호를 전송하는 방법을 설명합니다. 인스턴스는 동일한 서버 또는 다른 서버에 있을 수 있으며 해당 버전이 다를 수 있습니다.

SQL Server 인스턴스 간에 로그인을 전송하는 이유는 무엇인가요?

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

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

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

이 문제는 서버 A의 SQL Server 인스턴스의 로그인이 서버 B의 SQL Server 인스턴스에 없기 때문에 발생합니다.

오류 18456은 다른 여러 가지 이유로 발생합니다. 이러한 원인 및 해결 방법에 대한 자세한 내용은 MSSQLSERVER_18456 참조하세요.

로그인을 전송하는 단계

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

방법 1: 원본 서버에서 SSMS를 통해 스크립트를 생성하고 대상 서버에서 SQL Server 로그인에 대한 암호를 수동으로 재설정합니다.

데이터베이스에 대한 스크립트 생성 옵션을 사용하여 SSMS(SQL Server Management Studio)에서 로그인 스크립트를 생성할 수 있습니다.

원본 서버에서 SSMS를 통해 스크립트를 생성하고 대상 서버의 SQL Server 로그인에 대한 암호를 수동으로 다시 설정하려면 다음 단계를 수행합니다.

원본 SQL Server를 호스팅하는 서버 A에 연결합니다.
데이터베이스 노드를 확장합니다.
사용자 데이터베이스를 마우스 오른쪽 단추로 클릭하고 스크립트 생성 태스크>를 선택합니다.
소개 페이지를 엽니다. [다음]을 선택하여 [개체 선택] 페이지를 엽니다. 전체 데이터베이스 및 모든 데이터베이스 개체 스크립팅을 선택합니다.
다음 선택하여 스크립팅 옵션 설정 페이지를 엽니다.
스크립트 로그인 옵션에 대한 고급 단추를 선택합니다.
고급 목록에서 스크립트 로그인을 찾고, 옵션을 True로 설정하고, 확인을 선택합니다.
스크립트 저장 방법 선택에서 스크립팅 옵션 설정으로 돌아가서 새 쿼리 창에서 열기를 선택합니다.
다음을 두 번 선택한 다음 마침을 선택합니다.
로그인이 포함된 스크립트의 섹션을 찾습니다. 일반적으로 생성된 스크립트는 이 섹션의 시작 부분에 다음 주석이 있는 텍스트를 포함합니다.

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

참고 항목

이는 SQL Server 인증 로그인이 임의 암호로 생성되고 기본적으로 사용하지 않도록 설정되어 있음을 나타냅니다. 암호를 재설정하고 대상 서버에서 이러한 로그인을 다시 사용하도록 설정해야 합니다.
더 큰 생성된 스크립트의 로그인 스크립트를 대상 SQL Server에 적용합니다.
SQL Server 인증 로그인의 경우 대상 SQL Server에서 암호를 다시 설정하고 해당 로그인을 다시 사용하도록 설정합니다.

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

로그인 및 암호를 전송하는 데 필요한 스크립트를 생성하는 데 도움이 되는 저장 프로시저를 만듭니다. 이렇게 하려면 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_hexadecimal sp_help_revlogin.

SSMS 쿼리 편집기에서 텍스트로 결과 표시 옵션을 선택합니다.
같은 또는 새 쿼리 창에서 다음 문을 실행합니다.
```
EXEC sp_help_revlogin
```
sp_help_revlogin 저장 프로시저가 생성하는 출력 스크립트는 로그인 스크립트입니다. 이 로그인 스크립트는 원래 SID(보안 식별자) 및 원래 암호가 있는 로그인을 만듭니다.
대상 서버에서 단계 구현을 계속하기 전에 주의 섹션의 정보를 검토하고 따릅니다.
주의 섹션의 적용 가능한 단계를 구현한 후에는 클라이언트 도구(예: SSMS)를 사용하여 대상 서버 B에 연결합니다.
서버 A에서 출력으로 생성된 스크립트를 sp_helprevlogin 실행합니다.

설명

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

암호 해시 정보

암호는 다음과 같은 방법으로 해시할 수 있습니다.
- VERSION_SHA1: 이 해시는 SHA1 알고리즘을 사용하여 생성되며 SQL Server 2000~SQL Server 2008 R2에서 사용됩니다.
- VERSION_SHA2: 이 해시는 SHA2 512 알고리즘을 사용하여 생성되며 SQL Server 2012 이상 버전에서 사용됩니다.
출력 스크립트에서 로그인은 암호화된 암호를 사용하여 생성됩니다. 이는 문의 인수 때문 HASHED 입니다 CREATE LOGIN . 이 인수는 인수가 이미 해시된 후에 PASSWORD 입력된 암호를 지정합니다.

도메인 변경을 처리하는 방법

원본 서버와 대상 서버가 서로 다른 도메인에 있나요? 출력 스크립트를 신중하게 검토합니다. 서버 A와 서버 B가 서로 다른 도메인에 있는 경우 출력 스크립트를 변경해야 합니다. 그런 다음 문에서 새 도메인 이름을 사용하여 원래 도메인 이름을 CREATE LOGIN 바꿔야 합니다. 새 도메인에 액세스 권한이 부여된 통합 로그인에는 원래 도메인의 로그인과 동일한 SID가 없습니다. 따라서 사용자는 이러한 로그인에서 분리됩니다. 이러한 분리된 사용자를 해결하는 방법에 대한 자세한 내용은 분리된 사용자 문제 해결(SQL Server) 및 ALTER USER를 참조하세요.

서버 A와 서버 B가 동일한 도메인에 있는 경우 동일한 SID가 사용됩니다. 따라서 사용자는 분리될 가능성이 낮습니다.

모든 로그인을 보고 선택할 수 있는 권한

기본적으로 sysadmin 고정 서버 역할의 구성원만 sys.server_principals 보기에서 SELECT 문을 실행할 수 있습니다. sysadmin 고정 서버 역할의 멤버가 사용자에게 필요한 권한을 부여하지 않는 한 사용자는 출력 스크립트를 만들거나 실행할 수 없습니다.

기본 데이터베이스 설정이 스크립팅 및 전송되지 않음

이 문서의 단계에서는 특정 로그인에 대한 기본 데이터베이스 정보를 전송하지 않습니다. 이는 기본 데이터베이스가 서버 B에 항상 존재하지 않을 수 있기 때문입니다. 로그인에 대한 기본 데이터베이스를 정의하려면 로그인 이름과 기본 데이터베이스를 인수로 전달하여 문을 사용합니다 ALTER LOGIN .

원본 서버와 대상 서버 간에 다양한 정렬 순서를 처리하는 방법

원본 서버와 대상 서버 간의 정렬 순서에 차이가 있거나 동일할 수 있습니다. 각 시나리오를 해결할 수 있는 방법은 다음과 같습니다.

대/소문자를 구분하지 않는 서버 A 및 대/소문자 구분 서버 B: 서버 A의 정렬 순서는 대/소문자를 구분하지 않을 수 있으며 서버 B의 정렬 순서는 대/소문자를 구분할 수 있습니다. 이 경우 사용자는 로그인 및 암호를 서버 B의 인스턴스로 전송한 후 모두 대문자로 암호를 입력해야 합니다.
대/소문자를 구분하는 서버 A 및 대/소문자를 구분하지 않는 서버 B: 서버 A의 정렬 순서는 대/소문자를 구분할 수 있으며 서버 B의 정렬 순서는 대/소문자를 구분하지 않을 수 있습니다. 이 경우 사용자는 다음 조건 중 하나가 충족되지 않는 한 서버 B의 인스턴스로 전송하는 로그인 및 암호를 사용하여 로그인할 수 없습니다.
- 원래 암호에는 문자가 없습니다.
- 원래 암호의 모든 문자는 대문자입니다.
두 서버에서 대/소문자를 구분하거나 대/소문자를 구분하지 않습니다. 서버 A와 서버 B의 정렬 순서는 대/소문자를 구분하거나 서버 A와 서버 B의 정렬 순서가 대/소문자를 구분하지 않을 수 있습니다. 이러한 경우 사용자에게 문제가 없습니다.

대상 서버에 이미 존재하는 로그인을 처리하는 방법

이 스크립트는 대상 서버에 로그인이 있는지 확인하고 그렇지 않은 경우에만 로그인을 만들도록 설계되었습니다. 그러나 서버 B의 인스턴스에서 출력 스크립트를 실행할 때 다음 오류 메시지가 표시되는 경우 이 섹션의 단계에 따라 수동으로 해결해야 합니다.

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

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

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

이 문제를 수동으로 해결하려면 다음 단계를 수행합니다.

출력 스크립트를 신중하게 검토합니다.
서버 B의 인스턴스에서 보기의 sys.server_principals 내용을 검사합니다.
이러한 오류 메시지를 적절하게 해결합니다.

SQL Server 2005부터 로그인용 SID를 사용하여 데이터베이스 수준 액세스를 관리합니다. 경우에 따라 다른 데이터베이스의 사용자에 매핑될 때 로그인에 다른 SID가 있을 수 있습니다. 이 문제는 데이터베이스가 다른 서버에서 수동으로 결합되는 경우에 발생할 수 있습니다. 이러한 경우 로그인은 데이터베이스 보안 주체의 SID가 보기의 SID와 일치하는 데이터베이스에 sys.server_principals 만 액세스할 수 있습니다. 이 문제를 해결하려면 DROP USER 문을 사용하여 일치하지 않는 SID가 있는 데이터베이스 사용자를 수동으로 제거합니다. 그런 다음 문을 사용하여 사용자를 다시 CREATE USER 추가하고 올바른 로그인(서버 보안 주체)에 매핑합니다.

자세한 내용 및 데이터베이스 보안 주체와 서버를 구분하려면 CREATE USER 및 CREATE LOGIN을 참조하세요.

다음을 통해 공유