Alwayson Automatic synchronization Login (Alwayson 계정 자동 동기화)

이번에 서버를 업그레이드하면서 Alwayson으로 서버를 변경하였습니다. 물론 제가 변경 작업은 한 것은 아니고, 작업은 다른 분이 하셨고 변경 후에 한가지 문제가

발생하였습니다. Alwayson은 로그인이 자동으로 동기화 되지 않는 문제가 있어서 Active 와 Standby 서버 2개에 모두 로그인을 만들어 줘야 하는 문제가 있었습니다.

그래서 이러한 문제를 해결하기 위해서 몇몇의 스크립트를 약간 변형하여, 자동으로 로그인을 생성하는 스크립트를 만들었습니다. 물론 이 스크립트를 Job으로 

등록하여, 원하는 주기로 돌리면 자동으로 동기화가 됩니다. 

이 스크립트에서 사용되는 기본적 틀은 MS에서 제공하는 계정을 이관하는 스크립트를 참조하였습니다. 그 스크립트는 다음의 URL을 참고 바랍니다.

URL : https://support.microsoft.com/ko-kr/kb/246133

제가 만든 스크립트에서 사용되는 기능은 다음과 같습니다. 어쩌보면 이것은 제약조건일 수 있습니다.

• 링크드 서버
• OPENQuery

OPENQuery 는 링크드 서버만 된다면, 별다른 문제가 발생 할 것 같지는 않지만 링크드 서버의 경우 회사마다 사용하지 못하는 곳도 많은 것으로 알고 있습니다.

기본적인  이 스크립트의 기본적인 동작은 링크드 서버를 통해서 Active의 로그인정보를 조회하고 이것을 비교하여, 없는 것을 만들어주는 것 입니다.

모든 기본적인 정보(PW,Login Name등)를 모두 Active 서버에서 가져오기 때문에 링크드 서버는 필수 입니다. 링크드 서버를 만들 수 없는 분들일 경우 이 스크립트는

전혀 쓸모가 없습니다.

쿼리는 다음과 같습니다. 쿼리에 제가 수정한 부분에 주석을 모두 달아놨으며, 이 부분을 참고하시면 원리를 쉽게 이해 하실 수 있을 듯 합니다. 

이 스크립트는 Standby 서버에서 돌리 셔서 만들어야 합니다. (Active 의 정보를 Standby로 읽어오는 방식이므로)

USE master
GO
IF OBJECT_ID ('sp_hexadecimal' ) IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary ( 256),
    @hexvalue varchar ( 514) OUTPUT
AS
DECLARE @charvalue varchar ( 514 )
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char( 16 )
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH ( @binvalue )
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length )
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT( int , SUBSTRING (@binvalue , @i, 1 ))
  SELECT @firstint = FLOOR( @tempint /16 )
  SELECT @secondint = @tempint - ( @firstint *16 )
  SELECT @charvalue = @charvalue +
    SUBSTRING (@hexstring , @firstint + 1, 1 ) +
    SUBSTRING (@hexstring , @secondint + 1, 1 )
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin' ) IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar ( 1 )
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary   varbinary ( 514 )
DECLARE @PWD_string   varchar ( 1000 )
DECLARE @SID_varbinary varbinary ( 85 )
DECLARE @SID_string varchar ( 514 )
DECLARE @tmpstr   varchar ( 1024 )
DECLARE @is_policy_checked varchar ( 3 )
DECLARE @is_expiration_checked varchar ( 3 )

-- Password Varbinary를 임시적으로 담기 위한 테이블 추가 
DECLARE @temp_Login TABLE (PW varbinary(514))
DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

-- Active 서버의 sys.server_principals , sys.syslogins 테이블을 조회하여 Standby 서버에 없는 계정 조회
SELECT sp . sid, sp. name , sp .type , sp . is_disabled, sp. default_database_name , l .hasaccess , l . denylogin 
FROM ACTIVE.master.sys.server_principals sp
	LEFT JOIN sys. server_principals p ON sp.name = p.name  
	LEFT JOIN ACTIVE.master.sys. syslogins l on ( l .name = sp .name ) 
WHERE sp . type IN ( 'S', 'G', 'U' ) AND sp. name <> 'sa' and p.name is null

ELSE
  DECLARE login_curs CURSOR FOR

-- Standby 서버에 존재하지 않는 계정을 커서에 넣음
SELECT p . sid, p. name , p .type , p . is_disabled, p. default_database_name , l .hasaccess , l . denylogin 
FROM ACTIVE.master.sys .server_principals p 
	LEFT JOIN ACTIVE.master.sys. syslogins l ON ( l .name = p .name ) 
WHERE p . type IN ( 'S', 'G', 'U' ) AND p. name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb , @hasaccess , @denylogin
IF (@@fetch_status = - 1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN - 1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT ( varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> - 1)
BEGIN
  IF ( @@fetch_status <> -2 )
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G' , 'U' ))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid

			-- Active 서버의 계정 Password Varbinary 값 가져와서 변환 
            INSERT INTO @temp_Login
			SELECT PW FROM Openquery([ACTIVE],'SELECT  CAST ( LOGINPROPERTY ( name , ''PasswordHash'' ) AS varbinary ( 514) )as [PW] ,name FROM sys.syslogins ')
			WHERE name = @name

			-- OPENQuery를 통해 즉시 담기가 불가능하므로, 임시 테이블에 저장된 정보를 @PWD_varbinary 변수에 저장
			SELECT @PWD_varbinary = CONVERT(varbinary ( 514 ), PW)
			FROM @temp_Login
		
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC 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 = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1 )
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME ( @name )
    END
    ELSE IF ( @hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME ( @name )
    END
    IF (@is_disabled = 1 )
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME ( @name ) + ' DISABLE'
    END
	EXEC (@tmpstr)
    PRINT @tmpstr
  END

	-- 커서에 새로운 정보를 담기 위해 임시 테이블 초기화 
	DELETE @temp_Login

  FETCH NEXT FROM login_curs INTO @SID_varbinary , @name , @type , @is_disabled , @defaultdb , @hasaccess , @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

EXEC sp_help_revlogin

아래쪽에 일부러 PRINT를 추가하여, 실행된 쿼리를 볼 수 있도록 해놨습니다. 응용하면, 어떠한 계정이 언제 만들어졌는지도 만들 수 있습니다.

별로 어려운 작업은 아니니 각자 해보셔도 좋을 듯 합니다.

차후에 Job 스케줄러로 등록을 하실때 맨 아래에 있는 프로시저만 등록해둔다면, 문제 없이 실행 됩니다. 

이 프로시저 Job 스케줄러 또한 Standby 서버에 등록하여 돌리셔야 합니다.

EXEC sp_help_revlogin

실서버에서 테스트 했을 때 전혀 이상이 없었으며, 로그인도 문제없이 가능 합니다.  하지만 여기서 한가지 궁금점이 생깁니다.

?. Question

Login은 만들어져 있는데, 그렇다면 DB의 사용자는 어떻게 해야할까요?

이 문제는 전혀 걱정이 없습니다. 왜냐하면 Alwayson은 DB끼리는 동기화를 계속해서 해줍니다. 그러므로 사용자는 자동으로 Active 서버에 생성이 되어 있으며,

이는 계속해서 동기화를 해주고 있습니다. 

아래의 로그인 속성을 보시면, db_owner 자격이 추가되어 있음을 볼 수 있습니다. 물론 로그인도 문제없이 되며, 모든 기능이 모두 됩니다.

지금 사용하는데에 별다른 문제는 없으며, 문제점이 발견되면 메일을 주시면 수정하도록 하겠습니다. 이제 막 만든 스크립트이므로, 문제가 있을 수 있습니다. 

하지만, 계정을 만드는 것 이외에 스크립트가 하는 것이 없으므로 위험도는 매우 낮다고 봐도 무방합니다.  그러나 실서버에 사용 할 시에 주의하여 사용하시기 바랍니다.

감사합니다.