[MSSQL] SQL Server 간 동일한 계정 생성(Mirroring, AlwaysOn)

SQL Server Mirroring 또는 AlwaysOn 구성 시, 발생 할 수 있는 계정 미 일치 내용에 대해 기술 합니다.

Windows OS 또는 기타 Microsoft 제품에서는 UID(User ID) 또는 SID(Secure ID)를 활용 해 사용자를 구분 합니다.

예를들어, SQL 서버1과 서버2에 'testuser' 라는 동일한 name의 계정이 있다고 가정 합니다.

일반적으로 SSMS 에서 계정 생성, 또는 쿼리문 Create user 문을 이용 해 계정을 생성하면,

아래 그림처럼 user name은 동일하지만 SID 가 전혀 다른 계정이 생성 됩니다.

아래 쿼리문을 이용 해 계정의 SID를 검색 해 볼 수도 있습니다.

SID가 다른 상태에서 두 서버 DB 에 계정을 사용자 매핑을 하게 되면, DB는 같은데 다른 사용자로 인식하게 되어,

FailOver 시 사용자 매핑이 풀리게 되고, 해당계정은 DB에 연결 할 수 없는 큰 문제가 발생 합니다.

그래서 Mirroring 또는 AlwaysOn 구성 시 모든 서버(인스턴스)에 같은 SID의 계정을 생성 해야 합니다.

이런 문제를 해결하기 위해 Microsoft 에서 해결방법을 제시하고 있습니다.

https://support.microsoft.com/ko-kr/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

위 링크의 쿼리를 실행 하면, 현재 실행되고 있는 SQL Server 내 모든 계정과 같은 속성을 가지는 값으로

다른 서버에 생성 할 수 있는 쿼리문을 만들어 주는 SP를 생성 합니다.

계정전송_SP생성.zip

SP 이름은 sp_help_revlogin

위 SP를 실행 해 보면, 서버 내 모든 계정을 다른 서버에서 동일한 SID로 생성 할 수 있는 Create user 문을 출력 해 줍니다.

이제, 복사 후 다른서버에서 실행 하면 SID뿐만아니라 모든 속성값이 동일한 계정을 생성 됩니다.

다시 SID조회를 해서 SID가 동일한지 확인 합니다.

완벽히 동일하네요.

이렇게 SID가 동일한 계정을 생성 후, DB에 사용자 매핑을 하면 다른 SQL 서버에서도 같은 계정으로 인식 하게 되어,

AlwaysOn FailOver 시에도 사용자매핑이 풀리는 문제가 없습니다.

※참고

1. https://support.microsoft.com/ko-kr/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

2. https://docs.microsoft.com/ko-kr/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-2017