SQL Server Audit files

  • 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 months ago by  mtz676.
    • This topic was modified 2 months ago by  mtz676.
  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • 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

     

  • To clarify a few things on this:

    1. 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?
    2. 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.)

  • 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

     

  • 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.

  • 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.

     

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 9 (of 9 total)

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