This month’s T-SQL Tuesday topic is about extended events and comes from Jes Borland (B|T).

Like in most environments, we utilize a mixture of Windows logins and SQL logins.  Over the years, the amount of SQL logins we use has grown.  Knowing that it is a better practice to utilize windows accounts, we now want reduce the number of SQL logins.

So how do we do that? You could just disable the accounts and see what squawks, right?  The other alternative, which is what we choose, is to enable several types monitoring, which will allow us to see which accounts are being actively used.  Once we have a defined list, we can then weed out the inactive SQL logins as well as move the active ones to a windows account.

There are a couple of ways to do this that I’m aware of.

1. Profiler
2. Traces
3. Login Triggers
4. Extended Events
5. Audits
6. Others?

Each of these methods have their pros and cons, so I would advise that you consider each one carefully.

In full disclosure, I do not have much experience with extended events so I wasn’t even certain that these would work for what I wanted.  Thankfully, it turns out it did!

Brief History on Extended Events

In the beginning, in my opinion, extended events used to be a bear to utilize.  In 2008 R2 and older, there wasn’t an easy way to configure and utilize them.  There were ways around this, by using things such as add-in tools.

SQL Server 2014

With the release of SQL Server 2012, there is a GUI interface that allows you to create and configure an extended events session.

As you can see on the left, just expand the Management tree within SSMS, Extended Events and then Sessions.

By default, you will notice you have a system_health session already running.  This is very much like the black box default trace that started to show up in SQL Server 2005.

I will let you experiment on creating your own extended events session.  There are a number of resources on the internet that will help you accomplish this.

TL;DR

Instead of creating a very long blog post about how to create an extended events session, I have provided the script that I generated.

DISCLAIMER:  Please test accordingly. NEVER run code that you find on the internet against your Production systems without testing first. You have been warned!

IF EXISTS(SELECT 1 from sys.dm_xe_sessions where name = 'MonitorSQLLogins')
    BEGIN
   DROP EVENT SESSION [MonitorSQLLogins] ON SERVER
    END
GO
CREATE EVENT SESSION [MonitorSQLLogins] ON SERVER 
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.context_info,sqlserver.server_principal_name,sqlserver.session_id)
    WHERE ([sqlserver].[session_nt_user]=N'NULL')),
ADD EVENT sqlserver.login(
    ACTION(sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name)
    WHERE ([sqlserver].[session_nt_user]=N'')) 
ADD TARGET package0.event_file(SET filename=N'MonitorSQLLogins')
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.login',source=N'sqlserver.session_server_principal_name')
WITH (MAX_MEMORY=4096 KB
,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION MonitorSQLLogins
ON SERVER STATE = START
GO

There are a couple of things to note.

First, I did not want to capture all the logins, only the SQL logins.  In order to do that, I put a filter (WHERE clause) on the actions limited it to the session_nt_user of NULL or blank.  This effectively limits the list SQL logins, because a windows login would have a property value in this field.

Second, I am using a histogram target to keep track of the logins.  The histogram will aggregate the number of times a login occurred.  You could also use the ring_buffer or anevent_file target.  Given that I do not care about when the login occurred but just that it did, a histogram seemed more appropriate.

Testing

In order to test, I just created a SQL login named ‘LoginTest1’.   Once the account was created, I just simply started a new query window and authenticated using those credentials.

The Results

Once the extended event session is capturing data, the next hurdle is how to query it.  You can watch the live data stream or you can use this query:

SELECT name, target_name, CAST(st.target_data AS xml)
FROM sys.dm_xe_session_targets AS st
JOIN sys.dm_xe_sessions AS s
ON (s.address = st.event_session_address)
WHERE s.name = 'MonitorSQLLogins'

This query will return the target_data from the session as XML as shown below:

By clicking on the hyper link that is returned, you can see a better representation on what accounts are logging into your server.

In this case, the LoginTest1 user account has logged in twice.

One thing to note is the number of “buckets” shown here.  This represents the number of groups that will be tracked by the histogram.  In my case, since I am grouping by login, if there were more than 256 (which is the default) logins, the session would stop logging. Meaning any other logins, would not be captured regardless if it is a new group or not.  Given that I do not have more than 256 SQL logins on my server, this was not an issue for me.

You can adjust this value either by using the GUI when the session is created or by adjusting this line in the above script:

ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.login',slots=(512),source=N'sqlserver.session_server_principal_name')

If I wanted 512 buckets

Summary

Now that I’ve got a method to track SQL Logins, I can periodically check the histogram to see what accounts are logging into the instance.

Watch for a future blog post on how I’ll develop a process to extract this data into a more usable format!

If you have any interest in hosting one of these events, you can contact the founder, Adam Machanic.  He is always on the lookout for someone to host.  The only requirement is that you have participated in at least 2 T-SQL Tuesday events and you have an idea for a topic.