Configure/open Firewall ports for MS SQL 2008 R2/Windows 2008 (R2)

By default, installing SQL Server 2008 R2 on a brand new Windows Server 2008 R2 server does not open the required Windows Firewall ports.
I always wonder why they don’t give you the option during the installation of SQL and let MS make the changes for you. Anyway, MS has a tool to “Fix It” but on my Windows Server 2008 it runs but doesn’t apply to the Windows 2008 R2 setup.

You can of course follow Microsoft’s KB articles and manually add the Windows Advanced Firewall rules. For me, a script to do this was the way to go. Don’t forget to run the script as Administrator in the CMD box.

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

Check if the ports are opened successfully (see the picture).



Alerts from the HP management pack

Regarding my blog post on HP monitoring I get a lot of questions on errors which are generated by the HP management pack / SNMP hardware logs; these errors are displayed in the Computer State not in the Active Alerts.

The description is very poor but it suggests that there are issues in the HP Hardware logs.

Context: Date and Time: 11/11/2011 10:20:52 AM Property Name Property Value Processors_SNMP.HealthState HealthSuccess Cooling_SNMP. HealthState HealthSuccess RealtimeMonitors_SNMP.HealthState HealthSuccess TemperatureSensors_ SNMP.HealthState HealthSuccess Logs_SNMP.HealthState HealthError

Logs_SNMP.FailedComponent Integrated Management Log Server Others_SNMP.HealthState HealthSuccess Health explorer

The cause is in the computer’s local HP log which is in a bad state and has errors which are not resolved (cleared or fixed). Therefore it will stay in bad state.

The solution is most of the time very simple. Clearing the HP log on the local server and resetting the OpsMgr heath state will solve the issue. You can do this in the HP homepage on the local computer which is causing the issue.

SCOM 2007 R2 – Monitoring Windows Server 2012 machines in SCOM 2007 R2

Last week I got several questions on how and when Operations Manager will provide monitoring support for Windows Server 2012 so I wanted to clarify things in this blog.
There is also an official Microsoft blog post about this.

Management Group Version Management Server or Gateway Version OpsMgr Agent Version Agent OS Version Supported Configuration?
2007 R2 2007 R2 2007 R2 Windows Server 2012 No
2007 R2 2007 R2 2012 RTM Windows Server 2012 Yes
2007 R2 2007 R2 2012 SP1 Windows Server 2012 Yes
2012 RTM 2012 RTM 2012 RTM Windows Server 2012 Yes
2012 SP1 2012 SP1 2012 SP1 Windows Server 2012 Yes


As shown in the above table SCOM 2007 R2 doesn’t support monitoring of Windows 2012 in combination with the 2007 R2 agent.

But because the 2012 agent is backwards compatible installing the 2012 agent on an Windows 2012 machine, it properly reports to the SCOM 2007 R2 environment.

Expectations are that with the next CU Windows 2012 is supported.