SQL Server 取得客戶端 IP、MAC 位址與 IP 格式化實用腳本

1. 取得用戶端 IP 位址

DECLARE @ip VARCHAR(20), @hst VARCHAR(20), @sql VARCHAR(100), @str VARCHAR(100)
SET @str = 'PING ' + HOST_NAME()

CREATE TABLE #tmp (aa VARCHAR(200))
INSERT #tmp EXEC master..xp_cmdshell @str

SELECT TOP 1 @ip = REPLACE(LEFT(aa, CHARINDEX(':', aa) - 1), 'Reply from ', '')
FROM #tmp WHERE aa LIKE 'reply from %:%'

DROP TABLE #tmp
SELECT @ip AS ClientIP

原理： 呼叫 xp_cmdshell 執行 PING 指令，解析輸出文字擷取 IP。需啟用 xp_cmdshell 且具備權限。

2. 取得網卡實體位址 (MAC)

CREATE TABLE #tb (re VARCHAR(255))
INSERT INTO #tb EXEC master..xp_cmdshell 'ipconfig /all'

SELECT SUBSTRING(re, CHARINDEX(':', re) + 1, 255) AS MacAddress
FROM #tb WHERE re LIKE '%Physical Address%'

DROP TABLE #tb

3. IP 位址段標準化格式化（每段補零至 3 位）

CREATE FUNCTION dbo.fn_FormatIP (@ip VARCHAR(15))
RETURNS VARCHAR(15)
AS BEGIN
    DECLARE @result VARCHAR(15) = '', @part VARCHAR(4), @pos INT
    WHILE CHARINDEX('.', @ip) > 0 BEGIN
        SET @part = LEFT(@ip, CHARINDEX('.', @ip) - 1)
        SET @result = @result + RIGHT('000' + @part, 4)  -- 含點號共 4 字元
        SET @ip = RIGHT(@ip, LEN(@ip) - CHARINDEX('.', @ip))
    END
    SET @result = @result + RIGHT('000' + @ip, 3)  -- 最後一段無點號
    RETURN @result
END
GO

-- 測試
SELECT dbo.fn_FormatIP('202.1.110.2')  -- 回傳 '202.001.110.002'

應用場景

• 稽核記錄：記錄操作者真實 IP/MAC。
• IP 範圍比對：標準化後可直接字串比較或 BETWEEN 篩選。
• 異常登入偵測：比對註冊 MAC 與當前 MAC。

安全性提醒

1. xp_cmdshell 極高風險，生產環境預設停用，若需用請嚴格限制執行帳號權限。
2. PING 法依賴 ICMP 回應，防火牆可能封鎖導致失敗。
3. 現代應用建議從應用層（HTTP Header `X-Forwarded-For`、連線屬性）取得 IP，避免在資料庫層執行系統指令。