Building up to SQL Server Security

20 Mar

There are lots of things we do to be amazingly secure without even knowing it. We must learn about the security steps everyone else has taken before us so we don’t undo them. Uptime and usability can often interfere with sound security practices. The smart ones appreciate an access denied message once in a while.

I’m a fan of the distributed platform. Its a way of not putting all of your eggs in one basket.

5 rings of death fire

Every company has or should have an inner sanctum where the most precious of storage devices are held. In fact you should have two inner sanctums, one where the encrypted data is stored and one where the private encryption keys are stored. Separating duties is important so one person doesn’t run in godmode(sysadmin AND key admin).

What I want you to picture is your data as it sits right now. Consider the data in rings separated by red lines, the outer most ring might be the data that is cached in the clients browser(1). The secondary ring might be data that persists on the web server(2) such as a password in a config file or the non-persistent data such as in-memory compiled code.

At this point your 5 rings of death fire might start to look more like an onion. There are authentication requirements that force you out to a protected domain controller(2.5) before you can pass into the next layer of the database server(3). Once you authenticate to the database server there are authorization rules that grant you access to stored procs and views(4). Maybe then you have to reach out to a key to decrypt the inner most treasure(5). You might be picturing the inner sanctum as something that is small, but I would suggest you put your data warehouse in it.

The 5 rings of death fire is just one way of thinking about what you are trying to secure. These hurdles or red lines may have already been setup for you so don’t go knocking them down.

Physical security

Yup, still important even if you don’t own your cloud. Its mostly a joke with today’s VDI, VPN and RDP. Popping a guys eyeball out to pass the retina scanner was cool in the movies but unnecessary. We still need to lock down console access. If a local firewall service is disabled, the console access is the only way to enable it. Also, consider the piece of paper I know you have stored in your top desk drawer that has all of your passwords. All it takes is one crazy party for that to disappear.

Network Security

This is another low level security measure. Want to lock a hacker out, unplug your cat5. You won’t get much done but neither will they. It is very important to understand three things are needed to open up a connection, two IPs and a direction. One IP is the client app and the other IP has the listener. SQL Server listens by default on port 1433 but if you want to change the port you can, just use a comma in-between server and port when connecting. Telnet is the simplest program to check to see if you have a clear path to your listener or server. This is a step up from pinging because Windows turns ping (ICMP echo request) off by default. The direction is important because you can ping the client from your server but you can’t ping the server from your client.

Chances are you are not going to allow the internet to open up port 1433 connections to your database. If you are, you need to restrict the ability to do this using a whitelist and secure the traffic using certificates. Separation of duties is dictated in many compliance directives such as PCI. Two physical hardware firewalls, separately maintained (two people) is a requirement. Separation of applications and services on hosts is also a requirement.

Tools such as wireshark and netmon are powerful tools and should not be left on your server. You can prevent installs from non-administrators but if the weapon is already locked, loaded and key stuck in the case there is no need for administrator privileges. Use these tools to explain to your Java developers why you should use Kerberos instead of NTLM or SQL Authentication.

Windows Security

Patch patch patch patch patchity patch patch. Let me repeat that, patch your servers. Critical security updates that could potentially apply to your system is a must. Again the uptime nazis will have something to say about this and have a pretty good argument. They might say not to bother with patches that don’t apply but that is somewhat unrealistic to have Windows Admins investigate every patch given the workload. Sometimes the KBs are not particularly clear what is affected. My stance is to have your testing environments installing updates on a regular basis, daily perhaps or Wednesday’s after patch Tuesday’s. Production system should follow shortly after or once a month. In my three years I have seen 0, count it 0 windows patches that have broken any application let alone a Microsoft app. Don’t let anyone’s change paranoia stop you from patching.

Remember domain admins/enterprise admins/local admins have access to just about everything. 2008 stopped giving built-in\admins sysadmin but that was easily worked around with a psexec.exe -s call to sqlcmd.exe. 2012 is a little better by not granting the nt athority/system account sysadmin, however there is already ways around that out on the net


You may be able to open a connection to sql but can you login? You can sit down at this computer but the first thing you (should) see is a login prompt. What SQL has done quite well is integrate with Windows Active Directory security and use Kerberos. R2 didn’t quite make the cut for MSAs but 2012 does.

Windows Auth is great but makes an argument for physical security. The ceo’s and managers should definitely lock their computer when they are away because someone can sit down at a computer and use the token already generated to open a connection to sql and query away. No passwords needed. Even with that loophole Integrated Security is stronger than SQL Auth by a 1000 million percent. There are two protocols to choose for a login and one is still there but turned off by default. Like I said, don’t knock down those hurdles.

You should turn on failed and successful login logging. I overheard a friend say, “Nobody cares about the failed logins, its the successful ones we need to worry about.” Its intriguing but we need both to identify brute force attempts. Failed logins logging helps debugging as well. c2 audit logging will help keep a highly compliant record of access to objects.

Windows AD groups can be used to manage users as groups of people rather than individuals. Logins to SQL can be created from these groups which I highly recommend. If you want to do this you need a good AD auditing policy and a small number of domain admins. You need a trigger to alert you when users are added to sensitive AD groups. A regular peek at who’s in the group is not good enough. Some DBAs may not be comfortable with giving your AD admins access to the database but remember what I already covered about your domain admins already having access to everything.

1 Comment

Posted by on March 20, 2012 in Network Admin, SQL Admin


One response to “Building up to SQL Server Security

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: