Post reply

Auditing SQL logins

  • November 29, 2006 at 3:22 pm

    #171109

    I’m notcing a few login failure under SQL server Logs: these users are not part of any database on SQL Server A.  SQL server Logs doesn't provide any info besides ie: Login failed for user 'xyz'.  Is there any way to find out failed user’s hostname or IP or where they coming from?

     

    Thanks

  • November 29, 2006 at 4:15 pm

    #674822

    Is there anything in the windows event log?

  • November 29, 2006 at 7:59 pm

    #674833

    If it's a Windows based login and you've got the Local Security Policy (or a Group Policy) which has the Audit Policy set to Success, Failure for Audit account logon events and Audit logon events, you may see something in the Security event log for the OS. If it's a SQL Server login that's failing, if you run a server side trace (see the trace stored procedures) or Profiler where you explicitly trap for Audit Failure you can see a hostname. However, that hostname can be spoofed. About the only way to be 100% sure is to put a sniffer on the line, preferably one that understands TDS packets.

     

    K. Brian Kelley
    @kbriankelley

  • December 28, 2006 at 10:26 am

    #680312

    After running the tracer it shows hostname connections within the network, please advice how to capture TDS packets, is this is something needs to be done by the network team?  Thanks

  • December 28, 2006 at 11:37 am

    #680331

    What is the reason you want capture TDS packets?

    I don't think there is any way in sql server but you can use NETWORK tools...

     

    MohammedU
    Microsoft SQL Server MVP

  • December 28, 2006 at 11:43 am

    #680334

    please see the reason above

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply