박상현MSSQL 의 경우네는 기본 설정값이 실패로그가 남게되어 있을 겁니다
이부분은 설정으로 설공/실패등 구분해서 로그에 기록되고 할수 있으니 관리자의 입맛에 맞게 설정후 사용
- 로그인 실패쿼리
create table #loginfailedtmp
(
LogDate date,
ProcessInfo varchar(2000),
MessageInfo varchar(max)
)
insert into #loginfailedtmp
EXEC sp_readerrorlog 0, 1, ‘Login failed’
select *,ClientIP= STUFF(MessageInfo,1, CHARINDEX(‘[클라이언트:’,MessageInfo)-1,”)
from #loginfailedtmp t
where MessageInfo like ‘%sa%’
drop table #loginfailedtmp
- 위 쿼리를 실행하면 사진과 같이 로그인이 실패한 결과중에서 sa MessageInfo컬럼에서 sa 문자열이 들어간 결과 값을 출력하게 됩니다
2. 기본 쿼리로 클라이언트 IP 와 몇번 실패했는지 로 변경되서 출력되게 쿼리 수정
MessageInfo 필드에서 20번이상 실패한 클라이언트만 추출
create table #loginfailedtmp
(
LogDate date,
ProcessInfo varchar(2000),
MessageInfo varchar(max)
)
insert into #loginfailedtmp
EXEC sp_readerrorlog 0, 1, ‘Login failed’
select SUBSTRING(MessageInfo, CHARINDEX(‘Ʈ: ‘,MessageInfo)+3, CHARINDEX(‘]’,MessageInfo) – (CHARINDEX(‘Ʈ: ‘,MessageInfo)+3)) AS IP,
COUNT(SUBSTRING(MessageInfo, CHARINDEX(‘Ʈ: ‘,MessageInfo)+3, CHARINDEX(‘]’,MessageInfo) – (CHARINDEX(‘Ʈ: ‘,MessageInfo)+3))) AS COUNT_IP
from #loginfailedtmp t
where MessageInfo like ‘Login failed for user %’
GROUP BY SUBSTRING(MessageInfo, CHARINDEX(‘Ʈ: ‘,MessageInfo)+3, CHARINDEX(‘]’,MessageInfo) – (CHARINDEX(‘Ʈ: ‘,MessageInfo)+3))
HAVING COUNT(SUBSTRING(MessageInfo, CHARINDEX(‘Ʈ: ‘,MessageInfo)+3, CHARINDEX(‘]’,MessageInfo) – (CHARINDEX(‘Ʈ: ‘,MessageInfo)+3))) >20
drop table #loginfailedtmp
3 배치파일등을 이용해서 스케줄링을 하면 자동으로 차단이 가능
아래에 있는 명령어는 윈도우 방화벽에서 추가되는 룰셋이며, Ipsec 등을 이용해서도 차단이 가능하다
EX)
for /f “tokens=1,2 delims= ” %%a in (login_block.txt) do (
echo %%a
netsh advfirewall firewall add rule name=”MSSQL접근차단” dir=in interface=any action=block protocol=TCP localport=1433 remoteip=%%a
)