Reading SQL Server .sqlaudit Files Stored in S3

Jacob Mann 6 Reputation points
2022-02-02T22:25:55.21+00:00

I set up an audit in SQL Server that saves audit logs (.sqlaudit files) to RDS, which we then copy over to S3 using a standard process. All of the documentation I can find on reading these audit files uses the following query to read them from RDS:

SELECT *
FROM msdb.dbo.rds_fn_get_audit_file
('D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit'
,default,default)

RDS has an option setting called RETENTION_TIME for SQL Server Audit which allows you to specify a maximum of 840 hours before the files are removed from the RDS instance. I need to to be able to read the audit files for a longer period of time, so I am looking for a way to query the audit files in S3 instead, where they are retained.

One way may be to use sys.fn_get_audit_file, but I cannot do this with any user attached to this server that my organization has access to, even the admin user. None of the users have proper permissions for this and I don't see any way they can be granted.

With the .sqlaudit files sitting in the S3 bucket, how could I go about reading the files? Perhaps S3 Select could do it somehow, or maybe there's another obvious solution I'm overlooking?

Community Center Not monitored
{count} vote

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.