로그인이 실패하게 되면 SQL Server 로그에 다음과 같은 내용이 기록됩니다.Login failed for user 'DBUser'. 원인: 암호가 제공된 로그인의 암호와 일치하지 않습니다. [클라이언트: 127.0.0.1]SQL SERVER의 모든 Log파일을 조사하여 Login Failed를 찾는 스크립트 입니다. 모르는 IP에서 잦은 Login Failed가 있다면 해킹이 의심되므로 방화벽에서 차단하던지 LOGON 트리거에서 제한하던지 해야 할 것입니다. 저희 회사는 sa를 Disable 했는데도 불구하고 sa로 로긴 시도가 있었습니다. 이런 IP들은 가차없이 Logon 트리거에서 차단해버려야겠죠. 스크립트는 수정없이 그냥 붙여넣고 실행하면 됩니다.SET NOCOUNT ON DECLARE @MaxErrorLogCnt INT, @LogCnt INT DECLARE @ErrorLogs TABLE (NO INT, DATE DATETIME, SIZE INT) DECLARE @Errors TABLE (LogDate DATETIME, ProcessInfo NVARCHAR(1000), ErrMsg NVARCHAR(MAX)) INSERT @ErrorLogs EXEC xp_enumerrorlogs SET @MaxErrorLogCnt = (SELECT MAX(NO) FROM @ErrorLogs) SET @LogCnt = 0 WHILE @MaxErrorLogCnt > @LogCnt BEGIN INSERT @Errors EXEC sp_readerrorlog @LogCnt, '1', 'Login failed', 'for user' SET @LogCnt = @LogCnt + 1 End UPDATE @Errors SET ErrMsg = REPLACE(ErrMsg, CHAR(10), ' ') UPDATE @Errors SET ErrMsg = REPLACE(ErrMsg, '[', '') UPDATE @Errors SET ErrMsg = REPLACE(ErrMsg, ']', '') UPDATE @Errors SET ErrMsg = REPLACE(ErrMsg, 'Reason', '원인') SELECT LogDate , Login = SUBSTRING(ErrMsg, 24, PATINDEX('%.%', ErrMsg) - 25) , Client = SUBSTRING(ErrMsg, PATINDEX('%클라이언트%', ErrMsg) + 7, 15) , Reason = SUBSTRING(ErrMsg, PATINDEX('%원인%', ErrMsg) + 4, PATINDEX('%클라이언트%', ErrMsg) - PATINDEX('%원인%', ErrMsg) - 5) FROM @Errors ORDER BY LogDate DESC /* 이하는 업무일지용 */ DECLARE @RESULT NVARCHAR(MAX) = '' SELECT @RESULT = @RESULT + 'LogDate : ' + CONVERT(VARCHAR(20), CONVERT(DATETIME, LogDate), 120) + CHAR(10) + 'Login : ' + SUBSTRING(ErrMsg, 24, PATINDEX('%.%', ErrMsg) - 25) + CHAR(10) + 'Client : ' + SUBSTRING(ErrMsg, PATINDEX('%클라이언트%', ErrMsg) + 7, 15) + CHAR(10) + 'Reason : ' + SUBSTRING(ErrMsg, PATINDEX('%원인%', ErrMsg) + 4, PATINDEX('%클라이언트%', ErrMsg) - PATINDEX('%원인%', ErrMsg) - 5) + CHAR(10) + 'DBA 처리 내용 : ' + CHAR(10) + CHAR(10) FROM @Errors WHERE LogDate >= DATEADD(DAY, -1, CONVERT(CHAR(10), GETDATE(), 120)) ORDER BY LogDate DESC SELECT LOG = @RESULT실행 결과는 다음과 같습니다. 결과를 살펴보면 DBUser가 두번의 로그인 실패를 했고 DBReader는 없는 계정인데 누군가가 로그인 시도를 한것입니다. 방화벽 정책을 추가하거나, 로그온 트리거에서 해당 IP를 차단하는 처리가 필요할 것 같습니다.
2014년 4월 9일 수요일
LOGIN Failed 조사하기
피드 구독하기:
댓글 (Atom)
클라이언트가 외부IP라면 어떻게든 해보겠는데, local machine일 경우는 어떻게 알 수 있나요?
답글삭제