April 2, 2014 at 11:10 am
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?
April 2, 2014 at 12:28 pm
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?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 2, 2014 at 12:34 pm
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).
---------------------------------------------------------------------
April 2, 2014 at 1:32 pm
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.
April 2, 2014 at 1:38 pm
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.
April 2, 2014 at 1:40 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 2, 2014 at 1:41 pm
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
@kbriankelley
April 2, 2014 at 2:04 pm
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.
April 2, 2014 at 2:07 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 2, 2014 at 2:14 pm
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2014 at 2:28 pm
muthyala_51 (4/2/2014)
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.
You don't necessarily need the 'SA' account, but if you must leave it enables, then of course use a strong password. However, a trojan with local admin privillages may be able to login to SQL Server as sysadmin even without using 'SA' account.
https://www.netspi.com/blog/entryid/133/sql-server-local-authorization-bypass
So you really need to lockdown and minimize what accounts can potentially gain access to SQL Server in the event the Windows server is compromised. I wrote a script a few years back you can use to list what domain, mssql, and local system accounts can login as sysadmin, either explicitly or because they inherit from domain group.
http://www.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2014 at 2:43 pm
Erland Sommarskog (4/2/2014)
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.
+1.
It is very unusual to log both successful and failed logins as it floods the errorlog. Was it a requirement of this system beforehand? If so surprised its hosted in the cloud and there is no domain controller. The security team are being a bit wise after the event there. If no failed logins perhaps intrusion was from someone who knew the password, or was it an obvious one (don't tell us value!)
The real issue is they got through your firewall and broke your sa password, not so much you had sa. But thats not an argument you can win, because you have been breached. Don't just disable sa, make sure its password is changed as well to something VERY secure. You don't need sa and should never log on with it, only real problem you could get is if maintenance plan were created using sa, they would stop working and you would have to change their owner. Its a fall back if you lose your access but there is the DAC. Someone has to have sysadmin.
If the security team have these rules they should have been checking they were enforced by running audits and their own intrusion tests, they are not looking to good here either I'm afraid.
---------------------------------------------------------------------
April 2, 2014 at 4:14 pm
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).
Turning xp_CmdShell off does nothing but provide a false sense of security. Hacker software is designed to turn it back on an anyone/thing that gets in as SA will have no problems whatsoever doing that.
What needs to happen immediately is to...
1. Find some professional SQL Server Security specials and get their butts on-site NOW! While you're waiting, do the following at the very minimum RIGHT NOW!!!
2. Change all user passwords for all users having "SA" privs to much stronger and policy driven passwords. That includes all SQL Server logins, as well. The second largest source of attacks is made available by less than adequate passwords.
3. Review all application logins. If ANY of them have "SA" privs, you need to reduce those to a max of Read/Write/Exec immediately with future plans to reduce even that. The largest source of attacks is still SQL Injection.
4. Immediately limit what the SQL Server and SQL Agent services have access to so that if someone continues to bust in, they won't have so much access to the domain through xp_CmdShell, PoSH, or any of a dozen other attack vectors.
5. Make sure that NO individuals with less than "SA" privs have the ability to execute xp_CmdShell directly. That includes applications, as well. Of course, no application should have "SA" or even "DBO" privs. Only trusted DBAs should have "SA" privs, PERIOD!
6. Tell all the people that told you that you were too stingy and uncooperative because you tried to lock down SQL Server before but they wouldn't let you, to bite you.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2014 at 5:22 pm
Dealing with the compromise
If you're only worried about this individual SQL Server, you all are thinking too narrowly.
What typically happens is an attacker gets control of one system and then uses it to attack other systems. Typically an attacker with any sort of skill will install a trojan or some other sort of backdoor that they've run through the various AV engines. That means you have to assume the server itself is compromised.
Because of the way password hashes are stored and because these hashes can be used in a Pass the Hash attack, and also because of the fact that any service account being used on the system (such as SQL Server's own) can be dumped using an LSA secrets attack, you must assume that any logins used to log into the server via the console or via RDP or which run as a service are compromised.
Given all of this, it's important to:
1) Pull the box off-line
2) Perform any forensics you wish to do
3) Wipe the server and start over
4) Reset any accounts that may have been compromised
I know you have to take care of the access of your users and ensure minimal data loss, but you need to these 4 steps as soon as you possibly can. Even if they've fixed the firewall, a lot of trojans "phone home" to a CNC server, and most folks don't practice whitelisting outbound traffic through the firewall.
Auditing Logins
It is typical to audit failed logins. It is not typical to audit successful logins. That throws up so much noise to render the auditing useless and is only typically done on high security systems.
Auditing logins will not provide them the IP address they are looking for. Their IDS/IPS was their best mechanism for getting this info. If they have IDS/IPS.
Disabling xp_cmdshell is not a false sense of security
There are several good reasons to do this.
1) They prevent against a mistake by someone with privileges who just doesn't think. For instance, if you shouldn't be running a command shell query from a production server but it's okay from dev, having it turned off in prod will remind them they are on the prod box. We've all been guilty of running a query against the wrong server if we've been a DBA for any length of time.
2) It stops script kiddies and those who don't know much about SQL Server. To get xp_cmdshell back on involves knowing sp_configure and RECONFIGURE. If an attacker only knows the rudiments of how to attack a SQL Server, then this stops their attempt. Yes, they can then go search on the Internet for the answer, but it gives you something they don't want to give up: time.
3) It can cause a trip against a functioning IDS/IPS. A lot of IDS/IPS will flag the string xp_cmdshell. In order to turn on xp_cmdshell when it has been turned off requires an attacker to execute sp_configure 'xp_cmdshell', 1. Guess what? That triggers the hit. So does the initial xp_cmdshell use, which will fail. It may only give you a few seconds notice, but if the attacker has to search for the answer, your IDS/IPS just tripped, your security folks should get alerted, and you know you're under attack. If it's the case like in #2, that may give you just enough time to break up the attack.
It doesn't do a lot. However, to say it provides a false sense of security is incorrect. It's just another way to lay a tripwire to try and detect an attacker. Of course, for #2 and #3, if your organization doesn't have an IDS/IPS set up properly, it's all a moot point.
K. Brian Kelley
@kbriankelley
April 3, 2014 at 8:32 am
If we disable sa login on the server and all the database owners are sa. Do we see any conflicts in the reboot of the machine or restart of the services?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply