Microsoft SQL audits don't work at all, HELP!!

Question

I am all day trying to make Audit return logs, but always returns this error: "item has already Been added. Key in dictionary: 'mndo' Key Being added: 'mndo'"

I used these commands to test with a "test" user.

USE Clinica_Medica
SELECT * FROM TEST WHERE Nome = 'OI';

In the Audit log it returns empty and with this error, I tried with the User SA standard, with global policies and the same happens.

My build:

Both are enabled.

Answer 1

I run into the same problem, but found a workaround. I'm currently testing on a docker image of SQL Server 2022

select @@version

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.5 LTS) <X64>

But I had the problem also on other versions (windows based, SQL Server 2019, ...) I think the main problem is Management Studio, I'm running

SQL Server Management Studio 19.0.20209.0+f9a9d8f6

Microsoft .NET Framework 4.0.30319.42000

Operating System 10.0.19045

If I try to consult the audit with the SSMS viewer, I get the same error message as described above. But good news: I can read the log via sys.fn_get_audit_file:

select * 
from sys.fn_get_audit_file('/tmp/*.sqlaudit', default, default)

(I placed my audits in /tmp on this linux image for this demo)

Answer 2

Have you tried to read the audit log with sys.fn_get_audit_file directly rather than using SSMS?

Answer 3

Hi,

i had the same problem with SQL 2022 on Linux (16.0.1000.6, Ubuntu 20.04.5 LTS).

Reading logs with the sys.fn_get_audit_file function works as @Nico Jacobs mentioned. I was able to fix the error itself with an update to CU3. After successful installation and restart of the VM, I could read logs in SSMS.

Just for reference in case someone else stumbles upon it.

Answer 4

Hi @Caio Silva,

Did you try to restart SQL Server service in SQL Server Configuration Manager? If this could not solve this problem, please check the SQL Server error log: The SQL Server error log may provide more details about the source of the error. Review the error log to identify any related errors or warnings that may help troubleshoot the issue.

Best regards,

Seeya