March 21, 2017 at 5:59 am
The data is deleted from database but i am not able to identify the process or application which is doing this and its frequency is very much random. In order to identify the source of this deletion i have created delete triggers. That helps me to capture the time of deletion , the record which is deleted , the query which was used to delete the records and the source of the query.
Following are the findings till now
The query which was fired to delete the records is "delete top (10) from dbo.[tablename]"
In the source column i can see the value as ".net sql client" . I have checked the code of all the .net applications which are pointing to the database but i have not found any such query.
Now is there any way by which i can get the application name which has run this query ?
Thanks in advance.
Vipin
March 21, 2017 at 6:28 am
I'd suggest setting up trace events to capture remote procedure calls and sql batch. You should be able to identify the login from there. The application name might not be available since it's just a connection setting. If the app doesn't set it, you won't get it. You can also try capturing security events. Not sure if there's IP address in there or not, but it can't hurt to explore that space.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2017 at 6:28 am
Unfortunately all the applications are using the same user in windows authentication mode.
March 21, 2017 at 6:30 am
Thom's suggestion makes sense to me too, you could also log the pc being usedSELECT ... SUSER_NAME(), HOST_NAME()
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 21, 2017 at 6:42 am
vipinmittalmca - Tuesday, March 21, 2017 6:28 AMUnfortunately all the applications are using the same user in windows authentication mode.
Ouch... Perhaps something for the ideas board in the future, as this suggests that an application could make changes to the data on any of your other applications. Also, one breach, and someone has access to all your data, not just one application. If they all have different logins, and permissions set up correctly, then at least your breach will be minimal.
Andrew raises a good point of providing the host name, if it''s always coming from one host, that narrows it down, but it's not going to tell you what application it is, especially if they all share the same credentials.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 21, 2017 at 6:48 am
Your only hope is that the application name is supplied with the connection. It's optional, so you may not see it. I'd strongly suggest, as has everyone else, cleaning up the security.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2017 at 6:54 am
Thom and Andrew , yes i am also capturing the host_name as well, sorry i forgot to mention it in my question initially. I know the machine from the host name but again i have not found any application where we have written any such query.
Just to share with you guys:
That delete query has been executed in loop(7 times) for different tables.
As the program name (or source of query) is populating as ".net sql client data provider" so first thing i want to confirm from you guys is that there is some .net application which is doing this right, to make sure i am looking in the right direction?
March 21, 2017 at 7:37 am
vipinmittalmca - Tuesday, March 21, 2017 6:54 AMThom and Andrew , yes i am also capturing the host_name as well, sorry i forgot to mention it in my question initially. I know the machine from the host name but again i have not found any application where we have written any such query.Just to share with you guys:
That delete query has been executed in loop(7 times) for different tables.As the program name (or source of query) is populating as ".net sql client data provider" so first thing i want to confirm from you guys is that there is some .net application which is doing this right, to make sure i am looking in the right direction?
Maybe....maybe not. If this is an internal application it is very likely that is the case. But the application name can just as easily be spoofed because it is just a string literal in the connection string. This is sounding like potential sql injection as you have no code that does this and delete top 10 is a bit bizarre for most applications. I have a feeling you due for a long, hard look at security from both the database and application side of things.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2017 at 8:04 am
Sean Lange - Tuesday, March 21, 2017 7:37 AMvipinmittalmca - Tuesday, March 21, 2017 6:54 AMThom and Andrew , yes i am also capturing the host_name as well, sorry i forgot to mention it in my question initially. I know the machine from the host name but again i have not found any application where we have written any such query.Just to share with you guys:
That delete query has been executed in loop(7 times) for different tables.As the program name (or source of query) is populating as ".net sql client data provider" so first thing i want to confirm from you guys is that there is some .net application which is doing this right, to make sure i am looking in the right direction?
Maybe....maybe not. If this is an internal application it is very likely that is the case. But the application name can just as easily be spoofed because it is just a string literal in the connection string. This is sounding like potential sql injection as you have no code that does this and delete top 10 is a bit bizarre for most applications. I have a feeling you due for a long, hard look at security from both the database and application side of things.
Once I saw the delete, my mind went directly to SQL Injection. You might have a trickster using the system.
If the host name is always the same, block the access to the host and report the person.
March 21, 2017 at 8:46 am
Luis Cazares - Tuesday, March 21, 2017 8:04 AMSean Lange - Tuesday, March 21, 2017 7:37 AMvipinmittalmca - Tuesday, March 21, 2017 6:54 AMThom and Andrew , yes i am also capturing the host_name as well, sorry i forgot to mention it in my question initially. I know the machine from the host name but again i have not found any application where we have written any such query.Just to share with you guys:
That delete query has been executed in loop(7 times) for different tables.As the program name (or source of query) is populating as ".net sql client data provider" so first thing i want to confirm from you guys is that there is some .net application which is doing this right, to make sure i am looking in the right direction?
Maybe....maybe not. If this is an internal application it is very likely that is the case. But the application name can just as easily be spoofed because it is just a string literal in the connection string. This is sounding like potential sql injection as you have no code that does this and delete top 10 is a bit bizarre for most applications. I have a feeling you due for a long, hard look at security from both the database and application side of things.
Once I saw the delete, my mind went directly to SQL Injection. You might have a trickster using the system.
If the host name is always the same, block the access to the host and report the person.
This is where I was going as I read through the thread.
March 21, 2017 at 8:55 am
Very much thinking along the same lines.
If the SQL is simpl;y just deleting the top 10 (with no WHERE or ORDER BY clauses), then it's not even guaranteeing what it deletes, it purely random.
Like the others, I would suggest firstly blocking access from that host to your SQL Server, then keep an eye to ensure that it doesn't start happening elsewhere. After that, begin separating the credentials for each of your applications. That is not going to be a quick or easy task if you have a lot of applications. Personally I'd start with one of the smaller ones, which only needs very limited access to your data. Make sure you get that one right.Then probably go to another small one.
Once you're happy that the new credentials aren't breaking things, I'd start on the high risk applications instead. Those that will need access to a lot more data, and need higher permissions. if you have several applications with very similar, or almost identical, need at a permission level, consider making some roles. Then you can create their logins and assign a role that you've already done a lot of the work load for.
While doing all this, continue to keep an eye on if the deletes start back up again. If they've moved host, check to see if a particular user has begun using this other host machine. You should then report your concerns to the powers that be immediate, outlining that you have concerns that the user is doing malicious actions. With any luck, you may have already ring fenced the application they were accessing through.
Once everything is done, remove the block on the old host. If it didn't ever re-emerge then keep a very close eye on the deletes being put through. If you've been especially strict, the logins may only have access to execute SP's, thus the deletes would fail, so some login on permission errors might be beneficial. Keep an eye on what login is completing the delete, or receiving the permission errors, and hopefully you'll eventually end up with your answer (although, by that point, you might have stopped it, it is important to still find out the proximate cause for all that work you've had to do).
This isn't something I've ever had to put into practice post incident, so someone who has had that "delight" will likely have better expertise and guidance on best practice.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply