September 3, 2014 at 12:05 pm
I have a client who is running SQL Server 2008 R2 Express Edition w/ Advanced Services; Sp1 with a Microsoft Access front-end.
They are wanting a audit of who is looking at what table. Basically, when user John.Doe opens Form A in the access database, they want to know what tables was looked at. In this scenario, Form A (Information coming from Table A) could have a sub-form Form B (Information coming from Table B). Can I track John?
I can probably capture the user name on the Access side but I'd much rather catch it on the SQL Server side:-D
September 3, 2014 at 12:44 pm
If I recall with Access it utilizes an ODBC connection to the database. As long as the connection is not using a shared login then you can track individually who is access it.
Since you are using SQL Server Express it will be either Profiler or Extended Events. I would only capture object name, login name, and start time. You would want to filter on database, is user process, and possibly object type for tables.
One thing to note is finding out what they want to do with the audit data. There is really no purpose in capturing data if it is not going to be reviewed at some point. I would likely develop scripts that work through importing the data periodically to a audit table for posterity.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 3, 2014 at 1:03 pm
This client has IRS requirements that require them to log everything. Especially anyone who looks at the finance tables. I've implemented triggers on all the tables to audit update, insert, and deletes. I figured this would be the easier of the two. I was wrong. I guess I'm not familiar with SQL Expresses Profiler or Extended Events.
September 3, 2014 at 3:32 pm
jorgans14 (9/3/2014)
This client has IRS requirements that require them to log everything. Especially anyone who looks at the finance tables. I've implemented triggers on all the tables to audit update, insert, and deletes. I figured this would be the easier of the two. I was wrong. I guess I'm not familiar with SQL Expresses Profiler or Extended Events.
Many security standards for IRS, DoD, HIPPA, etc require this type of auditing.
I would also expect some type of reporting is going to be required, since auditors will likely need to review the auditing data the native format is not going to be all that easy for them to review if they are not familiar with SQL Server.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 4, 2014 at 10:02 am
I know Express is limited in what functions it can provide. If I could talk this client into purchasing SQL Standard, would this help me achieve the task I'm working through? Enterprise is out of the question. Cost is way to much for this small company.
The main objective for this project is to record any user that looks at any table. Btw, I really appreciate your responses. Thanks!
September 5, 2014 at 9:03 pm
jorgans14 (9/4/2014)
I know Express is limited in what functions it can provide. If I could talk this client into purchasing SQL Standard, would this help me achieve the task I'm working through? Enterprise is out of the question. Cost is way to much for this small company.The main objective for this project is to record any user that looks at any table. Btw, I really appreciate your responses. Thanks!
Sorry, missed notification you had responded...
SQL Server Audit at the server level is available in Express and Standard. In order to get Database level auditing you would need Enterprise Edition. SQL Audit is basically extended events on the back end so you could manually build out the sessions to capture the required data. I would have them specify what data should be captured to meet their requirements (login name, object name, date/timestamp). I would chose extended events over using Profiler, it is being depreciated at some point.
Now, with that you I would first look at third party products out there specific to IRS requirements. Those products are licensed much cheaper than SQL Server editions are and offer all that is needed in an out-of-the box solution. As well, it is less development time on your part and maintenance. Just my opinion. I know ApexSQL is one vendor that has compliance products for this type of thing but I have never worked with them before. There are other flavors of the same thing just need to find one that matches up to what you want/need.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply