主页 / dba / 问题 / 130977
Accepted
RayofCommand
Asked: 2016-03-03 00:24:17 +0800 CST

查找访问特定 SQL Server 的所有 IP 或用户

  • 772

假设我找到了一个 SQL Server，但我不知道哪些应用程序连接到它。也许我找到了一个应用程序，但我不确定它是否是唯一使用它的应用程序。

有没有找到所有不同连接的好方法？

sql-server sql-server-2012

4 个回答

  1. Best Answer

    您可以在 Activity Monitor 中查看 SSMS 中当前正在运行的进程。

    您还可以使用sys.dm_exec_sessions以及sys.dm_exec_connections和sys.dm_exec_requests 来获取它。

    询问：

    SELECT DB_NAME(database_id) as [DB]
    , login_name
    , nt_domain
    , nt_user_name
    , status
    , host_name
    , program_name
    , COUNT(*) AS [Connections]
FROM sys.dm_exec_sessions
WHERE database_id > 0 -- OR 4 for user DBs
GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;

    主机名、进程名或域用户名通常提供比 IP 地址更多的信息。可以添加其他列，例如login_time：last_successful_logon

    IP 和其他信息可以通过 JOIN 检索到sys.dm_exec_connections on session_id: client_net_address, local_net_address, connect_time, ports, ...

    sys.dm_exec_requests也可以提供有用的信息：commandtype, sql_handle, ...

    此查询只是一个示例。您必须JOIN将这 3 个视图放在一起，并从其中任何一个中输出/存储相关信息。

    当 SQL Server 重新启动时，这些视图中的数据会消失。因此，如果应用程序没有定期连接，最好使用作业或脚本 (Powershell) 定期存储它们。

    • 16

  2. 我还会建议 Julien 的建议，但我想通过 SQL Server 代理安排查询会更好，然后每次运行时您都将信息转储到物理表中。两个 DMV 加入sys.dm_exec_connections并sys.dm_exec_sessions

    就像首先创建一个物理表

    create table Session_Information 
( session_id int,
net_transport nvarchar(40),
host_name nvarchar(128),
program_name nvarchar(128),
nt_user_name nvarchar(128), 
connect_time datetime,
client_interface_name  nvarchar(128),
client_net_address varchar(48),
local_net_address varchar(48),
login_name nvarchar(128),
nt_domain nvarchar(128),
login_time datetime
);

    然后将记录插入到该表中：

    Insert into Session_Information
  SELECT 
      c.session_id, 
      c.net_transport, 
      s.host_name, 
      s.program_name, 
      s.nt_user_name,
      c.connect_time, 
      s.client_interface_name,
      c.client_net_address,
      c.local_net_address, 
      s.login_name, 
      s.nt_domain, 
      s.login_time 
  FROM sys.dm_exec_connections AS c
  JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id;

    安排它每隔 15 分钟左右通过 SQL Server 代理运行一次，在一天结束时，您可以从表中看到您喜欢的所有内容session_information。

    • 4

  3. 为成功和失败的登录启用 SQL Server 登录审核：https ://learn.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio

    更改审计范围需要重新启动 SQL 服务。重新启动服务后，所有成功的登录（用户）及其 IP 地址都将开始记录在 SQL Server 日志中。可以收集大约一到两周的数据，然后分析日志文件以列出在监控期间访问过 SQL Server 数据库的所有 IP 地址（和用户）。

    如果 SSRS 组件也安装在同一个盒子上，则可以分析 ReportServer 数据库的 ExecutionLog 表以列出访问 SSRS 门户的所有登录。

    • 2

  4. 来自Sarvesh Kumar Gupta在 SQL Server中获取客户端 IP 地址：

    CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

   SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

   Return @IP_Address;
END
    • -2

相关问题