sql audit questions

Question

I have enabled MS SQL audit on a client server. Requirement is to collect audit logs - audit failed, successful logins, permission changes audit.
So as a start I created an instance level logging saving to a file.

Q1: Upon enabling auditing it created 3 logs files so far from last week. However it is not creating or generating any new files. I logged in to SSMS with a new user. Is there a delay till log gets generated?

Q2: SQL Instance level auditing - what types of audit categories it offers? My understanding is it should capture all logins, if I modify permissions on sql users etc.

Q3: Instance level auditing offers logs to a file/event logs. We will be required to push the logs to a cloud stack. When I opened one of the earlier files it's not in very readable format.

Down the track we may need to create database audits, based on what we are getting on instance level auditing.
Thanks

Answer 1

Hi @shaikh shoaib ,

> However it is not creating or generating any new files. I logged in to SSMS with a new user. Is there a delay till log gets generated?

Did you restart SQL server instance twice? When you restart the SQL server instance, it will generate a new SQL audit file. This is similar to SQL server error log, when SQL server restart it will general a new file. Or it will continue to log the information to one file.

And the user need to have the CONTROL SERVER permission to read a SQL Server Audit Log. Please refer to MS document View a SQL Server Audit Log.

> SQL Instance level auditing - what types of audit categories it offers? My understanding is it should capture all logins, if I modify permissions on sql users etc.

Yes, you are correct. For the detail audits on server level, please refer to MS document Server-Level Audit Action Groups to get detail information.

> Instance level auditing offers logs to a file/event logs. We will be required to push the logs to a cloud stack. When I opened one of the earlier files it's not in very readable format.

We can using sys.fn_get_audit_file:

SELECT * FROM sys.fn_get_audit_file ('PATHTOAUDITFILE \FILENAME.sqlaudit',default,default);    
GO   

Please refer to the MS document sys.fn_get_audit_file (Transact-SQL) to get more.

Or you can view the audit log by using the Log File Viewer in SSMS as below screenshot.

> Down the track we may need to create database audits, based on what we are getting on instance level auditing.

For the detail audits on database level, please refer to Database-Level Audit Action Groups to get more information.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2