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.
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
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
- 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