안녕하세요.

 

늘 눈팅으로 도움만 받는 회원인데... 너무나 큰 일을 저질러버려서 염치불구하고 질문글만 남깁니다.

 

 

제가 하나의 DB서버를 사용하고 있는데요. 거기에 Database를 여러개 만들어서 쓰고 있었습니다.

 

그중에 한 데이터베이스에 접속을 할 때에만  특정IP 허용을 해주려고  구글링하고 검색하던중에  LOGON 트리거를 알게되었구요.

 

그래서 로그온 트리거를 걸었습니다....

 

 

미리 제 PC의 IP와 허용할 IP목록을 담은 ValidIP 테이블은 셋팅해 두고  트리거를 생성/가동 시켰는데,,

헌데 그 트리거에 약간의 실수가 있었나 봅니다..

 

터미널에서 작업하던 ssms,  제 개인pc에서 작업하던 ssms,  체크를 위해 열어뒀던 프로파일러 등등

모든 커넥션이 중지되어 버리더니  그뒤로는 무슨짓을 해도 접속을 할수가 없네요 ㅜㅜ

 

사용한 트리거는 다음과 같습니다.

 

 

 --접근허용목록테이블생성
 CREATE TABLE dbo.ValidIP (
 IP NVARCHAR(15),
 CONSTRAINT PK_ValidIP PRIMARY KEY CLUSTERED (IP)
 );
 GO
 INSERT INTO dbo.ValidIP (IP) VALUES ('114.70.79.238')
 INSERT INTO dbo.ValidIP (IP) VALUES ('210.101.203.46')
 GO
 
  USE MASTER
 GO

 

 


 CREATE TABLE Login_Info_Table (
   PostTime datetime,            -- 로그온 한 시간
   SID varbinary(85),            -- 로그인에 대한 보안 아이디(Security ID)
   ClientHost nvarchar(100),     -- 연결을 한 Client의 Host ID.
            -- 서버에서 연결할 경우 ‘<local_machine>’으로 저장됨
   LoginName nvarchar(100),      -- 로그온 명
   LoginType nvarchar(30),       -- 로그온 유형
   IsPooled tinyint              -- 연결 풀링 여부
 )
 GO

 

 --DDL Trigger 생성

 CREATE TRIGGER tr_logon_CheckIP
 ON ALL SERVER WITH EXECUTE AS 'ELTI_KUT'
 FOR LOGON
 AS
  BEGIN

      DECLARE @data XML
   SET @data = EVENTDATA()
 
   INSERT INTO Login_Info_Table(PostTime, SID, ClientHost, LoginName, LoginType, IsPooled)
   SELECT GETDATE(),
       @data.value('(/EVENT_INSTANCE/SID)[1]', 'varbinary(85)'),
       @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)'),
       @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
       @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(30)'),
       @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'tinyint')
                  
   IF IS_SRVROLEMEMBER('sysadmin') = 1 AND ORIGINAL_LOGIN()= 'ELTI_KUT'
   BEGIN
    DECLARE @IP NVARCHAR(15);
    SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
    IF NOT EXISTS(SELECT IP FROM ELTI_KUT.DBO.ValidIP WHERE IP = @IP)
    ROLLBACK;
   END;
  END;
 GO

 

 

여기서 위 트리거를 적용시키려 했던 Login 계정이  'ELTI_KUT' 입니다...

 

근데 제가 허용IP목록에 넣어둔 PC에서  ELTI_KUT로 접속을 하면 아래와 같은 메시지가 뜨면서 접속이 안되구요.

제목: 서버에 연결
------------------------------

000.00.239.217,1807에 연결할 수 없습니다.

------------------------------
추가 정보:

사용자 'ELKI_KUT'이(가) 로그인하지 못했습니다. (Microsoft SQL Server, 오류: 18456)

도움말을 보려면 다음을 클릭하십시오: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc="MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
단추:

확인
------------------------------

 

 

 

sa 계정으로 접속하려고 하면,,

 

제목: 서버에 연결
------------------------------

000.00.239.217,1807에 연결할 수 없습니다.

------------------------------
추가 정보:

트리거 실행으로 인해 로그인 'sa'에 대해 로그온하지 못했습니다.
데이터베이스 컨텍스트가 'master'(으)로 변경되었습니다.
언어 설정이 한국어(으)로 변경되었습니다. (Microsoft SQL Server, 오류: 17892)

도움말을 보려면 다음을 클릭하십시오: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc="MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
단추:

확인
------------------------------

이렇게 접속이 차단되면서  어떤방식으로도 DB에 접속이 안됩니다.

터미널에서 SQL SERVER 서비스 속성도   로컬시스템으로 바꿔서 Windows인증으로 접속해보려고 해도 트리거실행으로 인해 로그인이 막히구요...

 

접속자체가 안되니 Drop Trigger를 하던  중지를 하던 할 방법이 없습니다 ㅜㅜ

 

어떻게 하면 이 사고를 해결할 수 있을까요 ? ㅜㅜㅜㅜ

"