I need simple but reliable mechanism to ensure that table was not tampered with in the SQL Server 2008. The assumption is that hacker can access and control only one of the servers (application or database) but cannot access both. Any links or suggestions will be greatly appreciated.
Clarification. "Tampering" here means ability to update/delete row after it was inserted into the table. Table to be protected is a sort of business transaction log.
Put a DDL trigger on the database to monitor any changes to the database.
Your time would be better spent ensuring that a hacker can't get in at all, because as soon as you start making pessimistic assumptions like above, then you can be sure that a hacker Will get into both servers.
You could create an audit system via triggers that makes log entires anytime someone does an insert/update/delete to a table/tables on your DB Server, but this doesn't stop a hacker who's gained SA access from just nuking your audit tables or master DB.
Concentrate on Security, putting good code in place to prevent SQL Injection Attacks, Making sure you use SQL Login Accounts in your application with only the permissions they need, ensuring your database is behind a DMZ and not publically accessible, proper firewall and port security on the outside to ensure that only your Web/Public facing service ports are open.
If this is for a production environment and is beyond your expertise, there are security companies who will analyse you applications for any vulneribilities as well
What about access logs? You can probably see if your user changed something in the database or web application user did this. Then again, how do you differentiate between normal and abnormal activities from your web application user?
Making sure you server is properly locked down is obviously the first thing you should do.
A possible solution to catch mistaken edits is that you could compute a checksum of some sort on all the fields in a row, and store that in the row as a long, unintelligible string; If a stored procedure did the insert/update commands, it could re-compute that checksum at each insert/update. If someone directly edited the data, i.e. thru Access or Management studio and directly edited fields, the checksum would be off and on the next access you could detect that and take action.
Of course if someone has access to the SP, then a motivated hacker will just duplicate the logic (or else just use the SP), but it would likely deter and catch sloppy direct edit mistakes made by people who are not malicious, but are accidentally editing data they shouldn't.
If your only purpose is to be able to validate the contents of your business transaction log table, you could store a hash value (MD5 or SHA) in, say, an XML file on the application server. When the application adds a transaction to the database, add a hash value to the XML file. You could then audit the data in the table by ensuring that...
If you really want to go over the top, I'd suggest the following:
First, if you use stored procs and nodynamic sql, you can set permissions at the proc level instead of the table level. This protects you from ever having a hacker do anything except what the application will allow. No one except the dbas should have any acces to tables in production.
Next, set up audit tables to record all changes to the data and when they happend and who made them. This can be used to easily get the data back if someone makes unauthorized changes.
Third, make sure you have a good backup plan running and backup your transactions logs every 15 minutes. Make sure your dbas have practice restoring from backup.
Fourth in sql server 2008 you can have ddl triggers which will tell you who changed the structure of a table not the data.