LOGIN Failed 조사하기

로그인이 실패하게 되면 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를 차단하는 처리가 필요할 것 같습니다.