Need to lock down your SQL Server 2005 or 2008? SQL Servers are a prime target for hackers and worms. Corporations assets are generally customer data which is stored in databases on these SQL servers. Administrators must take precautions to harden these valuable assets.
Initial Setup and Protection
Protection starts at the core. The operating system must be installed with all service packs patches and updates. An antivirus software must be used. Any necessary service must be disabled and shared folders minimized and they must be checked for only authenticated users. Auditing must be turned on and logging in IIS should be turned on and all default paths for the logs should be changed. Logs must be checked daily and backed up.
The sa password for SQL should be complex. SQL logs should be enabled and the default paths changed. Logs should be checked daily and backed up along with your data. SQL Patches and Service Packs must be loaded to ensure that any vulnerabilities in the original software is patched.
Placement of the Server on Your Network
Placement in your network is of the utmost importance. SQL servers are often used for ecommerce and are publicly available. We will exam two topologies administrators can consider when placing SQL in the enterprise network.
SQL servers can be placed on the IIS server. This configuration is the easier of the two configurations. The server must be placed in the DMZ of a hardware firewall. Using a software firewall is not adequate protection. Hardware firewalls perform stateful packet inspections and can often detect intrusions as they are attempted.
The second configuration is splitting the web server and the SQL Server on to two separate servers. This allows for your SQL server to be on the inside of your network behind a second firewall. This allows the SQL server to have multiple roles so that it may be used internally or externally. The webserver has ports 80 (http) and port 1433 (SQL) open. The webserver (IIS) communicates to the SQL server through Port 1433.
During communication, SQL Server supports encryption using all protocols. This prevents data traffic from being sniffed by a sniffing program. With encryption, the price or 'hit' is performance. All transactions on the web with sensitive data needs to be encrypted.
Policies and Penetration Testing
Every administrator should have written policies in place. Backups must be made daily and checked with a Disaster, Recovery and Planning policy.
You should use NMap, Nessus or Scrawlr to see what ports are opened and what vulnerabilities exist. Microsoft's Baseline Security Analyzer (MBSA) can provide a detailed summary of any computer on your network. This software should be used to scan your server to find any flaws in your setup.
You must think like a hacker and you must research security, viruses, malware and the operating system installed along with SQL to understand how you can continue to protect your server.