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 2:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 1,605, Visits: 4,595
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/
Post #1557694
Posted Wednesday, April 2, 2014 2:43 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 5,976, Visits: 12,887
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.


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

Post #1557698
Posted Wednesday, April 2, 2014 4:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 36,793, Visits: 31,250
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557725
Posted Wednesday, April 2, 2014 5:22 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
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, 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 #1557743
Posted Thursday, April 3, 2014 8:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 381, Visits: 1,858
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?
Post #1558018
Posted Thursday, April 3, 2014 8:38 AM


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
muthyala_51 (4/3/2014)
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?


This is not an issue. SQL Server will prevent the sa account from logging in, but it doesn't affect any objects (databases, for instance), owned by that login.


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 #1558025
Posted Thursday, April 3, 2014 9:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 36,793, Visits: 31,250
K. Brian Kelley (4/2/2014)
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.


In that case, you should also make it so that DBAs only have read privs. There's no difference here between running a bad xp_CmdShell command and a bad TRUNCATE or a DELETE with no WHERE clause.


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.


No it doesn't. Only "SAs" can run or turn xp_CmdShell on unless one has made the horrible mistake of giving casual user privs to run it directly. Ostensibly, only DBAs have "SA" privs and they supposedly aren't "kiddies". An attacker that get's into your server as "SA" is going to hit paydirt whether they use xp_CmdShell or not. An attacker that successfully gets in as "SA" is also very likely using attack software to do such a thing and all of the attack software that I've seen turns on xp_CmdShell as soon as it makes a connection. You're statement is kind of proof that turning it off brings a false sense of security.


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.


You and I both know that's probably not going to do a thing for anyone on most systems except provide a log that will be written testament that the security team failed to prevent an attack. An attacker doesn't need xp_CmdShell to make a self deleting job that uses an EXEC task. The attack vector the OP described is proof of that (didn't use xp_CmdShell). Having xp_CmdShell turned off will give the minor advantage that you say but I doubt it'll give most DBAs the time to do anything, never mind get an alert. The key is to keep attackers out and, if they do get in, to prevent them from attaining "SA" privs. Anything else is likely to provide a false sense of security that leads people into more lax methods.


It doesn't do a lot.


I'll say. An attacker that is actually smart enough to get in with "SA" privs is absolutely going to know how to turn on xp_CmdShell. But, again, he doesn't even need xp_CmdShell if he can get in with "SA" privs. If he decides to use xp_CmdShell, it'll take his software less than 3ms to turn it on. By the time people react, he'll already have his payload and be gone.


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.


I guess we'll have to agree to disagree here, ol' friend. There are more cottage and accidental DBAs that think that turning off xp_CmdShell will provide something more than the 3ms "tripwire" you're talking about than there ever was.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558039
Posted Thursday, April 3, 2014 9:30 AM


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
Jeff Moden (4/3/2014)
[/b]


In that case, you should also make it so that DBAs only have read privs. There's no difference here between running a bad xp_CmdShell command and a bad TRUNCATE or a DELETE with no WHERE clause.


Hmm, maybe that is why he have the option of DML triggers for data changes and DDL triggers for schema changes...

No it doesn't. Only "SAs" can run or turn xp_CmdShell on unless one has made the horrible mistake of giving casual user privs to run it directly. Ostensibly, only DBAs have "SA" privs and they supposedly aren't "kiddies". An attacker that get's into your server as "SA" is going to hit paydirt whether they use xp_CmdShell or not. An attacker that successfully gets in as "SA" is also very likely using attack software to do such a thing and all of the attack software that I've seen turns on xp_CmdShell as soon as it makes a connection. You're statement is kind of proof that turning it off brings a false sense of security.


Think about what a script kiddie is. A script kiddie is someone who doesn't have any expertise but is using a script or code someone else wrote. The person who wrote coded certain functionality and the script kiddie makes the most of it, because it's usually push button stuff. However, when that stuff doesn't work, they don't know what to do.

So a script or program that a script kiddie uses could possibly get in with a privileged account. For instance, a keylogger trojan gets installed on a DBA's workstation. The script kiddie has the credentials, plugs them into the script, and the script attempts to execute xp_cmdshell. It fails, IDS is tripped, and you know you're under attack.

Don't assume that because someone has a privileged account, they know what to do with it. Malware is often bought and sold that does a lot of tasks for you. The scenario I describe happens frequently every day.

You and I both know that's probably not going to do a thing for anyone on most systems except provide a log that will be written testament that the security team failed to prevent an attack. An attacker doesn't need xp_CmdShell to make a self deleting job that uses an EXEC task. The attack vector the OP described is proof of that (didn't use xp_CmdShell). Having xp_CmdShell turned off will give the minor advantage that you say but I doubt it'll give most DBAs the time to do anything, never mind get an alert. The key is to keep attackers out and, if they do get in, to prevent them from attaining "SA" privs. Anything else is likely to provide a false sense of security that leads people into more lax methods.


Then you've been working with the wrong security/networking teams. IDS/IPS is typically monitoring network traffic not running on the host. It'll see the xp_cmdshell going across the network and alert. This isn't new functionality. It's been around for YEARS. Free IDS like Snort have detected it for years.





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 #1558052
Posted Thursday, April 3, 2014 4:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 36,793, Visits: 31,250
K. Brian Kelley (4/3/2014)
Then you've been working with the wrong security/networking teams. IDS/IPS is typically monitoring network traffic not running on the host. It'll see the xp_cmdshell going across the network and alert. This isn't new functionality. It's been around for YEARS. Free IDS like Snort have detected it for years.


I get that. My point is that the attack cited on this thread didn't use xp_CmdShell. Having it turned off did nothing to prevent this attack which did everything that one could do with xp_CmdShell.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558299
Posted Thursday, April 3, 2014 10:15 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
Jeff,

I wouldn't make that assumption. We've only been given limited info from the forensics done thus far. We've been told:

- tcp/1433 was exposed.
- A SQL Server Agent job was found because it failed.
- It failed because it called a file, a trojan, that wasn't there.

If tcp/1433 was the only port exposed, that means the attack came through SQL Server. Yes, by using an Agent job command shell access is easy. However, did the trojan get onto the system? If it did, how did it get there? While it could have been through SQL Agent, it could have been through xp_cmdshell as well. That would likely be my approach.

Once the server is breached, using xp_cmdshell the trojan is pulled down immediately. Then the SQL Server Agent job is built and scheduled so that the trojan is executed periodically so it can phone home and get its next set of orders or get the command to go back to sleep. If going back to sleep means it ends its process, that makes it that much harder for it to be spotted. You're only going to see it if you happen to catch it when it's running.

Why do that? Because attackers have gotten smart. They realize we're now looking for traffic during off-peak times. So the best time to phone home is when there's a bunch of other traffic. There's more noise, making it harder to spot the malicious traffic. If I'm not based on the same time zone as the server or I don't want to have to actively be on it, I want to schedule the phone home. SQL Agent does the trick.

Yes, Task Scheduler could, too. However, if there's already SQL Server Agent jobs, again, the phone home command gets hidden in the noise. if I put it in Task Scheduler I've got two problems. The first is I may not get the job to run with the credentials I want. The second is there isn't a lot of Task Scheduler jobs. So there's less noise to hide the job.

Therefore, I wouldn't rule out anything. What I've described is a pretty typical hack nowadays, whether SQL Server is part of it or not.


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 #1558359
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse