TECHNOLOGIES: SQL Server | Database Security
8 Crucial SQL Server Configuration Tips
Setting up SQL Server properly can go a long way toward preventing hacker attacks.
By Beth Breidenbach
Editor's Note: This article is an online companion to Beth Breidenbach's Defend Your Database published in the November/December 2002 issue of asp.netPRO.
Not everyone has the luxury of relying on a database administrator to configure their database. If you find yourself in the position of needing to set up and administer SQL Server, here are some basic pointers you should keep in mind.
1. Review Microsoft's security configuration white paper. This paper is located at http://www.microsoft.com/sql/techinfo/administration/2000/2000SecurityWP.doc. The paper might seem a bit overwhelming if you haven't worked in this field before. Still, print it out. Keep it in a binder next to your server console, handy for reference. It contains a foundation level of knowledge you can refer to as you work through installation and configuration issues.
2. Be sure your SQL Server service runs as a low-privileged user account. Your service does not require Administrator or LocalSystem privileges, and allowing such privileges is a dangerous security risk. If your service needs to be changed to a different user account, make the change from within SQL Enterprise Manager. Enterprise Manager also makes the necessary changes to ACLs, user privileges, and registry entries at the same time.
3. Apply all the latest patches to both your OS and SQL Server. Microsoft's Baseline Security Analyzer scans your system and provides a list of security updates that need to be applied. You can download the application from
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/tools/Tools/MBSAhome.asp. In addition, monitor http://www.SQLSecurity.com for announcements of newly found vulnerabilities.
4. If your server is accessible to the Internet, firewall off ports 1434 and 1433. You should not accept direct connections to SQL Server from the Internet. The typical configuration is to allow connections only from the Web site, which should be on a different server than the database. It's not uncommon for the Web-related database server to be distinct from the main corporate database servers, with network connectivity restrictions (firewalls) between the two. This creates a DMZ (demilitarized zone) to protect the corporate resources.
5. Be certain the "sa" account has a strong password. Do not allow this user account to be used by any applications. Users and applications (including Web sites) should connect with enough privileges to perform their functions, and no more.
6. Configure the server to log failed login and object-access attempts. From within Enterprise Manager, choose Tools | Manage SQL Server Messages. Search for messages regarding permission denial using search terms such as "denied," "failure," and "Login Failed." From the search results screen, select those messages you'd like to log, click on the Edit button, and select the "Always write to Windows eventlog" checkbox in the dialog box.
7. Drop the xp_cmdshell stored procedure (if you can). This procedure opens the server to the risk of unwanted commands being issued to the command shell. The commands will run with the all privileges granted to the SQL Server service's login account. (Are you beginning to understand why the service shouldn't run as Administrator?)
8. Drop system stored procedures for registry access and OLE automation unless you absolutely need them. Be sure to test this removal on a development server first before deploying the changes to a production server.
The tips provided here address some of the most blatant security configuration mistakes. But they are only the tip of the iceberg. As a next step, you might want to review a more detailed configuration checklist, such as what you can find at http://www.sqlsecurity.com/DesktopDefault.aspx?tabindex=3&tabid=4. For automated assistance, you might consider NGSSoftware's SQL Server auditing tool, SQLSquirrel. Details about SQLSquirrel are available at http://www.nextgenss.com/software/ngssquirrel.html.
Beth Breidenbach is a product architect for Getronics, a Netherlands-based provider of software and infrastructure solutions throughout the world (http://www.getronics.com). Her group develops branch, Internet, and call center applications targeted at top-tier financial institutions. A self-professed "data geek," Beth has an abiding interest in all aspects of data design, storage, transmission, and translation. E-mail Beth at mailto:[email protected].