SQL Server – Reporting Services – Administrating Using SSMS – WMI Network Port – Requirements

Background

We are experiencing errors connecting to a remote SQL Server Reporting Services Instance over SQL Server Management Studio.

 

Error Message

Here is the error message that we were getting.

Error Image

connecttoserver-20170105-0351pmwireshark-netshfw-20170105-0349pm-clipped

Error Text


TITLE: Connect to Server
------------------------------

Cannot connect to DBSERVER.

------------------------------
ADDITIONAL INFORMATION:

An unexpected error has occurred. Details:

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.Management.UI.RSClient)

------------------------------

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (mscorlib)

------------------------------
BUTTONS:

OK
------------------------------


 

TroubleShooting

Client

Netstat

Netstat Command


netstat -an | find "SYN"

Netstat Output

netstat-syn-20170105-0217pm-cleanedup

Explanation

  1. Trying to connect to port 49154

 

Server

WireShark

WireShark Trace

reportingservices-sharedinterface-20170105-0339pm-clipped

 

Remediation

Windows Management Interface (WMI)

Specific Network Port Number

Netsh firewall add – Using netsh firewall

Code

winmgmt -standalonehost

net stop winmgmt /y

netsh firewall add portopening TCP 24158 WMIFixedPort

net start winmgmt /y

 

Output
Output – Textual
>winmgmt -standalonehost
Service configuration changes succeeded.

Please stop and restart Winmgmt service for changes to take effect.

>net stop winmgmt /y
The Windows Management Instrumentation service is stopping.
The Windows Management Instrumentation service was stopped successfully.


>netsh firewall add portopening TCP 24158 WMIFixedPort

IMPORTANT: Command executed successfully.
However, "netsh firewall" is deprecated;
use "netsh advfirewall firewall" instead.
For more information on using "netsh advfirewall firewall" commands
instead of "netsh firewall", see KB article 947709
at http://go.microsoft.com/fwlink/?linkid=121488 .

Ok.


>net start winmgmt /y
The Windows Management Instrumentation service is starting.
The Windows Management Instrumentation service was started successfully.


 

Output – Image

netsh-firewall-20170105-0246pm

Wireshark
Wireshark -01

wireshark-netshfw-20170105-0349pm-clipped

 

Wireshark -02

wireshark-netshfw-20170105-0416pm-clipped

 

Explanation
  1. Image-01
    • Cannot identify specific error messages
  2. Image-02
    • Upon waiting a little while, noticed errors
      • The red ones
        • Ephemeral Port +135

Netsh firewall add – Using netsh advfirewall

Goal

We need to rid ourselves of the warning message that reads “netsh firewall” is deprecated.

We will thus replace “netsh firewall” with “netsh advfirewall

Code

winmgmt -standalonehost

net stop winmgmt /y

rem Setting up a Remote WMI Connection
rem https://msdn.microsoft.com/en-us/library/aa822854(v=vs.85).aspx
rem netsh firewall add portopening TCP 24158 WMIFixedPort
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable=yes

net start winmgmt /y

Output
Output – Textual


>winmgmt -standalonehost
Service configuration changes succeeded.

Please stop and restart Winmgmt service for changes to take effect.

>net stop winmgmt /y
The Windows Management Instrumentation service is stopping.
The Windows Management Instrumentation service was stopped successfully.


>rem Setting up a Remote WMI Connection

>rem https://msdn.microsoft.com/en-us/library/aa822854(v=vs.85).aspx

>rem netsh firewall add portopening TCP 24158 WMIFixedPort

>netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable=yes

Updated 4 rule(s).
Ok.


>net start winmgmt /y
The Windows Management Instrumentation service is starting.
The Windows Management Instrumentation service was started successfully.



Output – Image

netsh-advfirewall-20170105-0256pm

 

Wireshark

reportingservices-20170105-0300pm-clipped

 

Explanation
  1. We are redded at 51840+135
    • The network adds up the ephemeral port (58140) to the DCOM Port (135)

 

Resource Monitor

Unfortunately, it appears that through the “netsh firewall” and “netsh advfirewall“, WMI will be using ephemeral ports.

To test this out, start a Reporting Services connection request…

Netstat  / find “SYN”

On connecting client, issue Netstat /find “SYN” request

netstat-syn-20170105-0431pm-cleaned-up

Explanation

We see that netstat is seeing SYN_SENT on port 50917.

 

Resource Monitor

On server, review svchost listening ports via “Resource Monitor”

resoucemonitor-svchost-20170105-0426pm

 

Explanation

We see that svchost (winmgmt) is waiting on 50917; and ephemeral port.

 

Set Specific Port for WMI

Explicitly Set Port for WMI Component

Using Component Services, let us designate a specific port for Windows Management (WMI)

  1. Launch Component Services
  2. Navigate to Windows Management
  3. Edit the component’s property
    • Set DCOM Endpoint
      • Options includes
        • Use default endpoints
          • Not the one we want ( in this case), as it is ephemeral
        • Use static endpoint
          • Yes, as want to be explicit that we rely on one opened via the Firewall
        • Internet & Intranet
          • Can be considered, if you have codified ones for all DCOM Components

componentservices-wmi-staticendpoint-usestaticendpoint

 

Restart WMI Services

net stop winmgmt /y

net start winmgmt

 

Resource Monitor ( 2nd Time )

Post WMI Service restart, the previously assigned ports for WMI goes away

resoucemonitor-svchost-20170105-0446pm

 

Re-initiate Reporting Services Connection via SSMS

SSMS

We connected

connected-20170105-0451pm

WireShark

wireshark-compsvc-20170105-0449pm-brushed-up

 

Cleanup Changes

If this is just a test, please take upon the following clean-up tasks

Revert


@echo off
winmgmt -sharedhost

net stop winmgmt /y

net start winmgmt /y

:complete
echo completed

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s