Four fast ways to improve security in SQL Server 2008 R2

In this article you will learn four quick methods for increasing the security of a SQL Server 2008 R2 instance. This article is intended for Windows systems admins who don’t know (or don’t want to know) much about SQL Server.

Timothy Warner By Timothy Warner - Tue, November 22, 2011 - 12 comments

Timothy Warner is a Windows systems administrator, software developer, author, and technical trainer based in Nashville, TN.

If you are a Windows systems administrator, then there is a good chance that you are also an unwilling SQL Server DBA as well. After all, many organizations host line of business (LOB) applications that use SQL Server 2008 as their back-end data store.

Your organization may be subject to industry and/or governmental regulations that require you to step up the security of your IT infrastructure. On the other hand, the mere threat of data penetration or compromise may have you asking the question, “How can I secure our SQL Server databases without my having to know T-SQL and SQL Server architecture?”

The purpose of this article is to provide you with some nitty-gritty, fairly easy to implement security tweaks for SQL Server 2008. Of course, there is no such thing as a secure system. Instead, we have degrees of security, from low to high. Nevertheless, by following the tips I give you in this article, you will substantially improve the security of your SQL Server systems.

Tip #1: Change the default SQL Server port

By default, the first installation of a SQL Server 2008 engine (called the default instance) listens for incoming connections on TCP port 1433. This is bad news, of course, because any malicious user seeking to fingerprint your network will probe this well-known port in search of SQL Server services.

Additional installations of SQL Server on the same box (called named instances) will be dynamically assigned a TCP port number by SQL Server. However, I recommend you hard-code a personally chosen (and non-standard) port number for all of your SQL Server 2008 services.

Remember that SQL Server 2008 consists of several different services, each requiring its own TCP socket:

  • SQL Server Database Engine
  • SQL Server Agent
  • SQL Server Analysis Services (SSAS)
  • SQL Server Reporting Services (SSRS)
  • SQL Server Integration Services (SSIS)

Before you change the listener port for SQL Server, please check in with your application development team beforehand. The last thing you want to do is to inadvertently break your LOB applications that may be hard-coded to connect to SQL Server by using the default port.

You’ll also want to check in with the individual(s) who manage your corporate firewall, because exceptions will doubtless need to be created for the newly assigned SQL Server port numbers.

Rather than give you the click-by-click procedures for securing SQL Server in this article, I will instead point you to hand-picked external Web sites that have already undertaken the task in excellent form.

For instance, to learn how to change the SQL Server listener port numbers, please consult the following article:

Tip #2: Harden SQL Server service accounts

Each SQL Server 2008 engine that you install has an associated Windows service account. Your first plan of attack is to create a separate low-privilege Active Directory user account for use by each SQL Server service.

The reason why you don’t want to use a single service account is that if someone compromises the account, they would be able to control all SQL Server services.

SQL Server will automatically grant the service account any relevant OS privileges; therefore, you should never make your SQL Server service accounts a member of any administrative group.

Here are a couple of nice tutorial links on how to specify service account identities for SQL Server 2008:

We also want to ensure that our SQL Server service accounts have strong passwords. This subject introduces a common Windows administrator conundrum: password policy. Normally, when user accounts have password expiration, this produces headaches with service accounts and potentially breaks LOB applications that contain hard-coded references to service account passwords.

If you run Windows Server 2008 R2, you can leverage the stunningly awesome managed service account (MSA) capability and let Windows itself manage the passwords for service accounts.

To create and manage MSAs, we use Windows PowerShell 2.0. See the following link for instructions:

Tip #3: Take control of authentication

As you may already know, SQL Server 2008 supports both Windows-integrated authentication as well as SQL Server authentication. Unless you have a compelling reason to use local SQL Server accounts, I strongly suggest that you go with Windows authentication so you can take advantage of:

  • Domain password policy
  • Kerberos authentication protocol

You also already know that you should change the names of your default Administrator and Guest accounts, right? Right.

Even if you choose not to enable SQL Server authentication, you should rename and potentially disable the built-in default system administrator (sa) SQL Server account. This can be accomplished either with the SQL Server Management Studio GUI tool or by running a couple simple Transact-SQL statements:

Tip #4: Limit access of Public role and Guest account

SQL Server security is a thorny conceptual bush, indeed. The role-based security model in SQL Server includes a Public server role as well as a Public database role in every database within an instance.

The Public roles function very similarly to the Everyone and Authenticated Users Windows identities. SQL Server grants the CONNECT system privilege to the Public server role, and the Public database role has SELECT privilege against several system views.

Therefore, you want to strip as much access from the Public roles as you can get away with, and certainly not grant the roles any additional privileges.

For more information on this subject, see the following:

Each SQL Server database also includes a Guest database user, which can be renamed and disabled but not dropped.

Conclusion

The tips I provided you in this article will go a long way toward making your SQL Server 2008 instances less vulnerable to abuse/attack and more in compliance with any industry and/or governmental security mandates.

Please feel free to share any additional easy-to-implement SQL Server security tips in the comments portion of this post.

-1+1 - Rate this post
Loading ... Loading ...
Disclaimer
Your question wasn't answered? Please ask in the new 4sysops forum!

12 Comments- Leave a Reply

  1. David Nemeth says:

    I would really like to dive into ‘low priviledge access account’: when you, for example create a plain domain user account for such a SQL server service (let’s call it Somedomain\sql-service-1) there are a few things that will need to happen:

    - Log on as a service right is given (hopefully)

    but what other priviledges (such as Read/Execute to folders/files or better yet, Databases or internal SQL structures/settings do you all of a sudden find yourself having to give this service account user access to?

    The topic is right on but perhaps in another article you could elaborate as to what exactly happens after these security precautions are implemented: What will application developers need to modify (connection strings etc.) and perhaps OTHER priviledges that the Administrators class inherits…

    Not to mention the fact that now you’ll probably need to set an SPN to authenticate users from IIS properly (in a shop where IIS & SQL are on different servers etc…)

    Good article – hungry for more.
    (“Input, More Input” – as Short Circuit once said)

    David

  2. Andreas Erson says:

    Regarding the second part of tip 2.

    SQL Server 2008 R2 and older doesn’t support MSA/virtual accounts. SQL Server 2012 do support them though.

    Source:
    http://technet.microsoft.com/en-us/library/ff641729(v=ws.10).aspx

  3. Tim Warner says:

    Hi Andreas,

    Thanks for the clarification. Although you technically CAN associate your SQL Server 2008 R2 services with MSAs (try it yourself in a dev environment), this is an unsupported configuration. Ref: http://goo.gl/K3BTM

    Take care,
    Tim

  4. Andreas Erson says:

    Tim,

    Yeah, I’ve been using it in our dev. enviroments for some time. Later on I found out that it wasn’t supported so I never started using it in production. But I sure will when SQL 2012 is released.

  5. Tim Warner says:

    Hi David,

    I’ve been giving what you said quite a bit of thought, and I will definitely put up at least one post on the subject. In the meantime, have you checked out Process Explorer? (Ref: http://technet.microsoft.com/en-us/sysinternals/bb896653) This tool shows you exactly which system privileges have been granted to a particular process.

    Thanks for sharing your thoughts,
    Tim

  6. David Nemeth says:

    Hi Tim,

    Yes, I’m very familiar with Sysinternal tools. When we’re dealing with dozens of servers & systems, there is not a lot of time for “oh, I don’t knows” – the one or the team has to have a plan for making all the pieces fit together – what rights & implications such a reduction is rights means for other services or systems interacting with this.

    Example: we had a programmer who created a simple report with Reporting Services (to a csv file to a web share- don’t ask.) During testing the report ran fine on his dev server which had local IIS & SQL Server 2008 on. The minute we put it into a the semi-real environment (call it ‘production-test’ it never could generate the file. Come to find out the less priviledge account he was using didn’t have permissions to write to the network share (which only had admin priviledges.)

    This is just a simple example of what I mean – I guess what I’m after is “So what does least priviledge do to a 2 or 3 tier system (where IIS, Application & SQL servers might be on a different hosts?)

    David

  7. Brandon Aiken says:

    I would say that #1 does not add to security at all. It merely adds to *secrecy* which is not the same thing. It may be desirable to make the service ports secret, but in the absence of IPSec or firewalling, moving your service ports will not make a system more secure. Additionally, IPSec and firewalling can be done without moving the service port at all.

    It’s important to keep Kerckhoff’s principle in mind: a real enemy knows the system. Or rather, the system is only truly secure if it’s still secure when the enemy has a perfect copy of the configuration.

  8. Brandon, Kerckhoff’s principle might apply to cryptography which is a mathematical discipline. I’d like to cite Einstein here: “As far as the laws of mathematics refer to reality, they are not certain; and as far as they are certain, they do not refer to reality.

    From this follows that there is no such thing as “certainty” or a “truly secure system” in reality. However, security through obscurity (secrecy) works very well as every chameleon can tell. Empirical data shows that services which don’t use the default port are far less attacked because many hacker tools exist which just scan networks for default ports to save time. Real world security has really nothing to do with making systems “truly secure”.

  9. Tim Warner says:

    Brandon, thanks for sharing. Having worked in commercial software development and publishing for quite a few years now, I have learned that your software will always eventually be pirated. A goal in securing the software is to help keep honest people honest and to make the hacker’s work as troublesome as possible. If nothing else, changing the SQL Server ports gives me some comfort inasmuch as I am obfuscating my network infrastructure enough to ward off at least entry-level hackers. :). -Tim

  10. Brandon Aiken says:

    Michael, while Kerckhoff’s original statement was related to cryptographic algorithms, it has been generalized to general system design. Typically it is referred to as “security by obscurity” but I dislike that phrase as people tend to use it as a pejorative. Nevertheless, the basic assertion that you cannot rely on secrecy (of an algorithm, or a system) to ensure security stands. A locked door is secure. An unlocked door, even secretly hidden behind bush, is not.

    And yes, no system is perfectly secure. That does not mean we should ignore things that make a system *more* secure. A principle that states “a system which does not do A cannot be truly secure” does not mean “a system which does A *is* truly secure”.

  11. Brandon Aiken says:

    Tim, I’m not really referring to piracy of your software. I’m talking about knowing the configuration of the system. That is, you assume they know your app server is websrv008 running IIS 7.5 for an ASP.net app, the database is on sqldb004 running SQL 2008 R2 SP1 and listens on port 51045, and the legacy thick client is written in Java 1.4. None of these things are particularly difficult to determine given user-level access to a workstation on the network.

    Essentially, it is unwise to assume that a hacker that is both intelligent enough to penetrate your SQL system yet unintelligent enough to do something as trivial as running a port scanner. You talk about entry-level hacking, and to me port scanning is fairly entry-level.

    Yes, secrecy can be valuable, and if you need to keep the SQL server exposed to the whole network for your application it may very well be wise to move the port simply to reduce the liklihood of someone seeing it when they run SQL Query Analyzer. Nevertheless, that port change does *not* add to the security of the system, and it’s dangerous to think that it does. It is a false sense of security.

    Moving the door of your house from the front to the side doesn’t increase security if you use the same lock. It’s still the lock that is actually securing the door, and if they have a key they still completely circumvent your system.

  12. Brandon, I think you didn’t get my point. Let me put it this way. A locked door is NOT secure. A professional needs between 1 second and 15 minutes to crack a locked door depending on the kind of lock you use. On the other hand a perfectly invisible door would be more secure than most locked doors.

    However, what you can say is that a locked door is MORE secure than an unlocked door in most cases. IT security is never about making systems secure, but only about making them MORE secure or secure enough for a certain purpose. Thus security through obscurity works not because it makes systems secure, but because it improves their security significantly enough to justify the corresponding expenses.

    There is no room for theoretical discussions here because you can prove this empirically and you can even do it yourself. Expose two identical machines to the Internet, one that uses the default SQL server port and one that uses an unused port, preferably one with a high port number. Then you just have to count the number of attacks on both machines and you will see what I mean. If you want a more realistic test, you can delay SQL Server security updates for a week or so, and then wait which system gets cracked first.

    Hence, this very cheap security measure improves security significantly and you unnecessarily endanger your system if you don’t use this simple trick. Note that this is not only true for systems that are exposed to the Internet. Many viruses and worms also only attack default ports to save time.

    I estimate that 99.9% of all security incidents are not because of attacks of the super smart hacker. Admins are usually busy to fend off a myriad of script kiddies who use downloaded simple hacking tools and just want to “play” a little with your systems. I have seen that countless times in my career.

    And please don’t tell me that you know any admin who believes that changing the default port is the only thing that needs to be done to improve security. Security through obscurity does not lead to a false sense of security if admins learn to deal with realistic scenarios. Teaching them useless theoretical “security principles” is certainly not the right way.

    There is really no reason why an admin should keep Kerckhoff’s principle in mind because its generalization to real world security is simply wrong and because it leads to dangerous decisions about security.

Please share your thoughts in a comment!

Login

Lost your password?