database auditing specification

Question

database auditing specification

SQL Galaxy

One Orange Chip

Points: 29472
More actions
January 25, 2024 at 5:54 am

#4367402

Hi.
In database audit specification mentioned principal name - SQL user which all DML transactions captured by that particular user.
I want capture all users like widows authentication and SQL authentication both. what principal name need to be mention?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Set it as public.

Then in the audit it will show you the "Session Server Principal Name" and "Server Principal Name" of the person running the audit.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thank you..

CREATE DATABASE AUDIT SPECIFICATION [Audit1]

FOR SERVER AUDIT [audit_Logs]

ADD (DELETE ON DATABASE::[DBNAME] BY [public]),

ADD (EXECUTE ON DATABASE::[DBNAME] BY [public]),

ADD (INSERT ON DATABASE::[DBNAME] BY [public]),

ADD (DATABASE_OBJECT_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (UPDATE ON DATABASE::[DBNAME] BY [public])

WITH (STATE = ON)

The above parameters included in database audit logs. Does it capture DDL statement for create object, trigger, proc ?

Thanks

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

It should, take a look at the audit action groups and what each on means and use the right ones you need to audit.

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16#database-level-audit-actions

Also remember why you're auditing, if it is for security compliance with various legal regulations and you need to unequivocally stand up in a court of law and say this is the truth the whole truth and nothing but the truth based on this audit data, that Microsoft SQL Server Auditing IS NOT the tool you should be using.

Anyone with the right permissions can modify a .SQLAUDIT file, change a audit specification to hide their tracks.

If you must be legally protected by these audits, you need to use a man in the middle appliance like Imperva or Guardium.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi.

I understood your commends.

Database audit logs captured but I can see that event_time does not show the correct time. Actually it shows 5 hours earlier than time. is it logs are captured slow?

Thanks

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

Logs are captured at server local time. So if it is 5hours earlier, then the server must be set as your local time -5 hours.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Thanks for your reply..

By mistakenly posted commends like 5 hours earlier. it seems 5 hours back time from server current time.

I think, database audit logs stored in audit file date format is UTC time frame.

Is there option fetching data very fast due to SELECTING records from audit logs file it takes more than 45 seconds of 12,500,000 records.

Thanks.

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

Ah that time thing may make sense.

All our servers and clients are in UTC so they all match the time stamps in the logs.

As for reading that much data, well yeah reading 12.5 million rows from disk to client is going to take some time.

Probably best off writing a query to do a date limit, or go change the way you audit to be to the security log and get a syslog server to pull the events for you into a quicker queryable fashion.