SQL Server 2008, Configure Windows Firewall with Advanced Security

VBScript to open firewall for remote SQL Server 2008 management

On a fresh install of SQL Server 2008 R2, I could not use SQL Server 2008 Management Studio (SSMS) from my workstation to manage the server. I quickly realized I had forgotten to open the firewall port on the server, but upon opening Windows Firewall with Advanced Security (WFAS) the only "Rule" I could find was called "SQL Server Management Studio". I verified that the rule was enabled, but still I was receiving an error when trying to connect to the server in SMSS:

Cannot connect to mydbserver.
Additional information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

Some searching turned up Microsoft article: 'How do I open the firewall port for SQL Server on Windows Server 2008?'. However, upon reviewing the script on that page I realized there were far more changes (ports being opened, rules being renamed) than what I cared for and what was needed. All I really needed was TCP port 1433 to be open.


Considering how frequently remote management of SQL Server databases occurs, I was very frustrated that Microsoft does not add a rule to WFAS that makes opening the needed port an easy process.
* end rant *


Based on another WFAS VBScript, I created a new script to run on the server:
Allow SQL Server 2008 R2 remote management (port 1433).
(link opens a .txt file, in a new window - you will need to copy and paste to a .vbs file and run using cscript.exe).

After running the script on the server, SSMS can now connect and manage the SQL Server 2008 R2 just fine. The additional rules in the Microsoft article are indeed not needed (at least for my purposes).

Notes, rule configuration

- the rule will be enabled for both 'Domain' and 'Private' profiles.
- the script creates a Group called "SQL Server"
See the comments in the script to change this configuration.

Reminder: you also need to make sure that on the server in SQL Server Configuration Manager that TCP/IP is enabled under SQL Native Client.

Quick Links, WFAS scripts:

- Enumurate all Rules to a tab-delimited file.
- Change all enabled rules where "profile=all" to "profile=domain, private".
- Copy all enabled rules where "profile=all" to "profile=public" and disable.
- Enable RDP and Ping.
- Enable Ping.
- Enable SQL Server 2008 Remote Management.
Copyright 1996-2024 Ohman Automation Corp. All rights reserved.