Data Management

Tech Tip: Secure SQL Server transactions

Here's how to secure SQL Server transactions.

By Mike Mullins

Organizations often use SQL Server as a back-end device to collect and distribute information via the Web. But with the ongoing reality of Web server vulnerabilities, it's imperative that you make sure the communication between these two devices remains as secure as possible.

SQL vulnerabilities are still prevalent due to the SQL Slammer and the Sapphire worms. According to Dshield.org, SQL ports are among the top 10 most probed ports on the Internet. This statistic means you can't afford to let your SQL Server remain unsecured.

Begin with the ports

By default, Internet Information Services (IIS) and SQL Server connect via TCP/UDP ports 1433 and 1434. Your first step in securing this connection is to filter that traffic through a firewall—from the Web server (which should be in a DMZ) to the SQL Server (which lies within your intranet).

After you secure the path between these two servers, the next step is to secure the transactional data. To do so, turn on TCP/IP filtering on SQL Server. Follow these steps:

  1. Right-click My Network Places, and select Properties.
  2. Right-click the active network connection, and select Properties.
  3. Select Internet Protocol (TCP/IP), and click Properties.
  4. Click the Advanced button.
  5. Select the Options tab, choose TCP/IP Filtering, and select Properties.
  6. Select the Enable TCP/IP Filtering (All Adapters) check box.
  7. In the TCP Ports and UDP Ports sections, add ports 1433 and 1434 to the list.
  8. Click OK to exit all dialog boxes.

Choose an authentication method

After filtering your traffic, you must decide how to authenticate this traffic. You generally have two options:

  • Windows authentication: The account you use to log in to the system on the client computer or workstation controls access to SQL Server. This is the recommended security mode, since you don't have to send usernames and passwords in clear text over the network.
  • Mixed mode: Clients connect using Windows authentication or SQL Server authentication. If the database administrator specifies a username and password for a specific database, then the system passes that username and password from the client to SQL Server in clear text.

Unless you have a specific, documented reason why it's not a good idea, I highly recommend using Windows authentication for every installation. Verify that the IUSR account on the Web server has the appropriate permissions to SQL Server, and monitor the SQL Server logs for login failures and privilege escalation.

Final thoughts

If you're adept at Visual Basic, you can increase SQL Server security by writing your own trusted connection string. For more information, check out "Setting SQL Server 7.0 and IIS Security." You can improve performance and security in one session.

Many companies often overlook Web-to-database security. Using the default settings to get a site up and running is a recipe for disaster. Instead, make sure your system is secure from the client to the Web server to the SQL Server.

Mike Mullins has served as a database administrator and assistant network administrator for the U.S. Secret Service. He is a Network Security Administrator for the Defense Information Systems Agency.

Editor's Picks

Free Newsletters, In your Inbox