Your database is under attack. You look at the database log and see that someone has been trying for hours to hack your SA password. They haven’t gotten in yet. What do you do?
I’m not a security guru, so I’m not going to provide directions here. What I can tell you is that when this happens, it’s too late to start asking the question, “What should I do?” You should have already handled the use of the SQL Server standard SA Account. This can be disabled completely, and replaced with an NT account instead. The NT accounts have a higher level of encryption, and are not well known throughout the world, because you create the account name.
You should already have an action plan for what you will do when intrusion attacks are occurring. If that isn’t your skillset, then acquire it through consulting or education. But, you need a plan. It doesn’t happen often, but it does happen.
How did the attacker gain access to your database server in the first place? Are you using the SA account for your applications to connect to your database? If so, they can sometimes hack through your application to gain connectivity, and then simply try to guess the password for the login. Does your application have more permissions than are really necessary? If so, are you simply being lazy, because your database is behind your DMZ?
Let’s go back to the first question. What can you do when intrusion is detected? Here are some thoughts:
- Change your SA password to something really complicated.
- Stop SQL Agent, SSIS, SSRS, Full Text and other SQL Server services other than the engine.
- Make sure that the account under which SQL Server is operating has limited permissions, especially file access.
- Make sure SP_CMDSHELL is disabled.
- Detach all of your databases. Yes, you interrupted your application…but that is the result of choices made long ago. OUCH!
- You could have stopped the service, but, by keeping it online you can track activities the hacker is attempting through Data Management Views. That’s why I thought of detaching the databases instead, because it keeps the SQL Server Service running.
- Review the log to see what activity the Hacker has been doing.
Now that you have stopped the hemorrhage, let’s try and get back online.
- Create a new instance of SQL Server that has the SA Account disabled, and Administrative permissions are granted to an NT Account.
- Mount the databases from the previous instance on the new SQL Service.
- Create a new Client Account granting access to your database(s). Reduce privileges and assign them to a schema instead. This can be scripted quickly using DMVs.
- Modify the connection string for your applications.
- Turn things back on and monitor.
That’s a lot of advice from someone who is clearly not a security guru. Please, throw darts, as many as you need. Leave comments with holes, suggestions or corrections.
Cheers,
Ben