서버 트리거를 이용한 SQL Server 외부접속 차단

정상적으로 구성된 시스템이라면 데이터베이스 서버가 외부로 직접 노출되는 경우는 아마 없을 겁니다.

하지만, 언제나 예외는 있는 법이죠. 간단하게 구축된 시스템의 경우, 개발비용 최소화를 위해 어플리케이션에서 데이터베이스 서버로 직접 접속할 수 있도록 외부접속을 열어두기도 합니다.

이렇게 외부접속이 열려 있는 시스템의 경우, 아무리 단속을 잘해놨다고 해도 어디에나 허점은 있는 법입니다.
이런 데이터베이스 서버의 로그를 열어보면 지금 이 시간에도 특정 계정으로 끊임 없는 로그인 시도가 이어지고 있다는 걸 아실겁니다. 아! 세상엔 쓸모 없이 성실한 새끼들이 너무 많습니다.

특정 프로그램을 제외한 원격지에서의 접속을 모조리 막아버릴 방법을 찾던 중, 그간 거의 쓰질 않았던 서버 트리거를 통해 이를 해결 할 수 있는 방법이 있다는 걸 알게되어 공유해보고자 합니다.

아래 서버 트리거는 Microsoft SQL Server 2005 이상에서 사용할 수 있으며, 로그인 단계에서 조건에 맞지 않는 로그인 시도를 차단해버리는 내용입니다.

USE master;
GO

CREATE TRIGGER ssms_block_remote
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
            FROM sys.dm_exec_sessions AS es
            WHERE
                es.login_name = ORIGINAL_LOGIN() AND -- The current login
                es.host_name <> 'MAIN' AND -- Make sure it works only on remote connections
                es.program_name <> 'OurTestApp')
    ROLLBACK;
END;

WHERE 조건절에 보시면 es.host_name <> 'MAIN' 은 접속을 시도하는 곳의 컴퓨터 이름이 데이터베이스 서버 이름이 아닌 경우, 차단하라는 내용으로 즉, 이 부분을 적용할 데이터베이스 서버 이름으로 바꿔주셔야 합니다. 이 조건절로 인해서 로컬에서 접속하는 경우엔 아무 제한 없이 접속을 허용합니다.

그 아래의 es.program_name <> 'OurTestApp'의 경우 데이터베이스 연결을 시도하는 어플케이션 이름이 'OurTestApp'이 아닌 경우, 차단하라는 내용으로 이 부분 역시 상황에 맞게 바꿔주시면 되겠습니다.

데이터베이스 연결에 사용할 어플리케이션 이름은 데이터베이스 Connection String에서 지정하면 되는데, 대략 아래와 같은 형식이 되겠습니다.

SQL Server Native Client Connection String:
Data Source=YourServer;Initial Catalog=YourDB;Persist Security Info=False;User ID=YourId;Password=YourPasswd;Application Name=OurTestApp

OLEDB Connection String:
Provider=SQLOLEDB;Data Source=YourServer;User ID=YourId;Password=YourPasswd;Initial Catalog=YourDB;Application Name=OurTestApp

위 쿼리를 실행하고 Management Studio을 확인해보면 아래 그림처럼 SERVER -> Server Objects -> Triggers 항목 아래에 ssms_block_remote 서버 트리거를 확인할 수 있습니다.

이렇게 설정된 서버에 Management Studio를 사용해서 원격지에서 접속을 시도하면 아래와 같은 오류메시지가 나타납니다.

서버 트리거가 차단해버린 접속시도

본의 아니게 서버 트리거를 적용해놨던 것을 깜박 잊고 삽질할까봐, 친절하게도 트리거로 인해 차단됐다고 알려주는군요.