Reading SQL Server .sqlaudit Files Stored in S3

Question

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?

Answer 1

Depending on the format of the audit file you could query it directly with AWS Athena from S3 if it’s a supported format. If the format isn’t directly supposed by AWS Athena you can use AWS Glue to do a transformation as some of the answers on SO suggest.

Answer 2

Assuming you have read-access into S3....

There are ways (e.g. CloudBerry Drive, TntDrive) to mount S3 as a drive letter.

If you had a server with S3 mounted as a drive letter (for all users), which also hosted an Instance of SQL Server, you should be able to query the files using sys.fn_get_audit_file().

It wouldn’t be the same as querying the files in S3, because each file would need to be downloaded in the background before being opened, but it might be viable depending on the file size and network throughput. A server in AWS 'close' to S3 would be advantageous.