Capture successful logins: You may see many entries(high volume) when you create Audit for SUCCESSFUL_LOGIN_GROUP action group

 

When you are auditing successful logins attempts using SUCCESSFUL_LOGIN_GROUP action group through SQL Audit, you will see that there will be too many entries in the audit file.

You may see this behavior for successful logins when connections are made through SQL Server Management Studio. This occurs because for every successful login through SSMS you will have other corresponding connections made at the background. For example

•   Object Explorer
•   Query Editor Window
•   Solution Explorer etc.

Hence successful logins for every such connection will also be recorded.

This holds true if you make successful login attempts using:

- Extended event
- SQL error log
- Profiler
- Audits
Whereas you will observe only entry recorded when connecting from sqlcmd.

Workaround:

Unfortunately SQL Auditing does not have filtering option to remove connections coming from SSMS – object explorer etc.  To achieve this you can use Extended Events with a filter on the 'client_app_name' field with Operator - 'Not like' and Value field -  'Microsoft SQL Server Management Studio'

Program Name - Microsoft SQL server Management Studio -- (for the internal connections like object explorer etc.)
Program Name - Microsoft SQL Server Management Studio - Transact-SQL IntelliSense -- (for IntelliSense)
Program Name - Microsoft SQL Server Management Studio - Query -- (For queries run via SSMS)

Program Name can be identified either by using SQL Profiler or by using following query:

 

image

 

image

Using Extended Events:

Information on how to use Extended Events:

https://msdn.microsoft.com/en-us/library/bb630282(v=sql.110).aspx

https://blogs.msdn.com/b/microsoft_press/archive/2012/03/21/from-the-mvps-a-gui-for-extended-events-in-sql-server-2012.aspx

 

Written by:
Shreyanka Mathapati – Support Engineer, SQL Server Support

Reviewed by:

Devashish Salgaonkar – Technical Lead, SQL Server Support
Akbar Farishta – Escalation Engineer, SQL Server Support