Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Malicious files on the sql server found Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 11:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 381, Visits: 1,855
Hi All,

I need some help on how can we get the details and confirm that if some one view or modified the data in the sql server databases.

Issue:
Found a sql agent job running as "sa" and failed in the middle. SQL job has a step to execute an operating system file (xxxxxxx.exe) on root c:\ directory.
So the job failed stating as the file does not exist on the directory. that exe file is a trojan file.

So i found the details in the SQL default profiler trace as there was a successfull login attempt made to connect to the sql server using "sa" and ran a bunch of dbcc commands and finally stoped the trace as "sa".

So now we are stuck here to find actually "is any data is viewed or transferred or injected". As we are unable to view any more information in trace file and windows logs, i am looking for help as
1. Is a user login as "sa" can create a .exe file on the server ?
2. What is the way or any kind of analysis can we make and find out what actullay has been ran on the db server?


Post #1557614
Posted Wednesday, April 2, 2014 12:28 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 786, Visits: 691
Yes, sa could create an executable file on the server. For instance by first inserting the binary string into a table, and the exporting it to file with BCP through xp_cmdshell.

Finding out exactly what happened on that server before and after the trace stopped will be difficult. But it seems that you at least have the point it time it happened through the trace. I would advice that you restore the databasees to a point in time before the intrusion on a different server or similar, and use a tool like Red Gate's SQL Data Compare to see if you can find traces of manipulated data. If you have sensitive data on the server like passwords, credit card numbers etc you have all reason to be worried.

And most of all, you need to figure out how the intruder came in. Are their applications logging into sa? Are there web applications which have holes for SQL injection?


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1557650
Posted Wednesday, April 2, 2014 12:34 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 5,974, Visits: 12,875
I am sure you have already thought of it, but change the sa password or disable sa, turn off xp_cmdshell if you don't need it. By having sysadmin rights they would have broken out of SQL into the OS with the rights of the SQL service account, so make sure that has only the rights it needs (i.e. is not in local admins).

---------------------------------------------------------------------

Post #1557654
Posted Wednesday, April 2, 2014 1:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 381, Visits: 1,855
Yes. we know the root cause of how they got into the server- our networking engineer made some network port changes in ipconfig table so that the sql server is open to all inbound and outbound via NAT server on port 1433.

We already restored the databases before the point of intrusion.

I have disabled the sa account for now.
I would like to know the best practices to implement in sqlserver to avoid these kind of situations and getting alerting ahead of time. Please list some suggestions.
Post #1557671
Posted Wednesday, April 2, 2014 1:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 381, Visits: 1,855
george sibbald (4/2/2014)
I am sure you have already thought of it, but change the sa password or disable sa, turn off xp_cmdshell if you don't need it. By having sysadmin rights they would have broken out of SQL into the OS with the rights of the SQL service account, so make sure that has only the rights it needs (i.e. is not in local admins).


Hi George,

My security team is questioning why we need to enable "sa". i need a supportive answer to explain them that we can enable "sa" but with strong password. correct me if i am wrong.
Also our environment is in cloud and we do not have AD domain controller.
Post #1557673
Posted Wednesday, April 2, 2014 1:40 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 786, Visits: 691
Keeping the network engineer in a leash sounds like a good start. :--)

Disabling sa is also a good start. Disabling SQL authentication is even better for accidents like this, but if it is not possible, make sure that SQL logins don't have extra permissions.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1557675
Posted Wednesday, April 2, 2014 1:41 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Since you don't know what all was done, consider this server compromised (permanently). That means the OS and means the attacker could potentially come back in. Therefore, if possible, build a new server, transfer the DBs to it, take this server off-line and re-point everything to the new server. If the name is important, then when you're ready to make the change, take the old server off-line, drop the new SQL Server into a workgroup, rename it, and then re-add it to the domain.

As far as best practices are concerned, If the IPtables were changed and that led to the compromise, there's not a ton you can do. That's a firewall change outside of SQL Server. One thing you can do is modify the IPSEC policy (Windows Server 2003) or the Windows Firewall (Windows Server 2008+) and add a policy to block all IPs except from your internal network. This assumes that only systems on your internal network would be making the connection.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1557676
Posted Wednesday, April 2, 2014 2:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 381, Visits: 1,855
K. Brian Kelley (4/2/2014)
Since you don't know what all was done, consider this server compromised (permanently). That means the OS and means the attacker could potentially come back in. Therefore, if possible, build a new server, transfer the DBs to it, take this server off-line and re-point everything to the new server. If the name is important, then when you're ready to make the change, take the old server off-line, drop the new SQL Server into a workgroup, rename it, and then re-add it to the domain.

As far as best practices are concerned, If the IPtables were changed and that led to the compromise, there's not a ton you can do. That's a firewall change outside of SQL Server. One thing you can do is modify the IPSEC policy (Windows Server 2003) or the Windows Firewall (Windows Server 2008+) and add a policy to block all IPs except from your internal network. This assumes that only systems on your internal network would be making the connection.


Hi Brain,

I have only enabled "to log failed login attempts" to sql server and my security team is pointing out that why both login and successfull attempts is not enabled on the sql server. As till now they were not able to find the IP of the intruder they are raising this point.
In profiler trace file - i found the application name as some spoof name like MYSERVER and it connected to SQL server using sa and ran some DBCC commands.
So till now my security team were not able to find the actual Intruder IP address and they are raising a point why both successful and failed logins log is not enabled. So without that - network team cannot resolve the IP address of intruder? I need some suggestions and inputs here. Thanks.

Post #1557686
Posted Wednesday, April 2, 2014 2:07 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 786, Visits: 691
If there is no record of the successful login from the intruder, it is difficult to track.

But unless sa had a blank password or something else guess at first attempt, I would expect there to be tons of failed login attempts, if it is was a brute-force attack.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1557687
Posted Wednesday, April 2, 2014 2:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,595, Visits: 4,585
First, don't reboot the server or restart the SQL Server service, because you can loose valuable audit information doing that.

You can query sys.objects and order by modify_date desc, create_date desc for a quick idea of what objects had DDL modification recently.

Also query default trace for login, ddl, and dbcc events.
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

You probably want to call in a consultant who specializes in performing intrusion assessments for Windows Server and SQL Server.
Post #1557690
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse