Collecting and distributing data is part of the responsibilities of network administration, and you must ensure that this data is verifiable and secure. Regardless of their host operating system, database servers require special attention to ensure the security of their operation.
Of course, security begins at installation. Let's look at how you can secure Microsoft SQL Server from the start.
Before beginning installation, go to the premise router or firewall and specifically block UDP and TCP ports 1433 and 1434 to the IP address of SQL Server. This will prevent a SQL injection compromise while you're installing the system.
Never install SQL Server on a Windows domain controller because an application vulnerability could lead to the compromise of your entire domain. Install each SQL Server on a fresh operating system that you've fully patched before installing applications and migrating data.
SQL Server services should always run under separate local accounts. That way, if a hacker compromises the application, other servers will remain unaffected.
If the server is going to service a Windows-based network, all connections to the server should require Windows Authentication. This spares users from having to remember another password, which they would probably have to write down to remember.
As always, service accounts require special attention to the privileges you grant them. SQL Server uses two service accounts: SQL Server Engine and SQL Server Agent. Both accounts should run as a domain user with regular account privileges.
If you use SQL Server Authentication instead of Windows Authentication, or if your server will run ActiveX scripts or CmdExec jobs (i.e., operating system commands or executable programs ending with .bat, .cmd, .com, or .exe), the SQL Server Agent account will need local Windows administrator privileges.
If you need to change the account associated with a SQL Server service, use SQL Server Enterprise Manager. The Enterprise Manager will set appropriate permissions on the files and registry keys used by SQL Server. Do not use the Services applet of the MMC in Control Panel to change these accounts.
Clean up your installation by running Microsoft's KillPwd.exe utility, which removes the clear text sysadmin password stored in various setup files during installation.
After you've cleaned the installation files from your new server, run the Microsoft Baseline Security Analyzer (MBSA). This tool scans and tests your installation for several issues, including:
- Too many members of the sysadmin fixed server role
- Granting of rights to create CmdExec jobs to roles other than sysadmin
- Blank or trivial passwords
- Weak authentication mode
- Excessive rights granted to the Administrators group
- Incorrect access control lists (ACLs) on SQL Server data directories
- Plain-text sysadmin password in setup files
- Excessive rights granted to the guest account
- SQL Server running on a system that's also a domain controller
- Improper configuration of the Everyone group, providing access to certain registry keys
- Improper configuration of SQL Server service accounts
- Missing service packs and security updates
Finally, remember to audit failed connections. This is one of the most overlooked items of installation. You can enable auditing through the SQL Server Enterprise Manager. Follow these steps:
- Right-click the server and select Properties.
- On the Security tab, select Failure under Audit Level.
- Stop and restart the server for auditing to begin.
This is only the beginning to performing a secure SQL Server installation. If your server will collect data from a public Web server, you should limit those SQL ports to the IP address of your Web server. There are many excellent uses for a database server, but they all must begin with a secure installation.