Data Management

Secure SQL Server: Installing for security

Securing SQL Server is vital to the proper design of any database system. In this series, you get the lowdown on how to install SQL securely, protect data from prying eyes, and ensure its validity.

By Susan Harkins and Mike Gunderloy

For a number of reasons, people in a position to make security decisions often don't understand the true value of data and, therefore, often fail to secure it adequately. Keeping your data accessible to the people who need it, when they need it, and ensuring its validity is probably the most important job a database administrator has. However, keeping the data accessible doesn't mean making it open to everyone; rather, you need to carefully secure the data and then open it only to those who need to use it.

Builder's series on SQL Server security
Our goal is to give you the tools and the confidence you need to set up SQL Server securely, so that your data is protected from accidental or deliberate theft and corruption. Our series starts with some of the basic decisions and techniques for making sure that you have a secure platform to build upon. Throughout the series, we'll concentrate on SQL Server 2000, which introduces some new security features to help protect your data. In case you've missed our first article, you can read it here:

"SQL Server: Design for security from the start"

Security process
Ordinary maintenance and database modifications should take a back seat to the security process, which includes the following steps:
  • Assign a strong password for the default system administrator (SA) account. Then create your own uniquely named account to handle administration, and put that account in the sysadmin role. Make sure the new account has a strong password as well. You can then remove the SA account from the sysadmin role.
  • Assign individual passwords for each user. Better yet, use Windows' integrated security and let Windows enforce strong password rules.
  • Determine which users need to view what data and then assign appropriate permissions. Never give more permission than the user needs just because it may be easier. For example, your data entry operators really don't need to see every one else's salaries.
  • Determine which users need to change what data and then assign appropriate permissions. Account managers should probably be able to view all customer information, but you might want to limit who can actually change that information. Specifically, the account manager in charge of a particular account might be the only person assigned permission to change a customer's data.

You'll learn more about these processes throughout the series, but you should think about security issues right from the start. Otherwise, any user of your database could steal or delete your most sensitive data.

What can possibly go wrong?
For the record, you should know that SQL Server is not secure straight out of the box. You'll have to put forth some thought and effort to adequately and successfully secure your server. There are two serious issues you should contemplate before you even install your server:
  • Securing accounts and passwords for administrators
  • Protecting your system from the Slammer worm

Secure out of the box
SQL Server 2000 does have default security via the SA account. During the installation process, SQL Server automatically creates an administrative user and assigns the user name of SA with a blank password. Some administrators leave the SA password blank or set a common password that everyone knows, leaving their entire system vulnerable. Anyone can log in to your database and do as they please if you make this mistake. Anyone with administrative permission can do just about anything they want—not just with the database, but with the entire computer. That's definitely not the right attitude. You want to limit each user to just the access they need—no more, no less.

Forget the SA account for daily administration. Lock up this account with a truly secure password. Then, create another account specifically for administration (either a SQL Server account or a Windows account, depending on your authentication mode). What you want to avoid is making it easy to guess either the account name or the password of the account that controls the entire server, because if anyone manages to log in using that account, the game is over.

A cautionary tale—the Slammer worm
In January 2003 an extremely virulent piece of malicious code, the Slammer worm, targeted SQL Server installations. By taking advantage of a mistake in the SQL Server code, it was able to hijack machines with SQL Server installed and send out many copies of itself to break into other vulnerable machines. By some accounts, the worm saturated the Internet within 15 minutes of being released. Microsoft devoted extensive resources to making patches available, but the reverberations of Slammer are still being felt. Some sources even blame it for the slip in the beta schedule of the next version of SQL Server, thanks to the disruption it caused in the SQL Server team.

Why should this concern you months later? There are still infected machines out there sending out copies of Slammer every day. If you attach an unpatched SQL Server to the Internet, you may well become a victim yourself. The moral to this story is that you need to secure your server and apply the most recent service packs and patches before ever plugging in that Internet cable.

The importance of service packs
Service packs are free for the price of downloading and you can't afford to ignore them. The Slammer worm won't destroy data, but it can cause serious disruptions to your service, and that can be almost as bad. Protection is as simple as downloading Service Pack 3 or Service Pack 3a.

The technical aspects of how the worm works aren't all that important. What's important is that the Slammer worm hit hard a full six months after Microsoft released a patch that addressed the very vulnerability the worm preys upon. Businesses hit hard by the worm were victimized twice: once by the administrators who failed to protect the system, and then by the worm.

You should also remember that there's no guarantee that SQL Server SP3 fixes everything. You should monitor Microsoft's Security Bulletin page on a regular basis to make sure you stay informed of future service packs and patches.

Choosing an authentication mode
One of the first decisions you'll make during the SQL Server installation process is which security model to use. There are two:
  • Windows Authentication mode (also called integrated mode)
  • Mixed mode

Windows Authentication mode users connect to a SQL server by using an existing Windows user account. SQL Server validates the user's Windows login information when a user connects to the server using a valid username and password. As a result, users log in just once for both Windows and SQL Server. Account information is stored strictly by Windows.

Mixed mode is a combination of Windows Authentication and SQL Server Authentication. With Mixed mode, some users can continue to use Windows accounts to access SQL Server via a single sign-on. But others can use SQL Server user accounts that are completely separate from Windows accounts. Each SQL Server account stores its own username and a password. Even if these two login values are the same, the user must log in twice—once for Windows and again for SQL Server.

Windows Authentication is superior to Mixed mode for the following reasons:
  • It offers more features than SQL Server Authentication.
  • Most administrators agree that Windows Authentication is the easier of the two modes to implement and use.
  • With Windows authentication, there's no need to store passwords in connection strings—one less thing to protect.
  • Windows authentication means you only need to manage passwords in one place, instead of two.

Unfortunately, Windows Authentication isn't available to everyone. Mixed mode is required by SQL Server 7.0 (or earlier). Windows Authentication is available only with SQL Server 2000. There are also circumstances (for instance, when you're using a Web server outside a domain to retrieve data from a SQL Server inside the domain) in which Windows Authentication won't work because the credentials won't cross the domain boundaries. In these cases you must use Mixed mode and SQL Server logins.

Other installation tips
There are a few other things you need to think about when installing SQL Server:
  • Use TCP/IP as the network library for SQL Server. This is the library that Microsoft recommends, so it stands to reason that it's the best tested. If the server will be on the public Internet, use a non-standard port to make it just a tiny bit harder for the bad guys to find it.
  • Use a low-privilege account to run SQL Server, rather than an administrative account. This will limit the damage in case of any security breach.
  • Don't allow the unsecured guest account access to any database containing secured data.
  • Physically secure the server in a locked server room or closet. Remember, most intrusions come from insiders.

The verdict
Make security your number one priority from the very beginning. Whether protecting your data from incompetence or intentional destruction, take the time, learn the security model, and then apply it carefully. It'll only take one security nightmare to make you a believer. Take security seriously and sleep better.

Coming up next
If you follow the recommendations in this article, you'll end up with a SQL Server that is so secure that only designated Windows administrators can use it. That's because you haven't yet created any user accounts. Clearly, that's not useful for most business scenarios. In the next article of this series, we'll show you how to give other users access to the data that they need in a controlled fashion.


About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox