MSSQL Full Query Logging / Audit Abilities

Question

I'm looking for the ability to get full query logs in MSSQL for auditing purposes. I've attempted using the SQL Server Audit ability, however I'm running into a problem where the parameter name is being stored instead of the real parameter value. Very similar to this issue which appears to be a known limitation of audit logging https://learn.microsoft.com/en-us/answers/questions/1497827/in-sql-auditing-parameter-real-values-are-not-show and https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16#:~:text=When%20the%20query%20processor%20parameterizes%20the%20query%2C%20the%20parameter%20can%20appear%20in%20the%20audit%20event%20log%20instead%20of%20the%20column%20values%20of%20the%20query.

Is there a different way to pass those parameters that would allow the audit to view the real values?

Is there a way to get logging of those full queries in an audit capacity, or is that something that needs to be handled by 3rd party tools such as Apex SQL Log?

Thanks!

Answer 1

Is there a different way to pass those parameters that would allow the audit to view the real values?

Well, rather than using parameterised statements, build query strings with the values inlined. Which is bad practice for a number of reasons, so don't do that.

If you use Trace or Extended events to capture the actual calls sent to SQL Server, you can get the parameter values that way.

But if you are using stored procedures, and the query variables had their values set inside the stored procedure, it is getting difficult. In that case, the only option may to put the auditing inside the procedure code itself.

Answer 2

Hi @Weston S,

Thank you for reaching out and welcome to Microsoft Q&A.

Do you mean you need to log all queries and actions performed in any database?

As Erland says, that would be either tracing (using either extended events directly or SQL Trace) or SQL Server Addit (which sits on top of Extended Events). You can create a trace and trace "Audit Schema Object Access Event" that will give you a statement level audit.

Then you can filter on objects or databases to minimize the logs and create the trace as a server-side trace.

It seems that the Audit Schema Object Access Event cannot use extended events, but the server-side trace can be set up to stop the server if logging is no longer possible.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications