Monitor database every hour to prevent data loss

Question

Here is my requirement, though I'm aware what could possibly be done to fulfill it but still looking out for the best solution to work out with.

Few days ago someone(some hacker or some previous developer) had truncated my important table causing inconvenience to my customers. I need to change my database password, server's password and other application's password in order to avoid such obnoxious actions and prevent my application.

Additionally first I need to know if this kind of activity is being performed with my data before my customers so that I can find out any work around for them. This is what I thought till now-

• I should create a window service which will run in every 1 hour and check in all tables or most importantly in main tables that # of records are as expected or not. If not then an alert will be sent to me, so that I can quickly fix that.
• I should create an SQL job which will perform same steps that I mentioned in first point.

These workarounds seems fine and workable. but I'm really curious to know what else can I do to prevent my data or alert me in case of data lost. I'm sure there are other efficient and more feasible way to achieve what I want. It would be great if anyone can help me around.

This is a web application and only authenticated users can login. My SQL Server edition is Enterprise.

Answer 1

First and foremost this is an issue of access control. Eliminate all redundant logins, and reduce permissions on the required logins to the strictly necessary minimum. Do not use 'well known' logins shared by every person with DB access, make sure every user uses its own login (eg. use Integrated Auth).

It is not clear from your post whether this server is accessed directly from the internet or not. Ideally you should set up a VPN between your app(s) and the server and avoid exposing the server to the internet. If not possible, there are some defenses you need to set up. The default SQL Server ports are subject to constant scans and brute force attacks. You should use a non-default port to, at least, avoid the overhead of rejecting these constant brute force scan attacks. Next, you should restrict the internet client addresses that can connect. This can be done, ideally, at IP level using Firewall rules. SQL Server also can enforce this, using logon triggers, as Alex suggested. If possible, deploy both firewall rules and a logon trigger.

Enable SQL Server logon audit. Advertise it that is enabled, so that employees are deterred from trying to hack into the system.

Establish backup policies, with frequent log backups, and keep backups off-site. Have restore plans, and do restore drills to validate the backups and the restore procedures. If someone penetrates your defenses and manages to erase data, you should be able to quickly restore it.

And lets not forget the app itself. You may well be barking up the wrong tree and the erase may had been done via the app itself, via the usual SQL injection exploits. You must audit the app and ensure this is not the case, fix any issue you find.

Right now, you are in an impossible situation. You have lost data, and don't even know how. Contact professional help to help you find how the data was erased. If you don't know where the ship is leaking, is very hard to patch the hole...

My 2c on the idea of 'monitor the database see when someone deleted my data' is ... a waste of time right now. W/o understanding how the data was erased, it will just turn into an amusing game for somebody that will periodically erase your data, just to see how fast you are reacting today... Really, focus on mitigation and deterrence. Detection is nice to have, but it cannot be your first line of defense.

For structural database changes (eg. modify a table, alter a procedure etc) my company DBHistory.com offers a service that audits all server changes and keeps an off-site record. Unfortunately, at this moment I cannot offer data changes audit.

Answer 2

You need to investigate "Logon Triggers" (from memory they are available from SQL 2008 upwards). In the trigger you should be able to restrict logon attempts to specific hosts/applications only.
Note: this is not a bullet proof solution but it will make it a little bit harder for your hacker.

CREATE TRIGGER [tr_logonLogger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    DECLARE @lastLogonTime  DATETIME;
    DECLARE @LoginName      VARCHAR(30);
    DECLARE @clientName     VARCHAR(128);
    DECLARE @clientIP       varchar(48);
    DECLARE @AppName        VARCHAR(128);

    set @lastLogonTime  = getdate();
    set @LoginName      = ORIGINAL_LOGIN();
    set @clientName     = HOST_NAME();
    set @clientIP       = cast( CONNECTIONPROPERTY('client_net_address') as varchar(48));
    set @AppName        = APP_NAME();

On top of what you have specified in your question, regarding a job regularly checking data integrity, I would recommend very frequent backups, so that if data is deleted it can be restored with minimal loss.

Answer 3

The main issue here for me is how a web user was able to truncate the table. I would have a serious look at the permissions assigned to the tables and limit what can be done. If users only need to be able to read data then only allow them select access, if they need edit, give them select, insert and update etc... Work on giving them the least amount of privilages possible. This should at least start limiting what damage can be done.

Not knowing how this occoured, another thing to look at is making sure any page, input field where a user can enter in data that is saved back to the database has code to make sure what is being saved is actual legit data and not someone trying to do some SQL Injection.

The best course of action is obviously stopping it happen as once you are notified by any job logic its already too late. In saying that there are a few temporary workarounds you can do to make your life easier when it comes to restoring things back to normal until you have found out what the cause was and put a stop to it.

Alex has already touched on backups, if you find that there is a key table that is constantly getting hit I would as an easy rollback, create copy of the table (if its not too large of course), something like select * into TableName_backup From TableName. It will be a lot faster to recover the table.

In terms of monitoring the table counts. Just create an agent job that does a count on the table, if its below a number you are expecting send an email off. Id set this to run more frequently than every hour for now as you would want to pick up on this faster than your customers. You could even get clever and if it detects zero rows use the temporary table you created and repopulate it - then email yourself to let you know what happened.

Hope something in here is of help to you.