In my last post in this series, I talked about "blind SQL Server installs" and some of the potential consequences of making uninformed choices during setup (or of just accepting all of the defaults).  Today I wanted to touch on security a bit.

I frequently see cases where we put too much trust where we shouldn't.  In the real world, we've all done things like this, and I'll be the first to admit it:

• given a "friend" a key to your house, not thinking that they could make a copy to use later at their own discretion;
• trusted your beagles with that bowl of chili on the arm of the couch, when you run to the fridge for a beer during halftime; or,
• loaned money to someone, with low expectations of being reimbursed.

We do these same kinds of things in the IT world, and I think a lot of us pretend that the potentially bad consequences couldn't possibly happen to us (or just simply aren't aware of the potential consequences).  A concept we should all be familiar with is the principle of least privilege.  Basically, it states that we should not give elevated permissions because it's easier; but rather, spend the time up front to determine exactly which permissions are needed, and only enable those.  When you find that one more permission is needed, you add it; when you find that a permission was granted that shouldn't have been, you take it away.  Unfortunately I see many situations where the principle has been: give them ALL permissions so they don't come and bug us for individual permissions.

So I want to ask some questions of you; basically, do you currently do any of these things?  If yes, do you know they're potential issues?  And if yes again, do you have plans to correct them?  Some of these apply to IT/Domain Administrators, some to DBAs, some to developers, and some to a combination.  I just want to list bullet points right now, because I don't want to reveal my reasoning for including them in the list (I do plan a few future blog posts that are more like a deep dive into a few of these).  So, that all said, do you currently:

• give every Windows user sa privileges?
• put every SQL account into the server admin role?
• make every SQL account the db_owner of every database?
• use mixed authentication when Windows authentication will suffice?
• let your web and other applications connect as sa, db_owner or a similarly-privileged account?
• use the sa account for the security context of linked servers?
• give local admin rights to developers?
• give domain admin rights to DBAs?
• blindly enable xp_cmdshell on all servers, and grant exec to public?
• run SQL Server and SQL Server Agent services as domain or local admins?
• create CLR objects and use TRUSTWORTHY ON?

A much bigger one I'll separate out, because it is the one I see far too often, and I can give some examples of how to resolve the situation.  Do you:

• expose your SQL Server directly to the Internet?

Many people seem to think this is necessary in order to get distributed applications to work.  First off, applications within your data center should be connecting to SQL Server via an internal IP, not an external one.  SQL Server should be protected from the outside world via a firewall (preferably a real, physical firewall device, and not the Windows built-in or 3rd party software).  There should be a private network like 192.168.1.* or 10.10.1.* and those should be the only IP ranges allowed into SQL Server.  If you are on a domain that is different from the data center, then you should be able to connect via VPN and/or domain trust (talk to your network admins about this if it is currently not possible).  Need to connect to SQL Server from home or the road?  I use a VPN for this, and I imagine you can too.  If you can't, then the firewall can come into play again: have rules set up that allow administrators to dictate which IP addresses are allowed to connect.  For broadband and especially for dial-up users, this can be challenging, as you can be re-issued new IP addresses periodically.  But my personal preference would be to make that call to have the rule changed, rather than let in an entire subnet of Comcast, AOL or FiOS customers.  

Don't be ashamed to answer "yes" to any of these questions – I answered yes to more than one, and do have plans to address these items. 

And before you start writing an inflamed response, let me assure you, I understand that in some cases, "yes" might actually be the right answer.  I don't want to get into a debate about whether one of my bullet points is or isn't a security concern in your specific case – I just want to mention the things that pop to the front of my mind when I think about the security of my SQL Servers, so that you can think about whether or not you're doing them, and whether or not you should be doing them.  I probably missed some, so feel free to mention anything you think falls into this category.

Finally, if you answered "no" to all of the questions, congratulations, keep doing what you're doing, and sorry for the sermon.