Error when viewing audit files for Azure SQL Managed Instance in SSMS.

Issue:

When user view the audit log file from the log file viewer in SSMS he gets an error “Item has already been added. Key in dictionary: 'MNDO’ Key being added: 'MNDO'“.

Investigation:

After investigating this issue with SQL server profiler we found that this option is executing the following query:

select distinct action_id, name from sys.dm_audit_actions where action_in_log = 1

and by running this query in SSMS query editor you will find the duplicate value for ‘MNDO’ key

As SSMS is using the unique value action_id instead of the combination of action_id+name when creating the internal dictionary, So SSMS reporting the error message as duplicated key in the dictionary because using only the action_id.

Mitigation and workaround:

This issue was identified as a code defect and a fix will be rolled out in 2023.

And as a workaround to overcome this issue you can use sys.fn_get_audit_file to query the audit file or to use Extended events.

For more information please refer to those documents sys.fn_get_audit_file (Transact-SQL), Extended Events overview

Continue reading...