locked
fn_get_audit_file: Multiple Login Entries RRS feed

  • Question

  • Hello.  I posted this question on a different forum site and as of yet have not yet been able to isolate the problem.  I hope that I'm not violating any forum protocols by reasking this question again albeit with different information.  This appears to be a more appropriate site.

    I set up my audit (server & database) process in a file (as opposed to a Security or Application Log) and have been successful at viewing this data from the sys.fn_get_audit_file file (see tsql below).

    Select *
    From sys.fn_get_audit_file ('path',default,default)
    Where action_id in('DL','IN','SL','UP') --database oriented
          or action_id in('LGFL','LGSD','LGIS','LGIF')  -- server oriented

    Prior to running this extract, I ran four sql commands (i.e. update statement, insert statement, select statement & delete statement) and these four are showing up on my fn_get_audit_file extract.  However, the login records are enormous - some 2,000 already.  It appears that they are all action_id=LGIS.  The event_time on these entries are only miliseconds apart.  Sorta like my laptop is pinging the server several times per second and thus blowing up my login records.

    I've opened SQL Server Profile and it appears that each time I run a select statement referencing server data (like the fn_get_audit_fil select statement from above) a new login line is created.  As far as i know I'm not logging in but perhaps to SQL Server's point of view I am.

    Any thoughts?

    Dea


    Wednesday, April 15, 2015 1:31 PM

Answers

  • Follow Up:
    (excerpt from Pradeep Adiga @ http://blogs.msdn.com/b/sqlserverfaq/archive/2014/12/07/capture-successful-logins-you-may-see-many-entries-high-volume-when-you-create-audit-for-successful-login-group-action-group.aspx)
    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.

    Workaround:

    Unfortunately SQL Auditing does not have filtering option to remove connections coming from SSMS – object explorer etc.

    • Marked as answer by DeannaReynolds Thursday, April 16, 2015 12:01 PM
    Thursday, April 16, 2015 12:01 PM

All replies

  • Follow Up:
    (excerpt from Pradeep Adiga @ http://blogs.msdn.com/b/sqlserverfaq/archive/2014/12/07/capture-successful-logins-you-may-see-many-entries-high-volume-when-you-create-audit-for-successful-login-group-action-group.aspx)
    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.

    Workaround:

    Unfortunately SQL Auditing does not have filtering option to remove connections coming from SSMS – object explorer etc.

    • Marked as answer by DeannaReynolds Thursday, April 16, 2015 12:01 PM
    Thursday, April 16, 2015 12:01 PM
  • Hi Dea,

    Glad to hear that the issue is resolved. Thanks for your sharing, other community members could benefit from your solution.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Friday, April 17, 2015 1:33 AM