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
220.127.116.11) go here:
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
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.
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).
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
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
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
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.