locked
SQL Server Audit FILEPATH On Network Device RRS feed

  • Question

  • Hello,

    As we know we can backup databases on network device. Is their any way we can write SQL Server Audit Files to a network device?

    Regards,

    Taoqir

    Saturday, December 17, 2016 11:16 AM

All replies

  • Saturday, December 17, 2016 12:06 PM
  • Be careful with this. If the network link goes down, auditing goes down and takes your SQL Server with it. Note the feature to shut down SQL Server on audit log failure. This is disabled by default.

    Also with SQL 2016 there is an auditing resilience feature which means the should your link go down you auditing will be buffered until it goes up again.

    Here is how to create your audit to a unc

    USE [master]
    GO
    CREATE SERVER AUDIT [Audit-20161217-081205]
    TO FILE
    ( FILEPATH = N'\\publisher\temp'
     ,MAXSIZE = 0 MB
     ,MAX_ROLLOVER_FILES = 2147483647
     ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
     ,ON_FAILURE = CONTINUE
    )
    GO


    Saturday, December 17, 2016 1:13 PM
  • Hello Hilary,

    Right now we are testing on our UAT system and getting below error.  

    USE [master]
    GO
    CREATE SERVER AUDIT [Audit-20161217-081205]
    TO FILE 
    ( FILEPATH = N'\\192.168.15.30\Audit'
     ,MAXSIZE = 0 MB
     ,MAX_ROLLOVER_FILES = 2147483647
     ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
     ,ON_FAILURE = CONTINUE
    )
    GO



    Msg 33072, Level 16, State 1, Line 1
    The audit log file path is invalid.

    Sunday, December 18, 2016 1:26 PM
  • Logon on to the SQL server using the account that SQL Server runs under. If you are uncertain about this do this, open up the services applet, drill down on SQL Server (for me it is SQL Server (MSSQLServer), and click on the log on  tab.

    At a command prompt type net view \\192.168.15.30

    Does audit show up?

    If it does do this

    dir *.* > \\192.168.15.30\Audit\out.txt

    Did that work? If so you have all the permissions you need.

    If not, report  back with any error message you may get.


    Sunday, December 18, 2016 1:36 PM
  • dir *.* > \\192.168.15.30\Audit\out.txt works fine and its created a file named out.txt on remote side.

    Regards,

    Taoqir

    Sunday, December 18, 2016 2:12 PM
  • The message is generated by one of the following 2 conditions.

    1) incorrect path

    2) incorrect permissions.

    We have validated both.

    Can you do this now?

    xp_cmdshell 'echo %username% >\\192.168.15.30\Audit\out.txt'

    Then open up your out.txt file and see what account is in use.

    You may need to enable command shell to get this to work.

    Also what version and edition of SQL are you using.

    Sunday, December 18, 2016 2:22 PM
  • when I run given command 

    Access is denied. 

    I am using Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64).

    Regards,

    Taoqir

    Sunday, December 18, 2016 2:34 PM
  • Do this then

    xp_cmdshell 'echo %username%'

    Report back here with the account name. This account should be the account that the SQL Server service account runs under.

    You need to ensure that this account has write permission on the share and change permission on the files nad folders which underly the share.

    Somehow - even though you logged on with that account via rdp and were able to access the share it does not seem to work.

    Sunday, December 18, 2016 2:44 PM
  • xp_cmdshell 'echo %username%'

    "SYSTEM"

    On shared folder read,writes permissions are for every one.

    Regards,

    Taoqir 

    Sunday, December 18, 2016 3:16 PM
  • Where external access to resources is required, you really should be running your SQL Server service as a Domain Account.

    Running as a local SYSTEM account is not recommended as any attack via your SQL Server immediately gets full and unrestricted access to your underlying machine as well - not a good idea.

    See here for further guidance: https://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx

    If you change to a domain account, then you can easily grant permissions to the network share to this account and PLEASE, PLEASE REMOVE EVERYONE as the permissions on the share .... what is the point of auditing if anyone can simply open the audit files and remove the entries detailing the mischief they were up to.


    Martin Cairney SQL Server MVP

    Sunday, December 18, 2016 10:56 PM
  • Yes, the SQL Server service account needs to be a user or domain account. Permissions on the share and underlying files and folders should be not be granted to the everyone group.
    Monday, December 19, 2016 2:25 AM