The SQL Server Auditing stopped after the restart of the SQL instances. Netwrix error is “Tracing was disabled on the servername\instancename server. As a result, SQL Server logon activity data may be lost, SQL Server change reports may show incorrect data in the ‘Who’ and ‘When’ fields”
Is Netwrix not supposed to restart automatically the tracing? It must restart it because Microsoft documentation explains that trace file does not restart automatically except for the default trace file. Only the newer Extended Events can be configured to start at the SQL Server instance startup.
When a SQL Server restarts, the tracing will be disabled by default. Netwrix Auditor will re-enable tracing upon it’s first collection and you should see a message that states
“Tracing is required for successful change and logon activity auditing, and it has been automatically enabled.”
If you do not see this message or if you see the tracing error after every collection, then here are two articles that can assist.
Thank you Michael for your message! It is very helpful.
before trying to fix manually on all the SQL instances, I am trying to understand why Netwrix is not able to automatically restart it as it should.
in the Netwrix health log, before the error I posted earlier, I saw the following error message : “The trace ‘netwrix sql cr readaccess trace’ on the server ‘XXXX’ was not found, some data may be lost.”
It looks like that the Netwrix server is not able to read the trace files anymore. I am wondering whether Netwrix is trying to read the trace files with the SP too quickly … a SQL instance needs time to restart and time to let the SP read the trace file (it is usually pretty slow reading a file from SQL Server)…
would that make sense?
how many times Netwrix will try to activate or find the trace files?
When a SQL instance restarts, SQL will disable the tracing by default so it’s expected to see the error after a reboot. Netwrix Auditor checks for the tracing upon every collection. So after a restart, we will find that the tracing is disabled and we should be enabling it. You will though see the error that tracing is not enabled or found. But it should be followed with it enabling the tracing. You shouldn’t see the error for longer than 10-15 minutes after the SQL instance is back up.
Are you seeing these errors during each collection or are these errors just for a period of time after a restart?
You are right. I went through the errors in order in the Health Log and I could see that after the error, there is another error message to say it is enabling the tracing:
“Tracing is required for successful change and logon activity auditing, and it has been automatically enabled.”
But these errors don’t happen after the SQL instance reboot but during the scheduled report sent by email. At 3AM, the report for the PREPROD sql instance, at 5AM, the report for the PROD sql instance…
To summarize, after the SQL instance reboots, we get the errors about the missing netwrix trace files then later when the reports are triggered, the tracing is re-enabled.
Thanks for the update. If the instance is restarting, then yes it’s expected. I should have expanded on that when I first mentioned it but it doesn’t matter if the server itself is rebooted, it’s simply the instance restarting that causes the tracing to go back to the default of being disabled.
Are you restarting the instance on a daily basis due to a technical issue or is that just a best practice that you have? I don’t see any issues with it but if you are doing it for a problem with Auditor or Reporting Services, we might be able to look at that to prevent you from having to restart the instance.
Every month Microsoft releases updates/patches that restart all the Windows servers hosting SQL instances. So we are going to have auditing/tracing errors in Netwrix health log every month.
If it is an expected behavior for you, does it mean we should schedule a Netwrix report right after the reboot to be sure not to loose any audit collection (as the tracing only works after triggering a report)?
Sorry for the confusion, I thought you were seeing this daily. The tracing will re-apply upon the next collection after the server it back up. So you don’t have to force a report. It will just apply it within 15 minutes of the SQL server coming up.
The SQL plan is running quite often so it may just be a timing issue where it looks like it’s the reports being triggered that is turning it back on but it’s actually just the regular collection.
You can do a test and restart the SQL instance during a slow period and then just wait in Auditor. Within 15 minutes, you should see those errors come and go and they should stop, without the need to run a report.
If that is not the case, then we will probably need to look at some logs to see why it’s not happening. Let me know and if necessary, we will open a support ticket and work with you on it.