ODBC event collection fails with message "Unable to execute statement: Statement: exec nic_aud_swap_trace" in RSA Security Analytics
RSA Product Set: Security Analytics. RSA NetWitness Logs and Network RSA Product/Service Type: Log Collector, ODBC Log Collection Platform: CentOS 6,7 Platform (Other): Microsoft Windows, Microsoft SQL Server
ODBC Log Collection to a Microsoft SQL Database fails due to a trace file becoming corrupted. The Trace File directory on the SQL Server will continue filling with logs until this situation is resolved.
In the /var/log/messages file on the Log Collector, messages similar to the following will be seen.
un 5 07:59:45 REMOTELOGCOL nw: [OdbcCollection] [failure] [mssql.MSSQL] [processing] [MSSQL] [processing] An error occurred collecting ODBC events using query tag MSSQL. Error: Unable to execute statement: Statement: "exec nic_aud_swap_trace 30, 'c:\MyTraceFiles\', 1, 'WHERE StartTime > 2015-06-04 17:04:42.110'"; Reason: state: S1000; error-code: 140071768425015; description: [RSA][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]File 'c:\MyTraceFiles\-4.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
The important error to observe is below.
Error: Unable to execute statement: Statement: "exec nic_aud_swap_trace 30, 'c:\MyTraceFiles\',
In this example, c:\MyTraceFiles is the directory where the Tracefiles are being stored on the MS SQL Server.
A trace file has become corrupted which prevents the Log Collector from collecting further files. Ensure that the trace file directory is excluded from any software that may also lock files in this directory. Examples of such software include anti-virus or third-party backup software.
To resolve the issue:
Obtain access to the Microsoft SQL Server with Microsoft SQL Server Management Studio
Navigate to the Master Database
Run the db.nic_aud_init_trace stored procedure. To preserve data, make sure that a NULL value is parsed. Otherwise, trace files will be deleted and not processed.