As one of the more popular database servers on the market, Microsoft SQL Server enjoys a fairly large installed base. Like every other piece of software, you must make security decisions when you install it. Here are five tips for locking down your SQL Server 2000 installation to keep your data safe from prying eyes.

Tip 1: Apply the latest service pack and look for security-related hot fixes
At the top of almost any security “to do” list is making sure that software is properly patched. For a brand-new installation, your best bet is usually to install the latest service pack and any hot fixes that have been released. If you’re attempting to secure a production system, be sure to test the service pack and check for conflicts between patches/fixes and any installed software before rolling out the software.

As of this writing, the latest service packs for popular versions of SQL Server include the following:

  • Service Pack 2 for SQL Server 2000
  • Service Pack 3 for SQL Server 7.0
  • Service Pack 5a for SQL Server 6.5

You can download these service packs from Microsoft’s SQL Server Support Web site, where you’ll also find a number of hot fixes for such security flaws as problems with unchecked buffers, which can let hackers run their own code on your server by overflowing an input buffer.

Tip 1a: Sign up for Microsoft’s security alerts
Security hot fixes are important, so you need some way of knowing when they’re released, other than checking the SQL Server Web site every 15 minutes. You can sign up with Microsoft to have security alerts e-mailed directly to you—simply sign up at Microsoft’s Security Web site. If you maintain Microsoft products, I highly recommend signing up for this service.

Tip 1b: Use Hfnetchk
In order to make it easier for Windows and SQL administrators to keep up with security fixes, Microsoft released a tool called Microsoft Network Security Hotfix Checker, or Hfnetchk. Hfnetchk checks for unpatched Windows servers and lists any hot fixes that haven’t yet been installed on the system. In its current release, version 3.32, Microsoft has added the ability to look for SQL patches.

To use Hfnetchk, simply download it from Microsoft’s Web site, double-click the installation package, and give it a directory name. Hfnetchk is a command-line utility without a graphical interface.

Running Hfnetchk is easy. Just switch to the directory you provided during the installation, type hfnetchk at the command line, and press [Enter]. The utility will download a CAB file from Microsoft containing a list of all of the current patches and then compare that list to what it finds installed on the system.

Listing A shows sample output from my lab installation of SQL Server 2000. I’ve intentionally left it unpatched to show you the results of Hfnetchk. I’ve also omitted results not specifically pertinent to SQL Server 2000. The –v parameter tells Hfnetchk to provide verbose results, which I like to have for a better explanation of the message.

Tip 2: Fix the sa account and use Windows authentication
A key vulnerability for SQL Server is the system administrator (sa) account. Unfortunately, a default installation comes with no password assigned for this user. You may recall the recent worm that infected Microsoft SQL Servers. This worm gains access when the sa account lacks a password and the server hasn’t been updated with a recent patch.

For a production server, you should assign the sa account a very strong password and not use it for day-to-day server administration. You should also use the default mode of Windows Authentication in SQL Server 2000. This lets you use standard Windows utilities to manage your SQL Server as well as keeping a common user base.

Windows Authentication, the default mode of operation in SQL Server 2000, uses the user’s standard Windows user ID and password for SQL server authentication. The SQL database administrator can assign permissions to users and groups in the SAM database (under Windows NT 4) or in Active Directory (Windows 2000) to grant access to a database. Programmers sometimes find it expedient to use their own authentication routines, suggesting you not use Windows Authentication. For maximum security, ignore this advice if at all possible.

Tip 3: Block outside access to ports 1433 and 1434
A misconfigured or poorly configured firewall can ruin your whole day, not to mention exposing your SQL Server 2000 installation to unscrupulous online users. The SQL Server worm mentioned above exploited the failure of firewalls to block SQL Server ports, allowing the worm to infect servers. By restricting access to ports 1433 and 1434 on your SQL server to only those clients that require it, you help prevent unauthorized access. Microsoft SQL Server commonly uses Port 1433 for data access. The Microsoft SQL Monitor normally uses Port 1434.

Many organizations use SQL Server to support their dynamic Web sites. If your organization is one of these, make sure that only the front-end Web servers or the servers actually performing queries from the users can access the SQL server. Users out on the Internet don’t need direct access to the SQL server since their interaction is limited to the Web server. The Web site will handle connections to the database server.

Tip 3a: Run SQL on a separate server
If your server is strong enough, Windows 2000 is powerful enough that you could conceivably run your entire Web site under IIS, with SQL Server handling database requests and Exchange 2000, DNS, DHCP, or anything else, all at the same time. This isn’t a good idea. SQL Server should run on a server all by itself, separate from your Web servers and not exposed to the Internet. In a truly secure environment, the SQL Servers would instead communicate with the Web servers on a private network using private, nonroutable IP addresses. Figure A gives an example of how the network should look.

Figure A
SQL Server should run on individual servers, running private TCP/IP addresses.

Tip 4: Choose sysadmin role members carefully
SQL Server uses a number of predefined roles, and each role has a specific set of capabilities. SQL Server roles are analogous to Active Directory Group objects. I like to use roles because I can add and remove members to the roles much like user/group memberships. Like an Active Directory group, assigning permissions to a role is easier than assigning them individually to users.

Among the predefined server roles in SQL Server 2000 is the sysadmin role. The sysadmin server role has the full range of privileges to every object in SQL Server, and they’re always considered database owners. Because the sysadmin role is so powerful, only well-trusted users should be members of this role.

The sa login is considered a member of the sysadmin role, as is the Windows Local Administrators group. As a part of your efforts to secure this role, you should consider whether it’s necessary for your Windows administrators to be SQL administrators as well. If they don’t require this level of access, you can remove the Windows group from this role.

Tip 4a: Don’t ignore the other fixed roles
While the sysadmin role provides the most generous set of permissions in SQL Server and should be assigned with the most caution, you should be just as diligent when considering other role memberships. Some of the other roles include the following:

  • Serveradmin—This can be thought of as a SQL Server role that doesn’t administer databases, just the server. Its members can perform such actions as shutting down the server and changing system options.
  • Securityadmin—Members of this role can modify security permissions for the databases they can access as well as read the SQL error log.
  • Db_owner—The only non-server-based role that I’ll discuss, db_owner is actually a database role. It’s the equivalent of sysadmin on the server level, as it allows its members the full range of rights to the database, including backing it up, restoring it, granting or revoking privileges, and more.

Tip 5: Keep good logs and audit use
SQL Server and Windows are both capable of using auditing to track who is using a server and, in some cases, determining what they’re doing. For SQL servers that contain fairly sensitive information, such as payroll or financial data, it’s appropriate to keep track of this information. In these types of environments, you should enable auditing for failed login attempts at a minimum. This will keep you apprised if there are a number of attempts on your SQL Server and let you take steps to track down the problem.