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
Whereas you will observe only entry recorded when connecting from sqlcmd.
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:
Using Extended Events:
Information on how to use Extended Events:
Shreyanka Mathapati – Support Engineer, SQL Server Support
Devashish Salgaonkar – Technical Lead, SQL Server Support
Akbar Farishta – Escalation Engineer, SQL Server Support