-
Title
Change Auditor for SQL Auditing does not work on SQL Server 2017 -
Description
You would like to audit Microsoft SQL Server 2017 using Change Auditor but it does not work even after everything has been properly configured.
So far this issue has been encountered with Microsoft SQL Server 2017 / 2019 only.
When looking at the Change Auditor Agent log on the SQL Server, you notice the following error:
2020-08-21 16:29:23.063 [12916][WARN][NetProLib::EventTracing::SQLTraceSession::Start(222)] Cannot find provider MSSQLSERVERTrace...2020-08-21 16:29:23.079 [12916][WARN][CSQLSink::EventConsumerThread::AddTraceSession(841)] Cannot create trace session for instance MSSQLSERVER
NOTE: The -T1906 flag which is generally required for SQL Auditing has been already set and restarting services does not resolve the issue. -
Cause
Seemingly, the SQL Server installation did not create the WMI object for event tracing or the WMI class has been somehow destroyed.
This issue is not caused by Change Auditor as far as we know. It is related to Microsoft SQL event tracing. In this particular scenario the only difference between working servers and non-working servers was that the non-working servers experienced an in-place upgrade from Microsoft SQL 2016 to Microsoft SQL 2017. Potentially, the WMI class got lost in this process but we cannot tell for sure.
-
Resolution
Verify you are affected by this issue:
1. Run wbemtest as administrator
2. Click the connect button and change the namespace to root/wmi
3. Now that you are connected to WMI, click the query button or enum classes button
4 a. Type the following query if you used the query button: select * from meta_class where __superclass = "EventTrace"
4 b. If you used the enum classes button type EventTrace as the superclass name
5. The second method lists objects alphabetically. You get a bit more dirty list using the query.
Either way, look for an EventTrace object called MSSQLSERVERTrace (name of the instance and Trace is appended).
If you cannot find the MSSQLSERVERTrace object you can follow these steps on a different SQL Server.
In order to populate WMI with the EventTrace object we found that a re-installation of Microsoft SQL Server resolved the issue.