I have an audit setup on various versions of SQL Server to dump their log files to a network share.
The server that reads the files (using sys.fn_get_audit_file) to gather the data from them is 2019. We added some new SQL Server 2022 servers and I setup the audit on them. SQL Server 2019 can't read the audit files and produces this error:
Msg 33201, Level 17, State 1, Line 5 An error occurred in reading from the audit file or file-pattern: 'E:\Logdir\auditlogfile.sqlaudit'. The SQL service account may not have Read permission on the files, or the pattern may be returning one or more corrupt files.
I know it's not permissions or corruption because I can read the log file across the network from the file share on the 2019 server using fn_get_audit_file in SSMS from the 2022 SQL Server. Google isn't helping. Did they make some change in 2022 to the sqlaudit files that prevents previous versions of SQL Server from reading them?
2 Answers 2
Did they make some change in 2022 to the sqlaudit files that prevents previous versions of SQL Server from reading them?
XE, which is what Audit uses, is updated but rarely have I ever witnessed any details about it. Normally it comes in the form of this error, 33201. Since this is what you're getting, most likely there is a version bump on either the file format or one of the objects.
In this case, if you want to stick with using SQL Server to read the audit files then you'll need to use SQL Server 2022. You can also use the latest version of the managed .net XE libraries, or SSMS to also read them.
Building off Sean Gallardy's response, this powershell will read the content in all the .sqlaudit files in E:\temp folder into the $data object:
[System.Reflection.Assembly]::LoadFrom('C:\Program Files\Microsoft SQL Server150円\shared\Microsoft.SqlServer.XEvent.Linq.dll')
$data = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData('e:\temp\*')
Make sure to modify the assembly path for your SQL Server version and install location.
Credit: https://www.sqlservercentral.com/articles/load-extended-events-via-powershell-1