With SQLAudit you cannot track HostName or IP address.

Question

With SQLAudit you cannot track HostName or IP address.

hurricaneDBA

SSCertifiable

Points: 5027
More actions
January 16, 2020 at 6:48 am
Go to Answer
#3715888

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
Grant Fritchey

SSC Guru

Points: 398908
More actions
January 17, 2020 at 1:26 pm
Answer
#3716281

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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 15 (of 22 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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: 398908 More actions · Answer 2

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

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.

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: 398908 More actions · Answer 5

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 6

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: 398908 More actions · Answer 7

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 8

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

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 9

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: 398908 More actions · Answer 10

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 11

??

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

thank you

have a good weekend

Kal

mtz676 SSCrazy Eights Points: 9217 More actions · Answer 12

Hi All,

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.

@Grant Frritchey - Is the above statement referring to SQL Audit or Extended event

@Grant Fritchey & All

Using SQL Audit I can audit only the relevant SQL Login activity (Like a select/insert etc..DML).When I audit there is Only 1 record in the result set.Also can I capture hostname and client app here as in extended events.

Extended events capture all information, but there are several records when I try audit a login.How I can filter everything and have only 1 record in the result set.(capture only the relevant select/DML) as in SQL audit.Any references can help.

If you answers or references that can help, please share.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 13

Without seeing what you're talking about specifically, the session you have defined, I can't say what you're seeing or why.

Yes, Extended Events captures this information. In fact, unknown to most, SQL Audit is just using Extended Events behind the scenes.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning