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.