Similar to a past issue I had with audited queries not showing when using Extended Events, I'm having an issue with queries not logging when using C2 Auditing on an RDS SQL Server instance. (I would prefer to use Extended Events for my auditing, but this apparently cannot be enabled on RDS SQL Server instances currently.)
In this case, I enabled C2 Auditing via an RDS Parameter Group. I verified that C2 auditing is enabled by going to my server instance's Properties (via SSMS). I created a database, mytestdb, and then created a table within it, mytesttable. I ran a few queries (e.g., INSERT INTO mytesttable ...; SELECT * FROM mytesttable).
I then determined the path to my trace log file (SELECT id, path, is_default FROM sys.traces;) and then retrieved the SELECT queries that have been run: SELECT starttime, textdata FROM fn_trace_gettable('D:\RDSDBDATA\Log\log_2.trc', DEFAULT) WHERE textdata LIKE N'select%' ORDER BY starttime;.
The result is my queries on mytesttable did not show up. My queries on the trace table do show up though, so some queries are being logged.
I also tried a query on one of the master database tables, but that didn't show up as well. I also tried disconnecting from the database server and reconnecting (in SSMS), hoping that ending the session might clear some buffering going on, but that didn't help with getting the queries to show in the audit results.
I connected to the RDS instance with my "Admin" user account (created during RDS setup) and there is no associated Active Directory. This is an "SQL Server Standard Edition" RDS instance.
Any ideas on why these queries would not be logged? Any other debugging ideas? Thanks!
Best Answer
You have to query file located on D:\RDSDBDATA\DATA\audittraceXXX.trc, It is the first query you ran to get trace path