SQL Server 에 접속되어 있는 IP 찾기

DMBS에서 CMD 명령어를 사용하기 위해 cmdshell을 활성화 하는 것은 보안상 취약 할 수 있으므로

주의가 필요함.

 

 
 

-----------------------------------------

-- cmdshell Option Enable

sp_configure 'show advanced options', 1

RECONFIGURE

sp_configure 'xp_cmdshell', 1

RECONFIGURE

 

------------------------------------

--- Create Procedure

CREATE PROCEDURE usp_getboundAddresses

AS

BEGIN

 

SET NOCOUNT ON

 

IF OBJECT_ID('tempdb..#SomeTable') IS NULL

CREATE TABLE #SomeTable

(

ShellOutput VARCHAR(500)

)

 

INSERT INTO #SomeTable

EXEC xp_cmdshell 'nbtstat -a .'

 

SELECT

SUBSTRING( ShellOutput,

CHARINDEX('[',ShellOutput)+1,

-- LEN(ShellOutput) - CHARINDEX(']',ShellOutput) -2 --(EN)

 

LEN(ShellOutput) - CHARINDEX(']',ShellOutput) + 1 --(KR)

 

) AS BoundAddress

from #SomeTable

-- WHERE ShellOutput LIKE '%Node IpAddress%' --the important lines(EN)

 

WHERE ShellOutput LIKE '%노드 IpAddress%' --the important lines(KR)

AND ShellOutput NOT LIKE '%0.0.0.0%' --Unassigned addresses

 

DROP TABLE #SomeTable

 

END

 

------------------------------------------

-- Execute Procedure

 

exec usp_getboundAddresses

 

------------------------------------------

-- Result