Reading SQL Server .sqlaudit Files Stored in S3



  • 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?



  • Depending on the format of the audit file you could query it directly with https://aws.amazon.com/athena/faqs/ from S3 if it’s a supported format. If the format isn’t directly supposed by AWS Athena you can use https://aws.amazon.com/glue/faqs/ to do a transformation as some of the answers on https://stackoverflow.com/questions/55284580/how-to-query-nested-xml-file-in-aws-athena-via-glue suggest.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2