MSSQL 마이그레이션시 계정 로그인 불가 오류

서버가 오래되어서, EOS 대응을 위해서 MS-SQL 업그레이드 중에 확인된 에러가 있습니다.

 

신규 서버를 만들고 , 데이터를 마이그레이션 한 뒤에 IP를 스왑하는 방식으로 진행했는데요. 

로그인 계정 정보를 스왑 전에 마이그레이션을 했는데도, 로그인이 제대로 되지 않았습니다.

실제 운영에서 이런 일이 발생한다면 바로 시스템 장애로 이어지게 될 상황입니다. 

개발DB여서 일단 스왑을 하고 뒤에 판단을 했는데요, 운영 DB 같은 경우에는 특히 조심해서 진행할 필요가 있고 스왑하기 전에 미리 모든 상황을 다 체크하는 것이 필수입니다! 

 

 

내용을 확인해보니, 2가지 이슈가 겹쳐 있었습니다.

 

1. 암호화 해시값 해석 오류 

MSSQL 서버는 단방향 암호화를 사용하고 있습니다. 

그리고 암호화 시에는 일반적으로 버전별 특정 알고리즘 + 서버의 특정한 시드값을 이용합니다.
동일한 버전을 사용하는 것도 아니고, 서버도 바뀌었기 때문에 알고리즘/시드값이 모두 변동이 발생했던 것입니다. 마이그레이션을 해서 해시값은 동일했지만, 실제 암/복호화에 사용하는 값/알고리즘이 바뀌었기 때문에 우리가 알고 있던 기존 암호로는 로그인이 되지 않는 것이죠. 

 

해결책 :  뭔가 시도하는 것을 빠르게 포기하고,  기존 암호를 찾아서 일일이 평문으로 계정 비밀번호를 재설정해주시면 해결됩니다. 

 

 

2. 로그인 완료 후에 데이터베이스 매핑이 모두 풀려있음. 

 

MSSQL 로그인 계정은 각자의 ID를 가지고 있습니다. (SID) 

여기에서 관리자/슈퍼유저에 해당하는 sa 계정은 유일하게 고정된 SID = 1 을 가지고 있는데요. 

이외의 로그인 계정들은 마이그레이션 과정에서 SID가 변경될 수 있습니다. 

 

 ex) order 계정의 SID가 A서버에서는 300 이었는데, 마이그레이션을 했더니 B 서버에서는 305 이면 데이터베이스는 300번 SID를 찾고 있는데 order 계정이 없는 것처럼 인식하게 됩니다. 혹은 300번 SID 계정이 마이그레이션하면서 order 계정에 물려지는 것이 아니라 helper 계정에 물려져 있을 수도 있겠죠.

 

SID가 변경된 경우에는 실제로 데이터베이스에 매핑되어있는 사용자 ID 값과 사용자명, 권한이 불일치하게 되고 그럴 경우에 SSMS 같은 DB 접속 프로그램을 이용해서 확인해보게 되면, DB/테이블 권한이 없는 매핑 화면을 보게 됩니다. 

 

데이터베이스 매핑을 해제하지 않은 상태에서, 로그인 계정 자체를 삭제해버리면 비슷한 상황이 발생합니다. 

해당 SID 계정은 없는데, 그 SID로 데이터베이스에 접근을 요청하고 있는 상황이니 아무것도 할 수 없습니다.

 

 해결책 : 여전히 데이터베이스 매핑된 상태는 유지되고 있으므로, 이 정보를 일치시켜주는 작업이 필요합니다.  

  DB서버에 로그인 계정이 없는 고아 상태인 계정들을 가져와서 , 이름이 동일한 계정들을 찾아서 매핑시켜주는 작업을 해주면 정상적으로 매핑 상태가 복원됩니다. 

-- MSSQL 2022 기준 쿼리
USE [DB명];
GO
DECLARE @UserName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

-- 고아 사용자 목록을 커서로 가져옴
DECLARE Orphan_Cursor CURSOR FOR
SELECT p.name
FROM sys.database_principals p
JOIN sys.server_principals s ON p.name = s.name
WHERE p.sid <> s.sid AND p.type = 'S';

OPEN Orphan_Cursor;
FETCH NEXT FROM Orphan_Cursor INTO @UserName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- ALTER USER 구문 동적 생성 및 실행
    SET @SQL = 'ALTER USER [' + @UserName + '] WITH LOGIN = [' + @UserName + '];';
    EXEC sp_executesql @SQL;
    PRINT '매핑 완료: ' + @UserName;

    FETCH NEXT FROM Orphan_Cursor INTO @UserName;
END;

CLOSE Orphan_Cursor;
DEALLOCATE Orphan_Cursor;
GO