Although Microsoft tries to ship most products ‘secure by default’, there is almost always more you can do to lockdown a service. SQL 2008 R2 is no different. As much as possible I like to automate processes so they are repeatable, less prone to errors, and save time. To that end I’ve automated much of a SQL 2008 R2 installation, which customizes many parameters and performs some lockdown operations. Every environment is different, so it’s almost guaranteed you will need to tweak the scripts for your situation.
At a high level my process is as follows:
1. Perform an unattended installation of SQL 2008 R2, following my part 2 blog post here. This sets custom directories for user databases and log files.
2. Using SQL 2008 Studio, use a SQL query script to configure SQL auditing and point the SQL system databases and logs to a custom drive/path. This does not move any files, so you must run step #3 or SQL will fail to start. See my part 3 blog post here.
3. Execute a PowerShell script that disables some AdHoc Access to certain objects, reconfigures the SQL start up parameters, and physically moves the SQL system databases and files to their new home. See my part 4 blog post here.
4. Since SQL security is very important, creating a Windows Firewall rule to just expose the SQL server to the required remote servers is important. You can check out my basic script here, in part 5.
5. If you have a requirement to change your TempDB size or number of databases, check out part 6 for a script to help automate that process as well.
6. If full database encryption is your thing, check out this blog post. Transparent to any application!
There you have it! Once you tweak the scripts to suit your needs, SQL installations can become much faster, repeatable, and more secure. Remember step #2 and #3 depend on each other. Don’t run one without the other or SQL will fail to start.