When a Web site's traffic begins to outweigh the Web server's ability to keep up with incoming client requests, you need to upsize your Web server. This article discusses the security and performance challenges I faced in upsizing my Web site, and how I configured SQL Server to defend my Web site against intruders.
My Web site, http://www.ntsecurity.net, provides information about Microsoft-related security issues. In the past, I used HTML disk files from an Internet Information Server (IIS) to serve the content, as many sites still do. My setup worked well for a long time, but as traffic increased, server performance dropped in proportion to the additional daily traffic. And because bandwidth wasn't a factor in the degradation, I faced the task of reengineering my entire Web system from the ground up. I decided my primary goals were to regain lost performance, add performance, and ease the burden of adding new content to the Web site.
In addition, without a strong database back end, I couldn't add the new features to the site that I wanted. Items on my wish list included dynamic Web page content that changed based on how the stored content was dated, a product database that allowed product owners to manage their database records, and a simplified means of adding new content to the site. I decided that SQL Server and Active Server Pages (ASP) were the ticket I needed to meet these needs.
To drag my Web site out of the past and into the present, I built two new servers: one for IIS and one for SQL Server. With this setup, I could separate the Web client load from SQL query loads, introduce a higher level of subsystem isolation, and add the new features on my wish list. With a two-server configuration, I could use IIS to serve static HTML- and ASP-based Web pages. Also, I could use SQL Server to store Web pages and other HTML-based information in database table records, where IIS could use an ASP application to retrieve those records for display.
In the end, the two-server configuration works well. If you view my home page now, you'll see a long list of news stories. I store every item in SQL table records with a date stamp for the publication date. When the home page loads, an ASP application queries
Configuring a Two-Server Setup
Here's how I structured the two-server setup and how I designed and configured the security aspects of the systems. I used SQL Server 6.5 when I built the new Web site. Also, I assume most readers know how to create user accounts and set table permissions, so I won't go into the details of those basic SQL tasks, but I will give specific information when necessary.
I chose Microsoft Site Server 3.0 Commerce Edition because it offers features that standard IIS doesn't, such as a membership and personalization system, a built-in ad manager system, and a more robust Index Server. And because those technologies rely on a SQL Server back end, Site Server 3.0 Commerce Edition perfectly fit my needs.
In a nutshell, I installed Site Server Commerce on one system, installed SQL Server on another system, and connected those two systems on a network so they could communicate with each other. I discovered that you can run IIS and SQL Server on the same system and get reasonable performance results, as long as the system has plenty of RAM and CPU power. For example, I found that a 233MHz Pentium II server system with 128MB of RAM could deliver at least 1 million pages per month without any noticeable performance degradation. However, deliberately choosing this setup isn't wise. With both sets of software running on one system, security and system failures can jeopardize your Web site's availability. If you have a serious hardware failure, such as a crashed hard disk, you risk losing data, and recovery will take longer to complete than if you had the software running on two systems. In addition, if your single system is compromised, both IIS and SQL Server will be wide open to attack. You're always better off investing in an additional, separate system so you can isolate and control SQL Server. And this point brings me to the first security consideration: connectivity between two machines that work together to deliver Web pages.
SQL Server can communicate across many protocols, including TCP/IP and NetBEUI. But for security's sake, I don't recommend using TCP/IP on a SQL Server unless you have no other choice, because TCP/IP might expose the server to the Internet if the border protection systems, such as firewalls, aren't configured to prevent direct access. If you use NetBEUI instead of TCP/IP, even an improperly configured firewall won't let outsiders into your SQL Server from a TCP/IP network.
I didn't need to provide anyone TCP/IP access to the SQL Server, so during the SQL Server installation process, I structured the server to use named pipes. This setup caused SQL Server to require the use of named pipes and to ignore any available network protocols. Using named pipes on the SQL Server system meant that I also had to configure the IIS server to use the NetBEUI protocol so that the two systems could communicate with each other. During the installation process, I chose not to enable SQL Mail because that technology might grant an intruder an easy means to transmit SQL Server user IDs and passwords to a remote system if the server becomes compromised.
To establish the necessary inter-server communication, I installed two network cards on the IIS server, one configured to use TCP/IP and the other configured to use NetBEUI. With this configuration, the network card using TCP/IP could serve all Internet-originated Web client requests, whereas IIS could use the other network card, which supports NetBEUI, to communicate with the SQL Server machine to perform database queries. Keep in mind that NetBEUI is a nonroutable protocol, which means an attacker couldn't use that system alone to move sensitive information offsite. With the basic two-server network configuration in place, I created the necessary database tables on the SQL Server machine, then addressed the preliminary security configuration of SQL Server.
For users to submit a query against a SQL Server, they need to supply a user account to log on to the server. And although SQL Server does install a built-in guest access for particular databases (master, msdb, and tempdb) and an administrator account for SQL Server (named sa), I don't recommend using either of those accounts for access to a SQL Server database from a publicly available Web application. The reasons are many, but the most prominent one is that the sa account is all-powerful and can perform any actions against SQL Server. Second, IIS usually treats a user visiting an IIS server as anonymous and therefore not trusted in any way. Also, because my Web server queries a SQL Server database table with every click on my Web site, I wanted to severely restrict any functionality available to a visiting user.
To do so, I created a new SQL login on the SQL Server (called tempacct for this example), made the login a user in only the necessary databases, and then granted that account access to only those database tables required for use with the IIS server. Also, I limited the new account so that it had no authority to modify, add, or delete records in any database tables—that is, I granted the new account only SELECT permission for each required table. Screen 1 shows this limited permission for a database table called TestTable in SQL Server 6.5.
Customizing SQL Server 6.5 Security
After I restricted user table access, I focused on the SQL Server bells and whistles that can present significant security risks. In particular, I looked at extended stored procedures and various configuration settings.
Removing extended stored procedures. Extended stored procedures are useful, but leaving some of these procedures available in a Web farm is not a good idea. For example, several extended stored procedures allow direct Registry manipulation and command execution, which can quickly lead to a compromised system. I recommend removing some of these stored procedures. Keep in mind that removing particular procedures might break management interfaces, so be sure to test any procedure removal thoroughly before placing that system into production. To test your system, run each SQL-based application and run each SQL Server management tool to ensure that you haven't removed any required procedures. If you later need an extended stored procedure that was previously removed, you can easily add it. "Risky Stored Procedures" and the accompanying tables contain a list of extended stored procedures that might compromise your Web site's security.
To find the extended stored procedures, open Enterprise Manager and navigate the server's tree to find the Databases item. Expand the Databases tree, then find and expand the Master database. Under the Master database tree, expand the Objects item to reveal the extended stored procedures tree.
Configuring xp_cmdshell. In addition to removing extended stored procedures, I made a few other precautionary security adjustments. First, I configured the xp_cmdshell extended stored procedure to use the SQLExecutiveCmdExec account for all non-sa logins. This configuration ensures that any commands that the procedure issues run in the context of the SQLExecutive-CmdExec user account. To set this configuration, open Enterprise Manager, choose the Server menu, select the SQL Server item, and select Configure. In the dialog box, select the Server Options tab, where you'll find the xp_cmdshell configuration option near the bottom of the screen. Select the checkbox next to the item, click Apply Now, and close the dialog box.
Adjusting permission settings. Next, I adjusted file system permission settings for SQL Server logs and databases. In a default installation of SQL Server 6.5, these files are in a directory called \MSSQL on the drive you specified at installation. I set this directory tree so that only the administrators group has access. And because I run SQL Server under a non-Local System account, I had to grant that account full control over the directory tree, too.
Encrypting system objects. Also, consider encrypting system objects—procedures, views, and triggers—to prevent Web site visitors from viewing clear text contents, which can reveal sensitive system information. To encrypt system objects, you need to drop and recreate each object you want to encrypt. When you recreate the object, specify WITH ENCRYPTION as a suffix with the CREATE command. For example, to create a stored procedure called MyProc, use the command
CREATE procedure MyProc WITH ENCRYPTION
To generate scripts to help you quickly recreate objects, use the Generate SQL Scripts command in the Enterprise Manager tool on the Objects pull-down menu.
Enabling auditing. Audit trails are important in any software environment, so be sure to enable some level of auditing in your SQL Server. In my installation, I set auditing to track both successful and failed logins, which helps me determine exactly when someone accessed the server regardless of whether access authorization was successful.
To enable auditing, open Enterprise Manager, choose the Server menu, select the SQL Server item, and select Configure. In the dialog box, select the Security Options tab, then Successful Login or Failed Login (or both if you want complete auditing). Then click Apply Now, and close the dialog box.
Also, I recommend running SQL Trace while you run SQL-based applications so you'll know whether your system is executing unwanted commands.
Configuring replication and remote access. When configuring your SQL Server installation for better security, you can set replication and remote access. I won't cover replication in detail in this article, but I recommend that you configure SQL Server to perform some actions that build in redundancy. And as seasoned SQL Server administrators will point out, replication isn't always the best means to achieve redundancy. Other options include log shipping (available in the Microsoft BackOffice 4.5 Resource Kit) and failover clustering, which is available with the Enterprise Edition and Microsoft Cluster Server (MSCS) software.
SQL Server's remote access configuration option controls whether SQL Server will initiate or accept connections to and from other SQL Servers. If you aren't using replication, you need to disable this feature because it presents a significant risk. To disable the feature, open Enterprise Manager, choose the Server menu, select the SQL Server item, and select Configure. In the dialog box, select the Configuration tab, and scroll down the list to find the Remote Access item. Set the item's value to zero to disable the feature, and close the dialog box. Keep in mind that any change to this configuration item takes place after SQL Server has been restarted.
Defending against Denial of Service (DoS) attacks. DoS attacks are commonplace on the Internet today, and all servers, regardless of platform, are vulnerable. An intruder can launch a DoS attack against a SQL Server machine fairly easily if the server's assigned resources are inadequate. For example, if you configure your SQL Server machine to accept only 25 user connections, someone can launch a DoS attack against your system by using a program that opens 26 or more connections to your Web site—assuming SQL Server drives your Web site, of course.
To help defend your SQL Server machine against DoS attacks, set the number of allowed user connections reasonably high. Choose a number based on the available RAM and CPU power of your system. For example, each SQL Server 6.5 user connection requires 37KB of RAM, so when your system is up with all services running, inspect the amount of free RAM by viewing the Task Manager on the Performance tab. Divide the available RAM by 37888 (37KB) to calculate the maximum number of users the system can handle, assuming all the free memory space is available for SQL connections. In most cases, your system needs some of that available memory to perform various operations, so you can't reasonably assign it all to SQL Server. But keep in mind that in many cases, IIS will use connection pooling, which causes connections to remain open for some extended period, which means those connections will not close immediately after a SQL Server user session terminates. To find the User Connections setting, open Enterprise Manager and choose the Server menu, SQL Server, and Configure, and then select the Configuration Tab.
Developing a Product Application
After I configured the two servers to serve Web pages correctly, I developed a product database application that lets users submit products into a database. I needed to structure the database so that users could modify their records without a Web master's assistance. This task was a challenge because I wanted to isolate user accounts for this application from the user accounts stored in SQL Server and Windows NT Server. In an upcoming article, I'll describe in detail how I used ASP, cookies, and some simple scripting to accomplish this task and maintain a secure system.