SQL Server Connections – How to Keep Track of All Connections and Disconnections

Here is a rundown of some of the options I have used to audit logins and logouts. I haven't had the chance to use Extended Events for auditing purposes yet. Below, I quickly describe tracking login and logout events using SQL Server tracing and SQL Server Audits. You'll probably end up going with setting up background server tracing which is described briefly below. If you need me to go into further detail, don't hesitate to ask.

Default Trace

The default trace is one way to audit activity on your server without making any changes. A post on what you can get out of the default trace is provided here.

Server Trace

If you are just looking for Login/Logout information, you can also create your own server trace to audit logins and logouts using SQL Server profiler. Just create a new trace and then export it as a SQL Server Trace definition file.

1. Open up SQL Server Profiler
2. Create a new trace using the blank template
3. Go to the Events selection tab
4. Under Security Audit, check Audit Login and Audit Logout

Run your trace, stop it and then under File, you can Export your trace as a SQL Server trace definition file. You can then run that trace in the background and save it to a file which can later be imported into a SQL Server table.

A portion of an example server Trace created by profiler:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 66, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
.....
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

Here is the output I got from just capturing those items:

It is possible to convert server traces into extended event sessions and that is described in detail here.

Server Audits

There are also server audits which allows you to track and monitor Database/Server level events. Auditing may only be available in the Enterprise and Datacenter editions. I can't seem to find a specific article from Books Online that says otherwise.

USE MASTER
GO

CREATE SERVER AUDIT audit_test                                           
TO FILE (FILEPATH = 'D:\Audit')
GO


CREATE SERVER AUDIT SPECIFICATION audit_test_spec       
FOR SERVER AUDIT [audit_test]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)
GO

You can then view server audit events in SSMS by going to Security -> Audits. Then using the context menu, you can select View Audit Logs to review activity.

For more information on audits, you will find additional information here:

CREATE SERVER AUDIT (Transact-SQL)

CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)