10 Steps to securing your SQL Server

Securing your SQL Server can be an arduous task, but very rewarding. Nothing makes me personally prouder than running a penetration test on a server that we just secured and not seeing any hot spots. This article will focus on the ways to easily secure your SQL Server. Although this takes care of some of the big issues, securing your SQL Server is an ongoing battle of constantly auditing and adjusting your plan. So without further ado, here's the laundry list of items to take care of on your SQL Server:

Windows Authentication if you Can

In almost every Microsoft SQL Server vulnerability I've seen, the footer of the alert usually states, "well, you should've been using Windows Authentication to avoid this problem". (paraphrased of course). Using Windows Only mode will rule out about 95% of the SQL Server security issues I've seen including every virus. For a hacker to penetrate your system with Windows Only mode enabled, he would first have to authenticate into the domain, which is much more difficult than going through SQL authentication. More importantly, no passwords are passed over the network, since SQL Server will use the user's authenticated token.

Watch your SA Account Usage

You should never use the SA password. Even if you're an admin. If you are an admin and can't use a Windows Authentication, then create an admin account for you to use. You will find yourself making lots of enemies initially as you change the SA account password across every department and production server. Yes, the password should even be changed in development and never given to the developers. For some reason, I found that if a developer knows the SA password, he'll begin to code using it since the account has the ultimate permissions and he won't have to think about what rights to give the application account.

Once you do have it changed, make sure you continue to change it periodically to avoid the word slipping out about the account. Have a process in place to where if anyone that knows the SA account were to leave the company, you can change it across every SQL Server in just a few hours time. A paranoid DBA like myself find myself wondering how many SQL Servers are on the network that I don't know about. You can use a free tool by E-Eye to scan for SQL Servers with no SA password in your network (or ones that have a password for that matter). To download the Retina SQL Worm Scanner (Version 1.0.0.0) go here:
http://www.eeye.com/html/Research/Tools/RetinaSqlWorm.exe
 

Remove BUILTIN/Administrators

As far as I'm concerned, this is the biggest SQL Server vulnerability today out of the box. I'm not sure why Microsoft feels that the machine's NT Administrators need to have SA rights by default, but that's exactly how it installs. The first thing I do on a new installation is remove this login. Beware though, before you do that, you'll need to ensure that the account that starts SQL Server has a login created. If the SQL Server does not have an account created for whichever NT account is starting SQL Server, you will have problems starting SQL Server or SQL Server Agent.

Change the Startup Account

Along those same lines, I like to change the account that starts SQL Server from LocalSystem to a non-descript login an password. If you do this through Enterprise Manager (right-click on the server's name and select Properties | Security tab) you can save a lot of work since it gives the necessary permissions to the account and does a lot of work for you. When you change account that starts SQL Server, ensure that you're assigning a new login with very little rights to your actual machine (not an Administrator!). The reason you want to ensure his permissions are tight is to prevent a hacker from getting anywhere in NT if he is able to obtain SysAdmin rights.

Also, make sure the Logon Locally policy  is denied for that account. This ensures that if someone won't be able to login with that account, to see any of your data or administrate your SQL Server. Finally, make sure the account that you change your SQL Server to startup with doesn't look like a startup account. For example, don't call the account SQLAdmin or SQLStartupAccount. Ensure that this account looks like a normal user and is not obvious to a hacker.

Auditing of Failed Logins and Denied Access

The best way to detect that you have an intruder is to put the proper alarm system up. By enabling the Failed Login option (Server Properties | Security tab), you give yourself to tool to see when an unwanted visitor is attempting to access your system. This is especially useful when you have a canned application that only uses a few accounts. If you see any failed logins at all, you know the application is not causing it, so it must be a user. The next step is to turn on Profiler and capture only Failed Logins and the Hostname. That will tell you what computer name the unwanted visitor is coming in from.

Turning on this type of auditing won't do you a bit of good unless you actually monitor the logs or setup the proper alerting system to alert you when the entry comes through. One of the best ways to do this is to setup SQL Alerts to alert you when these errors come in either through NET SEND or through e-mail.  I also like to turn on auditing of any type of permission denied error, like #229. If you find all the items you'd like to audit, you can write a script to update the sysmessages table (which holds all the SQL Server errors) to turn on logging as shown below:

 -- Error Message #229: %ls permission denied on object '%.*ls', database '%.*ls', owner '%.*ls'.
UPDATE sysmessages SET dlevel = (dlevel | 0x80) WHERE error = 229

If you're a hacker and you wanted to hide your activity in SQL Server, the ideal way to do this would be to rollover the error log through DBCC ERRORLOG five times, thus eliminated any evidence you were there. To defend against this, I recommend that you add a registry key (if it doesn't already exist) to increase the number of logs that SQL Server will keep from 5 to at least 10. The below key can be imported to do just that.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer] "NumErrorLogs"=dword:00000010

Finally, consider auditing regularly for logins that don't have a password. You can do this through a simple query (note that the Windows accounts never store a password so the isntname = 0 parameter filters those out).

use master
go
select name, password from syslogins where password is null and name is not null and isntname = 0

Stay Up on Service Packs and Hot Fixes

Staying up on service packs and hot fixes is the best way to prevent the very experienced hacker. Most of the vulnerabilities I see that are corrected by service packs and hot fixes are hard to exploit, but once exploited, are very very dangerous. Always plan to update your SQL Servers at least once a quarter with any hot fixes or service packs. You can also stay ahead of the game by subscribed to the Microsoft Security Alerts at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/notify.asp. Once you've subscribed, an e-mail will be delivered to you when a vulnerability is found. Unfortunately, there's no way to isolate the messages to just SQL Server ones.

Protect Your Extended Stored Procedures

SQL Server provides some very handy system and extended stored procedures. The only problem with this these stored procedures will use the same rights as whichever account is starting SQL Server. If you are not using replication or SQL Mail, start SQL Server with the system account. You will want to after that, lock down any stored procedures that you do not use on a regular basis. Especially any extended stored procedure such as xp_cmdshell. This extended stored procedure allows a user to have access to any command level actions and could act indirectly as a gateway to the rest of your network. I would not drop any of these stored procedures however. Enterprise Manager commonly uses these to access system level functions and dropping these stored procedures, will present some new bugs. I would though deny access to every account that does not need them and if you feel that you've done enough testing, then remove them.

Change the SQL Server Port and Block it

One way to slow down port scanners and hackers attempting to find your SQL Server is to change the SQL Server port. Most "small-time" hackers will get bored after scanning your network for the commonly used ports. To prevent the experienced hackers though, you'll need to make sure your firewall protects your SQL Server from all unknown traffic. You can change your SQL Server port under the Server Network Configuration utility and then highlight TCP/IP and select Properties. Check with your network administrator to make sure that your firewall allows this port where needed before making this change.

Control Access Through Stored Procedures

Always try not to give direct access to your data. Instead, control all data access through stored procedures and grant access to those instead of giving a blanket db_datareader and db_datawriter permissions. Once you use stored procedures, make sure you code properly in ADO to use them. This helps protect you from SQL Injection attacks, which we'll talk about in another article. SQL Injection attacks allow a hacker to run any type of SQL command he wishes through a form in an application. One bonus to using stored procedures is that they're easier to deploy. For example, if you don't use them, you'll have to either recompile your application or redeploy ASP pages ever time a query changes.

Protect Your OS

The old saying states, "You are what you eat". If your OS is not protected, then your SQL Server is wide open. It's like locking the door but leaving the window open next to it. In another article coming shortly I'll talk about how to protect your OS running SQL server.

Don't forget to test out your solution and test often!  Document your final solution so when new servers are built, you don't forget to apply the same patches. Hopefully this article gives you a good baseline to start at for securing your SQL Server.

출처 : http://www.sqlservercentral.com