积极答复者
使用sys.dm_os_ring_buffers查看链接,SQLSERVER为什么也能记录链接情况

问题
-
使用下面的sql语句来查看曾经链接过sqlserver的链接
WITH RingBufferConnectivity as ( SELECT records.record.value('(/Record/@id)[1]', 'int') AS [RecordID], records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType], records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime], records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error], records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State], records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid], records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost], records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort], records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost] FROM ( SELECT CAST(record as xml) AS record_data FROM sys.dm_os_ring_buffers WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY' ) TabA CROSS APPLY record_data.nodes('//Record') AS records (record) ) SELECT RBC.*, m.text FROM RingBufferConnectivity RBC LEFT JOIN sys.messages M ON RBC.Error = M.message_id AND M.language_id = 1033 WHERE RBC.RecordType='Error' --Comment Out to see all RecordTypes ORDER BY RBC.RecordTime DESC
SQLSERVER是暴露在公网的,但是做了端口映射,公网的端口是23313,内网端口是1433
sql语句查询出来的结果
SQLSERVER错误日志里的某一条记录
如果公网连进来,应该链接23313这个端口,才能链接sqlserver,但是为什么人家从50490、1731、1424这些端口链接进来,sqlserver也可以记录呢?
答案
全部回复
-
Hi 桦仔,
我建议您可以收一个Network Monitor的日志来查看从远端连进SQL Server的连接的具体信息。
我尝试从我的10.172.19.57这台服务器访问SQL Server的服务器(IP:10.172.18.238 Port:58516),连接的时候我制造了一个login failure 的18456的报错。
从sys.dm_os_ring_buffers中我们可以看到RemotePort=58164; RemoteHost=10.172.19.57.
对应Netmon Monitor的日志,可以很清楚的看到两端的IP以及Port都是相对应的。
如果说在您这边的Network Monitor日志中也可以看到从50490,1731,1424这些remote port访问SQL Server所在的服务器。那么可能问题出现端口映射。
谢谢。
Doris JI