Panthom
asked on
Auditing File Access by users to SQL server.
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 :)
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 :)
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
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?