특정 IP로 실행한 쿼리 추적

가끔 SQL Server 로그에 특정한 IP를 이용하여, 접속을 시도하였으나 실패한 메시지나 특정 IP에서 어떠한 공격이 들어옵니다.

IP를 통해서 Profiler를 통해서 뭐가 실행되었는지 확인하고자 하였으나, IP로 추적은 없는 듯 하여, 구글링을 하여 찾았는데 좋은 정보가 있어서 공유하고자 합니다.

해당 내용은 http://mani4u.tistory.com/41 블로그를 참조하였으며 약간의 변형만 줘봤습니다.

우선 쿼리는 다음과 같습니다.

SELECT P.spid,E.name as DBname,P.login_time, P.last_batch, P.status,P.program_name,P.cmd,P.loginame,C.client_net_address,D.text
FROM sys.sysprocesses AS P 
INNER JOIN sys.dm_exec_connections AS C
ON P.spid = C.session_id
CROSS APPLY sys.dm_exec_sql_text (P . sql_handle) D
INNER JOIN sys.databases AS E
on D.dbid = E.database_id
-- WHERE client_net_address IN ('검색IP')
ORDER BY login_time

WHERE 부분은 특정한 IP를 검색하기 위해 쓰는 것이므로, 주석을 제거하고 사용하시면 됩니다.

우선 해당 쿼리는 sys.sysprocesses의 정보를 토대로 다른 dmv를 붙여서 정보를 추출하는 방식입니다. 해당 쿼리는 다음과 같은 결과를 보여줍니다.

위의 결과는 다음과 같습니다.

① spid : 세션의 ID 

② DBname : Database 명

③ login_time : 클라이언트 프로세스가 서버에 로그인한 시간

④ last_batch : 마지막으로 프로세스가 실행된 시간

⑤ status : 상태 (원본 그대로 가져왔습니다.) https://technet.microsoft.com/en-us/library/ms179881(v=sql.110).aspx

dormant = SQL Server is resetting the session.

running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see 

background = The session is running a background task, such as deadlock detection.

rollback = The session has a transaction rollback in process.

pending = The session is waiting for a worker thread to become available.

runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum.

spinloop = The task in the session is waiting for a spinlock to become free.

suspended = The session is waiting for an event, such as I/O, to complete.

⑥ program_name : 실행중인 프로그램의 이름

⑦ cmd : 실행 중인 명령

⑧ loginame : 로그인 계정

⑨ client_net_address : 해당 실행 프로세의 IP 

⑩ text : 실행중인 SQL 구문 

해당 내용을 변형하면 여러가지 정보를 볼 수 있으며, 나아가 해당 쿼리의 실행결과를 테이블에 담아서, 모니터링도 가능 합니다. 

그 변형은 각자가 하는것이 좋을 것으로 보여집니다.

감사합니다.