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.
End of Article
Prev. page
1
[2]
next page -->
