Geoff N. Hiten Blog

I have been working with SQL Server2005 and 2008 on Windows 2008 lately. I really like the Windows clustering improvements and want to take advantage of them. More on that in another post. One quick note that I think is essential. When you install SQL 2005 on Windows 2008, you have to manually configure the firewall to allow SQL connections from the outside world. This is in addition to any SQL surface area configuration changes you do.

1. Start Server Manager
2. Drill down to Configuration | Windows Firewall with Advanced Security" | Inbound Rules
3. Select "New Rule…" on the right.
4. The rule applies to a "This program path" : "%ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" Note that your specific execution path may be different due to instance naming.
5. Select "Allow the Connection"
6. Apply to all profiles
7. Name the Rule "SQL Server Database Engine"
8. Repeat this for the SQL Browser Service.
9. The program path is "%ProgramFiles% (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe". Note that this is from an x64 OS and SQL Install. The SQL Browser service is always a 32-bit application.
10. Name the Rule "SQL Browser Service".

The Security Configuration Wizard will create these rules for you, but will also adjust the startup settings for most of the services on the system. Not exactly what you wanted, but you don't get a choice in the matter if you use the wizard. Wonder who thought that one up?