With SQLAudit you cannot track HostName or IP address.

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Dear Everyone

    I am trying to create an audit to be able to capture hostname / ip addresses of servers which are connecting to the database server.

    I read online that

    "With SQLAudit you cannot track HostName or IP address."

     

    What else can I use to audit as we have one application and I need to check if its even connecting to the database or not so I was asked to provide the audit log but I was surprised to see that it didn't contain the server name or IP address.

    SQL server database is 2016 SP2 and the OS is windows 2012 R2

    Kal

  • anthony.green

    SSC Guru

    Points: 112358

    IIRC the SUCCESSFUL_LOGIN_GROUP action type has an additional information XML field, this contains the XML element <address> which is the IP of where the connection came from, so you would need to do some XML shredding to pull out the IP.

  • Grant Fritchey

    SSC Guru

    Points: 396288

    The host name is dependent on the connection string. If people leave this off, it's why you can't audit for it. You can use HOST_NAME to get this information if it's passed. You can probably, in most cases, I won't say all, get the IP address from sys.dm_exec_connections.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Hi Grant

    To audit from sys.dm_exec_connections don’t I need to create a table and insert all the logins there then I can see the IP for a later date?

    as I need to audit for a span of a week or more.

    Kal

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Yep. You'll have to do that. Or, you could use Extended Events. The connection_accept event has the IP address. Also, the existing_connection event captures all the available information for the existing set of data. This would all output to an XML file. You can query it using SSMS or load it into a table using extended stored procs and XQuery.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995648

    Grant Fritchey wrote:

    This would all output to an XML file. You can query it using SSMS or load it into a table using extended stored procs and XQuery.

    ...which is why I still hate EE. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Jeff Moden wrote:

    Grant Fritchey wrote:

    This would all output to an XML file. You can query it using SSMS or load it into a table using extended stored procs and XQuery.

    ...which is why I still hate EE. 😉

    HA!

    All I can say is, I can show you how to avoid most, but not all, of the XQuery stuff. However, I can't defend the XML. It's there. Honestly, at this point, I'd say it's the only real weakness. A second weakness is the lack of documentation on some of the events. Microsoft does such a great job with documentation these days. I'm not sure why Extended Events isn't getting the love, but it's not. However, that's a pretty minor issue. All the major events are extremely well documented. It's just some of the weird ones, you know, that people like you & me are going to want to look at, that are less than well documented.

    Come to the dark side Jeff, we have cookies, and beer, and a little XML.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    So if I understand all the notes here.

    SQLADUIT doesn’t audit properly

    EE isn’t documented properly

    so how do we audit sql server in addition to the error log file?

    id like to know what people do.

    Do you really setup database audits per database using specifications? I’d think that’s a lot of work.

    Kal

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Don't mistake my comments. XE is very well documented. Just not all the events. However, the events that most people are going to use, especially around auditing, are well documented.

    I use XE all the time to solve all sorts of interesting questions in and around SQL Server. It's a hyper useful tool. It's not simply a replacement for trace/profiler, it's a radical upgrade on 21 year old technology. Yes, I complain about some of the documentation and the XML, but these are me whinging, not excuses to avoid the technology. This is especially true if you're looking to audit your instances.

    Please, please, please, hear me on this. YOU SHOULD BE USING EXTENDED EVENTS BECAUSE IT IS A GREAT AND GOOD TECHNOLOGY THAT WILL HELP.

    I hope that's slightly more clear.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Ok Grant

    so I’ll drop the SQL audit and use EE to audit my instances.

    I’m a novice with EE so I’ll need to dig deep into them for auditing.

    Thank you for all the interesting information

     

    Kal

     

    Kal

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Forgot to mention I need to audit DDLs and DMLs so which solution is the best practice?

    I read the following are used:

    the Change Data Capture, Change Tracking or System-versioned Temporal Table methods

     

    which is the recommended method?

     

    kal

     

  • Grant Fritchey

    SSC Guru

    Points: 396288

    They're all different and do different things. I'd suggest reading up on all of them and then comparing them to your needs. Just saying DDL & DML isn't sufficient to make a suggestion.

    Temporal tables are more for tracking data changes over time for specific purposes. Take insurance as an example. Let's say you insure your house. The effective date, you're covered. Now, six months in, you tell the company you added a deck. They update their records with the new value and the effective date, two weeks ago. They'll be using a temporal table (maybe, for my example, they are) so that they have effective dates for everything. Because, a claim comes in. Someone fell at your house and broke their hip and their suing the insurance company. When did they fall? Before you had the deck, so the older property evaluation is what the claim is based on. It all gets really weird. But you get the general idea. Temporal tables aren't meant for just tracking data, but tracking data with effective dates, expiration dates & more. So, it's probably not on the list.

    As I said, read up more on the rest of it. Tracking all DML is hard, but, are you read, Extended Events can do it.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    ??

    let me get the audit scope from the CISO and business and then I’ll come back.

    thank you

    have a good weekend

    Kal

Viewing 13 posts - 1 through 13 (of 13 total)

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