With SQL Server 2008 and 2008R2 limping along and becoming terminally ill (End of Life Support was July 9, 2019), it should come as no surprise that it is recommended to migrate/upgrade affected databases/servers to newer technology.
Planning to upgrade/migrate requires a fair amount of prep work. Some of that prep work involves auditing your server for any users that may still be using the instance.
Where does one even begin in order to audit those logon events in SQL 2008 or 2008R2? Some may say to use a SQL Server Audit but that feature is an Enterprise only feature for 2008 and R2. If we were on a newer version of SQL Server, Extended Events would be the easy choice. Unfortunately, XE was not adequately mature on 2008 or R2. XE just doesn’t have the appropriate events to use in these versions of SQL Server. What’s left?
How about a server side trace? Yes, that was a bit difficult to say. Server side trace does have just the right events for us to use to discover who is using the server and which principals can be eradicated. A big benefit here is that a server side trace does not come with a service outage as would be required for other methods.
Server Side Trace
How do we go about creating a trace to capture these events? It is common knowledge that using a script to create a server side trace is not very intuitive. It is also not very plausible to run a Profiler session and leave it running for weeks while you do your due diligence. There is a shortcut available that allows us to run a server side trace but it does require the use of Profiler – for just a tiny bit. You can start here to find how to do that.
Great, we have a method to create the script. What needs to go into this session? Let’s take a look at that. Ignoring the initial steps to start a profiler session (other than to use a blank template), let’s jump to the event selection tab. From there, we will make two selections (Logon and Login Failed in the Security Audit section) as shown in the next image.
Once the events are selected, go ahead and click run. From there you may stop the session and then continue with the instructions from the previous link on how to script a profiler session.
After scripting this session, I get a script that looks like the following.
/****************************************************//* Created by: SQL Server 2019 CTP2.4 Profiler *//* Date: 07/12/2019 02:36:40 PM *//****************************************************/ -- Create a Queue DECLARE @rc INT; DECLARE @TraceID INT; DECLARE @maxfilesize BIGINT; SET @maxfilesize = 5; -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access to your network share EXEC @rc = sp_trace_create @TraceID OUTPUT , 2 --option of 2 = trace rollover , N'c:databasexeLogonAttempts' , @maxfilesize , NULL , 10 --number of rollover files = 10 (last parameter passed to proc) ; --parameters are: traceid, options, filename, max file size, stop time, file count 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, 2, @on; EXEC sp_trace_setevent @TraceID, 14, 3, @on; EXEC sp_trace_setevent @TraceID, 14, 6, @on; EXEC sp_trace_setevent @TraceID, 14, 7, @on; EXEC sp_trace_setevent @TraceID, 14, 8, @on; EXEC sp_trace_setevent @TraceID, 14, 9, @on; EXEC sp_trace_setevent @TraceID, 14, 10, @on; EXEC sp_trace_setevent @TraceID, 14, 11, @on; EXEC sp_trace_setevent @TraceID, 14, 12, @on; EXEC sp_trace_setevent @TraceID, 14, 14, @on; EXEC sp_trace_setevent @TraceID, 14, 21, @on; EXEC sp_trace_setevent @TraceID, 14, 23, @on; EXEC sp_trace_setevent @TraceID, 14, 25, @on; EXEC sp_trace_setevent @TraceID, 14, 26, @on; EXEC sp_trace_setevent @TraceID, 14, 35, @on; EXEC sp_trace_setevent @TraceID, 14, 41, @on; EXEC sp_trace_setevent @TraceID, 14, 49, @on; EXEC sp_trace_setevent @TraceID, 14, 51, @on; EXEC sp_trace_setevent @TraceID, 14, 57, @on; EXEC sp_trace_setevent @TraceID, 14, 60, @on; EXEC sp_trace_setevent @TraceID, 14, 64, @on; EXEC sp_trace_setevent @TraceID, 14, 66, @on; EXEC sp_trace_setevent @TraceID, 20, 1, @on; EXEC sp_trace_setevent @TraceID, 20, 3, @on; EXEC sp_trace_setevent @TraceID, 20, 6, @on; EXEC sp_trace_setevent @TraceID, 20, 7, @on; EXEC sp_trace_setevent @TraceID, 20, 8, @on; EXEC sp_trace_setevent @TraceID, 20, 9, @on; EXEC sp_trace_setevent @TraceID, 20, 10, @on; EXEC sp_trace_setevent @TraceID, 20, 11, @on; EXEC sp_trace_setevent @TraceID, 20, 12, @on; EXEC sp_trace_setevent @TraceID, 20, 14, @on; EXEC sp_trace_setevent @TraceID, 20, 21, @on; EXEC sp_trace_setevent @TraceID, 20, 23, @on; EXEC sp_trace_setevent @TraceID, 20, 26, @on; EXEC sp_trace_setevent @TraceID, 20, 30, @on; EXEC sp_trace_setevent @TraceID, 20, 31, @on; EXEC sp_trace_setevent @TraceID, 20, 35, @on; EXEC sp_trace_setevent @TraceID, 20, 49, @on; EXEC sp_trace_setevent @TraceID, 20, 51, @on; EXEC sp_trace_setevent @TraceID, 20, 57, @on; EXEC sp_trace_setevent @TraceID, 20, 60, @on; EXEC sp_trace_setevent @TraceID, 20, 64, @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; error: SELECT ErrorCode = @rc; finish: GO
I created this script from SSMS 18.0 and find it interesting that the script says “Created by: SQL Server 2019 CTP2.4 Profiler”. Despite the very recent version of SSMS used to create this script, this script will work perfectly fine on SQL Server 2008 or R2.
Once I start the trace on a server, I am ready to do just a little bit more. I want to verify what this script means. Afterall, it is a bunch of numeric values. Let’s look at that with this next query and then compare it to the initial script used to create the trace. The following query requires SQL 2012 or later.
SELECT t.idAS TraceId , t.event_count , te.name AS EventName , te.trace_event_id , tc.name AS ColumnName , tc.trace_column_id FROM sys.tracest CROSS APPLY sys.fn_trace_geteventinfo(t.id) gei INNER JOIN sys.trace_events te ON gei.eventid = te.trace_event_id INNER JOIN sys.trace_columns tc ON gei.columnid = tc.trace_column_id WHERE t.path LIKE '%LogonAttempts%' ORDER BY te.trace_event_id, tc.trace_column_id;
This produces output similar to the following.
As you scroll through the list, you can see the Event Name along with the column name for all events/columns that have been added to the trace. I have highlighted the first event / column mappings to illustrate this relationship.
Cool! Now, I know the session does indeed contain the requisite data that I wanted so it is time to start checking to see what is happening on the server.
SELECT fgt.TextData,
d.name AS DBName,
fgt.HostName,
fgt.ApplicationName,
fgt.LoginName,
fgt.StartTime,
fgt.EventSubClass,
fgt.Success,
fgt.ServerName,
fgt.EventClass,
fgt.Error,
fgt.DatabaseName,
fgt.TargetUserName,
fgt.LoginSid,
fgt.TargetLoginName,
fgt.LinkedServerName,
fgt.Type,
fgt.IsSystem,
fgt.SourceDatabaseID,
fgt.SessionLoginName
FROM sys.fn_trace_gettable('c:databasexeLogonAttempts.trc',DEFAULT) fgt
INNER JOIN sys.databases d
ON d.database_id = fgt.DatabaseIDNow you are all set to go to start figuring out which logins (if any) are still connecting to the server and using databases on that server.
The Wrap
This article has shown how to audit the logon events for a SQL 2008/R2 instance. It also happens to be an excellent follow up to another recent article – here. As you begin to plan your migration off of the SQL 2008 dinosaur, it is essential to baseline the activity and use of the server. This audit session will help you do exactly that!
This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.