Home / server / Questions / 229731
In Process
BIBD
Asked: 2011-02-02 08:39:04 +0800 CST

Is there a connection history for SQL Server?

  • 772

Is there a way to find the list of workstation that have connected to my SQL Server (2000) or to one of it's databases over its life time? Even a couple weeks would do for my purposes.

Is there a log file somewhere, or is it something I have to turn on?

sql-server

3 Answers

  1. You first need to enable connection auditing, which is not on by default. In Enterprise Manager, right-click on the server instance and choose properties. Then set the Audit Level option on the Security tab. When enabled SQL Server will log connection information into the SQL Server Log. You can also see connection information through SQL Server Profiler traces when auditing is enabled.

    Keep in mind that the server instance has to be reachable for connection events to be raised, obviously.

    If your server is not auditing connection attempts then you will have to revert to piecing together information from the Windows event logs.

    Have a read through SQL Server 2000 Auditing on Technet.

    • 4

  2. You can explore the following views:

     select * from sys.dm_exec_sessions 
 select * from sys.dm_exec_connections

    they keep connections from the time the server was started and are cleared upon restart

    • 3

  3. I use the following script to log connections but it builds up very fast. I recommend filtering it down further:

    USE [master]
GO
CREATE TABLE [dbo].[_log_connections](
    [session_id] [int] NULL,
    [connect_time] [datetime] NOT NULL,
    [net_transport] [nvarchar](40) NOT NULL,
    [encrypt_option] [nvarchar](40) NOT NULL,
    [auth_scheme] [nvarchar](40) NOT NULL,
    [client_net_address] [nvarchar](48) NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [tr_CheckLogin]
ON ALL SERVER
FOR LOGON
AS
BEGIN
    INSERT INTO TestDb.dbo._log_connections
    SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address
    from sys.dm_exec_connections c
    where c.session_id = @@SPID and @@SPID > 50
END
    • 0