SQL Server Audit files

Question

Post reply

SQL Server Audit files

mtz676

SSCrazy Eights

Points: 9214
More actions
June 14, 2022 at 5:32 am

#4049110
I have audit files from diffferent servers under the same folder on ServerA.
\\ServerA\aaa\ServerA.audit and
\\ServerA\aaa\ServerB.audit and
\\ServerA\aaa\ServerC.audit
The SQL service account on ServerA has permissions to this folder \\ServerA\aaa\.
.When I run this ...
```
SELECT 
server_instance_name as ServerName,database_name as DatabaseName,dateadd(hour,-5,event_time) as EventTime,
server_principal_name as ServerPrincipalName

--,object_name
--,class_type
FROM sys.fn_get_audit_file('\\ServerA\aaa\*.sqlaudit', DEFAULT, DEFAULT)
```
it is throwing the following error.
Msg 33224, Level 16, State 4, Line 2
The specified pattern did not return any files or does not represent a valid file share. Verify the pattern parameter and rerun the command.
Please advise.
Thanks
- This topic was modified 2 years, 5 months ago by mtz676.
- This topic was modified 2 years, 5 months ago by mtz676.
June 14, 2022 at 7:09 am

This was removed by the editor as SPAM
June 14, 2022 at 7:11 am

This was removed by the editor as SPAM
June 20, 2022 at 6:54 am

This was removed by the editor as SPAM
December 5, 2022 at 8:00 am

This was removed by the editor as SPAM
January 6, 2023 at 9:29 pm

This was removed by the editor as SPAM
April 24, 2023 at 5:13 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3666 More actions · Answer 1

Have you set up the aaa directory as a share?

That path "\\ServerA\aaa\" presumably refers to a specific drive on the server.

I have an audit that I reference using a mounted drive letter. If I use a UNC path I have to use the admin share of the drive, because the specific folder has not been set up as a share

jasona.work SSC Guru Points: 50117 More actions · Answer 2

To clarify a few things on this:

The SQL instances, do they write the audit files directly to the locations you indicated, or is there a process that copies / moves the files later to those locations?
Outside of SQL and presuming the \aaa\ is a shared folder, can *YOU* access the share by browsing to it through Windows Explorer?

Presuming that the answer to #1 is SQL writes directly to that location and it is a network share, I'd bet the answer to #2 is that you can not access the share.

You could also work with the server admin of ServerA (presuming it's not you,) to check the Windows Security Event Log for any failed attempts to access that location (easiest way to do this, run your query, noting the time you ran it, then check the event log on ServerA around that time.)

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 3

1.SQL audit files are directly writen to the shared folder from across all servers to ServerA's shared folder.

2.Yes I can access the shared folder on ServerA from ServerB or ServerC using \\ServerA\aaa\ this shared folder to which the audit files are being written

jasona.work SSC Guru Points: 50117 More actions · Answer 4

So that rules out the easy stuff.

If you explicitly put the name of one of your audit files in, rather than the wild card *.sqlaudit, does it work? If not, I'd still lean towards there being a permission problem somewhere, maybe the SQL Server service account not having read permissions on the share or file system.

Ed B Hall of Fame Points: 3666 More actions · Answer 5

It may be the permissions of the SQL Server Service account.

I just created a share and granted just myself permission to access it and I confirmed that I could in windows explorer. When I tried to query the audit I got the same error about the pattern not representing a valid share. I then added the service account to the share and I was able to query the audit as I could before I tried using the new share.