삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항)

삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항)

 

·       Version : SQL Server

 

도메인 [DOMAIN\group]에 멤버 [DOMAIN\user]가 있고 [DOMAIN\group]에 SQL Server 인스턴스에 대한 로그인 권한이 있는 그룹이 있다고 가정한다. “Drop Login [DOMAIN\group]”을 실행하면 [DOMAIN\user] 사용자는 더 이상 SQL Server에 액세스 할 수 없을 것으로 예상했다. 그러나 아래와 같은 결과가 나왔다.

·       [DOMAIN\user]의 새로운 연결은 허용되지 않음

·       기존 연결된 사용자는 계속 작동함 (기존의 [DOMAIN\user] 연결에는 그룹 구성원 및 권한이 캐시되므로 해당 값을 새로 고칠때 까지 연결을 통해 쿼리를 계속 실행할 수 있다.)

 

“DROP LOGIN [DOMAIN\user]”가 실행될 때 [DOMAIN\user]가 로그인으로 추가되어있고 SQL Server 에 연결이 되어 있는 경우 다음과 같은 오류 메시지와 함께 명령이 실패 한다.

DROP LOGIN” command fails with error “Could not drop login 'DOMAIN\user' as the user is currently logged in.

 

 [DOMAIN\user]의 연결이 해제되면  “DROP LOGIN [DOMAIN\user]”가 성공하고 사용자는 더 이상 SQL Server에 액세스할 수 없다.

[DOMAINO\group]이 로그인으로 추가되고 “DROP LOGIN [DOMAINO\group]” 명령을 실행하면 [DOMAINO\user]가 로그인 된 경우에도 “DROP LOGIN”명령이 성공한다. 그러나 [DOMAIN\ user]가 여전히 로그인되어 있는지 확인하기 위해해서는 아래 스크립트를 실행하여 확인할 수 있다.

select login_name from sys.dm_exec_sessions where login_name = 'DOMAIN\user'

 

"DROP LOGIN <loginName>"을 실행하면 SQL Server는 <loginName> 계정에 대한 활성 연결이 있는지 확인한다. 이 로그인에 대해 활성화 된 연결이 없으면 로그인이 제거되고 명령이 성공한다. 그러나 <loginName>이 AD 그룹 인 경우 SQL Server는 그룹의 구성원 중 활성 로그인이 있는지 확인하지 않는다. [DOMAIN\ group]을 삭제하면 [DOMAIN\user]가 로그인되어 있고 [DOMAIN\ group]의 구성원 인 경우에도 [DOMAIN\ group]에 대한 활성 연결이 없기 때문에 명령이 성공한다. 실제로 [DOMAIN\ user]는 권한있는 작업 (예 : 로그인 생성)을 수행하거나 연결을 재설정 (예 : sp_reset_connection 로그 아웃 및 다시 로그인) 할 때까지 연결 상태를 유지한다. 이때 SQL Server는 [DOMAIN\ user]의 권한을 확인하고 더 이상 유효한 로그인이 없는지 확인하고 연결을 끊는다. 이러한 연결을 강제로 재설정 할 수 있다. 그룹 로그인을 삭제 한 후 아래 스크립트를 실행한다.

SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'DOMAIN\user'

 

스크립트를 실행하면 [DOMAIN\ user] 가 연결의 모든 세션 ID를 보여준다. 이제 “KILL <session_id>”를 실행하여 강제로 사용자 연결을 종료한다. [DOMAIN\user]의 세션은 강제 종료된 상태이기 때문에 쿼리를 실행할 때 더 이상 유효한 로그인이 없는 상태여서 다시 연결을 해야한다. 이때 그룹이 삭제되어 있기 때문에 연결할 수 없게 된다.

연결 자체는 sp_reset_connection을 호출하여 스레드가 로그 아웃했다가 SQL Server에 다시 로그인하도록하여 권한 업데이트를 강제 할 수도 있다. 연결이 영구적이며 지속적으로 로그 아웃했다가 다시 로그인하여 권한 검사를 수행하지 않기 때문에 연결 풀링에 유용하다. 풀링 된 연결을 검색 할 때 sp_reset_connection을 호출하면 로그인 권한이 취소 된 경우 연결이 실패된다.

 

그룹에 대한 작업 권한이 취소되면 구성원 사용자의 권한이 즉시 취소된다. 예를 들어 [DOMAIN\group]에 "foo"테이블에 대한 선택 권한이 있고 "REVOKE SELECT ON foo TO [DOMAIN\ group]"을 실행 한 경우 [DOMAIN\user]는 즉시 "foo"에 대한 선택 권한을 잃게된다. 아래 스크립트를 사용하여  실습 가능하다.

# Login as an admin user to SQL Server and add [DOMAIN\group] as a login # CREATE LOGIN [DOMAIN\group] FROM WINDOWS GO   # In another terminal, login to SQL Server as [DOMAIN\user] and run a query to show you have access # SELECT @@VERSION GO   # Switch back to your admin terminal and drop the group login. It will succeed even though [DOMAIN\user] is connected. # DROP LOGIN [DOMAIN\group] GO   # Switch back to your [DOMAIN\user] window and check you still have access # SELECT @@VERSION GO   # Switch to the admin terminal and kill the connection # SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = ‘DOMAIN\user’ GO   KILL <session_id from query above> GO   # Switch to your [DOMAIN\user] window to see they no longer have access. This command should not return the version information. # SELECT @@VERSION GO

 

 

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2018/08/03/sql-server-mysteries-the-case-of-the-dropped-ad-group-login/

 

 

2019-09-11 / Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server Login, SQL Server Account, Active Directory, AD, DROP LOGIN, CREATE LOGIN