We help IT Professionals succeed at work.

Auditing File Access by users to SQL server.

Panthom
Panthom used Ask the Experts™
on
Hi there

I'm looking for a way to log File Access on our FileServer to an SQL-server, because I don't want to have the information in the eventlog.

Is this as easy as I would have thought it would be, or am I venturing into dangerous territory. I have never logged to an SQL-server, so I don't know what I should look into. If there is a simple program for doing this instead, alternatively to another Database, that's just as fine, All I want is a system that allows me to go in and say, who accessed this file on the fileserver at that day and keep the logging for a long time, 1 year - 5 year... depending on the diskspacerequirement.

I'm not prepared to invest a lot of money in this, but if there is a smart program that can fullfill my requirements easily, that's fne by me too.

Let me know what you think/know already :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I can't understand the question.
1.You mean you want to do audit log on file server or sql server?

2. May I know the reason you don't want to use event log?
Top Expert 2010

Commented:
hi

i havent seen any straight forward tool that monitors file activity and stores it in SQL, but there are few methods

see below

you can use WMI task in SSIS to monitor a file and then store the results in a SQL table, but this means your DTSs package will always be on
http://www.sql-server-performance.com/articles/dba/wni_data_reader_wmi_event_watcher_p1.aspx

or you can enable file auditing in windows and then create a simple application that will read the event viewer and will store the data in sql.
http://asp.dotnetheaven.com/howto/doc/LogInfo.aspx
http://www.thescarms.com/dotnet/EventLog.aspx
http://www.freevbcode.com/ShowCode.asp?ID=5658

or check out these tools

http://www.addictivetips.com/windows-tips/monitor-log-folder-activity-to-perform-actions-on-events/

http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

http://www.softplatz.com/Soft/Security-Privacy/Other/File-Audit-Free-Edition.html

http://www.codeproject.com/KB/files/monitor_all_filesystem.aspx

hope it helps

Author

Commented:
1.) I want to do file access audit logging on files that are stored on our fileserver in a database that is either our SQL-server, or another database that is just setup by a program that makes auditing easier hopefully.

2.)I want to keep the information regarding who accessed what file when, in a separate database.so I don't have to filter away information that is not relevant to the information I want.

Best Regards
Panthom
Mohamed KhairyEnterprise Solutions Architect
Commented:
I think that you have to configur auditing on the mentioned server:

Auditing with Windows Server 2003  is configured in several different ways, all depending upon what needs to be audited, and where that object resides. Generally, the first step is to enable the specific type of auditing through the audit policy, which will usually begin the audit process at that point. Auditing is generally turned on through a security policy, which is another part of Group Policy. These security policies are generally accessed through Administrative Tools.

- Audit Account Logon Events: Tracks user logon and logoff events.
- Audit Account Management: Reports changes to user accounts.
- Audit Directory Service Access: Reports access and changes to the directory service. If the system is a member server or XP system, directory service is NTLM-based, and consists of user accounts and group policies.
- Audit Logon Events: Reports success/failure of any local or remote access-based logon.
- Audit Object Access: Reports file and folder access. Must be implemented here, and then the individual file/folder must be configured for auditing within its properties in order to fully enable this feature.
- Audit Policy Change: Reports changes to group policies.
- Audit Privilege Use: Related to Audit Object Access: reports when permissions are utilized such as read, or full control.
- Audit Process Tracking: Reports process and program failures. Not security related.
- Audit System Events: Reports standard system events. Not security related.

If it becomes necessary to audit file or folder access, the audit policy must be changed, and then the file or folder must be flagged for auditing. From that point, items will appear in the Event Viewer. How the file or folder is accessed is also subject to auditing, and must be decided once auditing of the object is enabled. Every type of permission is available as a type of access, with each type of access capable of being audited if successful or failed.

Here are some helpful links that will guide you:

http://www.experts-exchange.com/OS/Miscellaneous/Q_21003847.html
http://www.windowsecurity.com/articles/Windows-Active-Directory-Auditing.html

Also, you can find and specify the event in the Eventlog and right-click on it and choose"attach a task to this log".  That will spin up a wizard and allow you to specify the action to generate an email upon that event.
So you will have a real time monitoring too through your email address.
Enjoy it.
MKhairy
I find some articles which are quite useful for you to audit file access:

A. Step-By-Step: How to audit file and folder access to improve Server security
http://articles.techrepublic.com.com/5100-10878_11-5034308.html

B. Eventcomb to ease event log management
I assumed that you've turned object auditing for success. This will throw up a hell of a lot of event entries. If you know what files/folders are involved, just turn auditing on for them and turn off all audit for every directory/file your not interested in.

eventcomb is an excellent tool for parsing event logs, you can find it here.
http://www.microsoft.com/downloads/details.aspx?familyid=7af2e69c-91f3-4e63-8629-b999adde0b9e&displaylang=en

It's part of this set of tools, if your not interested in them ignore them. It far more powerful the the standard event search.

C.
Security auditing on Win 2003 Server - Security Auditing
www.chrispeiris.com/articles/Article_2_Security_Auditing_1.doc 

In my personal opinion, you can export your related event logs to files, so that you can stored them for years instead of store in database.

Author

Commented:
Hi there

Apologies for the late return in answering, I have been on vacation.

Thanks for the good insights. I had in fact not setup auditing to begin with, I just have a very restrictive policy on audit's that I need to implement, where every access to any file on our development server is to be logged.
Having had this setup a short while made it obvious that a tool is needed for the parsing of such a detailed log.
Do you know any products that can parse the audits for a single user, so that I could filter for access from 12.00 to 15.00 on a given day, and then see the files that the user had accessed, and also the other way around, saying who has accessed this file since a certain date?

Also, I can see that the need for logging to an SQL-database is not really necessary, as long as the logs are stored, and searchable that is fine.

Best Regards
Panthom
Top Expert 2010
Commented: