Reporting Services – SSL Certs

Objective

Our task is to secure Web\HTTP traffic.

Process

Request Certificate

We will not touch on the various pathways to request a certificate and leave that for another post.

 

Receive Certificate

Certificates are precious and so maintain custody during request and receipt.

Secure File

Here we receive it through secured email…

BTW, the file type in this case is PFX.

 

Register Certificate

Microsoft Management Console ( MMC )

Prepare MMC for Certificate.msc

Initiate mmc.exe

Initiate MMC by running mmc.exe

Add/Remove snap-in…

Use menu items “File” “Add/Remove Snap-in …“.

From the “Available snap-ins“, please choose “Certificates“.

 

Add/Remove snap-ins – Selected snap-ins ( Certificate )

Here is what the screen looks like upon clicking the Add> button and adding the “Certificates” snapin.

 

Certificate Snap-in

Here we choose the “Computer account” as the targeted account.

 

Using MMC – Import Certificate

Computer – Personal Store
Menu

Access the “Console Root” “Certificates (Local Computer)” Personal Certificates node.

Right click the Selected Node and from the drop down menu, choose “All Tasks” “Import…”

 

Certificate Import Wizard – Welcome to the Certificate Import Wizard

The “Certificate Import Wizard” window opens up…

Explanation
  1. Store Location
    • Local Machine
Certificate Import Wizard – File To Import

The “File to Import” window appears

Please click the Browse button…

 

Certificate Import Wizard – File To Import – Open Dialog – File Type – X.509 Certificate

The “File Open” window opens.

The default file type is “X.509 Certificate (*.cer, *.crt)

 

Certificate Import Wizard – File To Import – Open Dialog – File Type – Personal Information Exchange ( PFX )

In our case, we have a PFX file.

And, so we chose “Personal Information Exchange (*.pfx)

Certificate Import Wizard – File To Import – Specify File To Import

Confirm the filename.

And, click the Next button to complete the Certificate Import.

 

Review Certificates in Personal Store
List Certificates

Access “Console Root” \ “Certificates ( Local Computer )” \ Personal \ Certificates and review the certificates.

 

Review Certificate

Please review the imported certificate, the important areas:

  1. Issued By
    • This is the certificate issuer
  2. Expiration Date
    • The certificate’s Expiration Date
  3. Intended Purpose
    • The certificate intended purpose
      • Want to be sure that the following are included
        • Server
Review Certificate – In Depth

Select the certificate and doubleclick on it.

Outline

Please review the various tabs:

  1. General
  2. Details
  3. Certificate Path
General

Details

Certificate Path

 

Reporting Services Configuration Manager

Launch SQL Server Configuration Manager

From Windows desktop, perform a Search for SQL Server applications.

To do so initiate Search, and enter “sql server

 

SQL Server Configuration Manager Connect

The first step is to choose the server and Reporting Server Instance to connect to…

 

Reporting Services Configuration Manager – Web Service URL

Original

Advanced Multiple Web Site Configuration

Access the “Advanced Multiple Web Site Configuration“, by clicking the “Advanced” button.

Advanced Multiple Web Site Configuration – Before

 

Add a Report Server SSL Binding
Add a Report Server SSL Binding – Initial

Add a Report Server SSL Binding – List of Certificates – Initial

Restart Report Server

If the certificate is not shown in the list of certificates, please restart the Report Server as detailed here:

Configure SSL Connections on a Native Mode Report Server
Link

Expand the list of SSL Certificates. Reporting Services detects server authentication certificates in the local store. If you installed a certificate and you do not see it in the list, you might need to restart the service. You can use the Stop and Start buttons on the Report Server Status page in the Reporting Services Configuration tool to restart the service.

 

Add a Report Server SSL Binding – List of Certificates – After

Please exit the “Edit a Report Server SSL Binding” window, and come back and choose the target certificate.

We chose “(All IPv4)” and click OK.

And, repeat for “(All IPv6)“.

 

Reporting Services Configuration Manager – Report Manager URL

Once the Web Services configuration is done, please choose “Report Manager URL”.

Original

 

Process

Click the “Advanced” button and follow identical steps to the ones we took for “Web Services”.

 

Revised

 

Validate

Outline

  1. Launch Web Browser
  2. Enter URL, please be sure to use https and not http
  3. Access the Page’s property
  4. Review the Certificate

 

Steps

Launch Web Browser – Using https

Launch a browser and enter the URL.

In our case we entered https://RS.labdom.org/Reports

 

Web Page Property

On Internet Explorer (IE), right click on an empty spot on the page and choose Properties from the drop down menu.

 

 

Review Certificate

 

 

References

  1. William R. Vaughn and Peter Blackburn
    • Installing and Configuring SQL Server Reporting Services
      Link
  2. Microsoft Docs
    • Docs > SQL > SQL Server Reporting Services > Report server web service > Net framework
      • Using Secure Web Service Methods
        Link
    • Docs > SQL> SQL Server > Reporting Services > Security
      • Configure SSL Connections on a Native Mode Report Server
        Link
    • Docs > SQL > SQL Server > Reporting Services > Report server
      • RsReportServer.config Configuration File
        Link
  3. Microsoft | Developer
    • Team Foundation Server – Setup, Administration and Operations Blog
      • TF255455: SQL Server Reporting Services is configured to require a secure connection.
        However, no HTTPS URL is configured with a valid certificate
        Link

SQL Server – Reporting Services – Provision Users

Background

We have a farm of Web Servers running Microsoft IIS and they need to connect to a Microsoft SQL Server Reporting Services and serve reports.

 

Provisioning

Security

Goal

One of the areas that we need to cover is to ensure that the Active Directory Accounts that the Web Site is running under has the right security privileges to consume reports from the RS Server.

And, so took a survey of all the Application Pools and it is easy to go in and add each account.

But, it will take time to do so in all of our environments ( Dev, Test, QA, and Production ).

 

Script

As always looked for scripts on the Net.

Bryan Keller, MSFT

Found one written by Bryan Keller way back in 2004.

Sample Script for Setting Item-level Security in Reporting Services
Link

 

Modifications

Made a few modifications and here is what we came up with

addItemSecurity.rss
'=====================================================================

'  File:     AddItemSecurity.rss

'

'  Summary:  Demonstrates a script that can be used with RS.exe to

'                set security on an item in Reporting Services.

'

'

' THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY

' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

' PARTICULAR PURPOSE.

'=====================================================================*/

'

' Variables that are passed on the command line with the -v switch:

' userName the name of the user for which to add a policy

' roleName the name of the role to apply for the user (i.e. Browser, Content Manager)

' itemPath the path of the item for which you want to add the policy (i.e. /SampleReports)

' keepCurrentPolicy whether to keep the current policy and add the new one

'

' Sample command line:

' rs -i AddItemSecurity.rss -s http://localhost/reportserver -v userName=MyTestUser

'    -v roleName=Browser -v itemPath=/SampleReports -v keepCurrentPolicy=True

rem Const debug as Boolean = True 
Const debug as Boolean = False
Const CHAR_TAB As String = Microsoft.VisualBasic.vbTab

Dim CHAR_SEPARATOR As String = Microsoft.VisualBasic.StrDup(80, "*")
Dim logMessage as String
Dim colorSave  as System.ConsoleColor

Public Sub Main()

   Dim isRoot As Boolean = False

   Dim inheritParent As Boolean

   Dim policies() As Policy

   Dim newPolicies() As Policy

   Dim policy As New Policy()

   Dim roles(0) As Role
   
   Try

	   roles(0) = New Role()

	   roles(0).Name = roleName

	   policy.Roles = roles

	   policy.GroupUserName = userName

	  

	   While Not isRoot

		  ' Once the root of the catalog is reached,

		  ' stop applying policies

		  If itemPath = "/" Then

			 isRoot = True

		  End If

		  policies = rs.GetPolicies(itemPath, inheritParent)

			

		  ' If the user selects not to keep inherited or current policy,

		  ' empty the policy

		  If Not keepCurrentPolicy = "True" Then

			 policies = Nothing

		  End If

		  if (debug) then Console.WriteLine("Calling AddNewPolicy.")
		  
		  newPolicies = AddNewPolicy(policy, policies)
		  
		  if (debug) then Console.WriteLine("Calling SetPolicies.")

		  rs.SetPolicies(itemPath, newPolicies)
		  
		  if (debug) then Console.WriteLine("Calling GetParentPath" & itemPath)

		  itemPath = GetParentPath(itemPath)

	   End While

	   Console.WriteLine("Policy successfully set.")
   
   CATCH ex As Exception
   
		colorSave = Console.ForegroundColor
		
		Console.ForegroundColor = ConsoleColor.Red
   
		Console.WriteLine(CHAR_TAB & "**************************************************")
   
		Console.WriteLine(CHAR_TAB & "Exception Type    :" & CSTR(ex.GetType().ToString()) )
   
        Console.WriteLine(CHAR_TAB & "Exception Message :" & ex.Message)
		
		Console.WriteLine(CHAR_TAB & "**************************************************")		
   
		'THROW ex
		
		Console.ForegroundColor = colorSave
		
   END TRY

End Sub 'Main

  

 

' Method to parse the path of an item and retrieve

' the parent path of an item

Private Function GetParentPath(currentPath As String) As String

   Dim delimiter As String = "/"

   Dim rx As New System.Text.RegularExpressions.Regex(delimiter)

   Dim childPath As String() = rx.Split(currentPath)

   Dim parentLength As Integer
   
   parentLength  = childPath.Length - 1

   Dim parentPath(parentLength) As String

   Dim i As Integer

   For i = 0 To parentLength - 1

      parentPath(i) = childPath(i)

   Next i

   If parentPath.Length = 1 Then


	  Return "/"

   Else

	  Return String.Join("/", parentPath)

   End If

End Function 'GetParentPath

  

' Takes the policy to add and applies it to the current set

' of policies if applicable

Private Function AddNewPolicy(policyToAdd As Policy, policies() As Policy) As Policy()

   Dim list As New System.Collections.ArrayList(policies)
   Dim policy As Policy   

   If Not (policies Is Nothing) Then

      For Each policy In  policies

         If policy.GroupUserName = policyToAdd.GroupUserName Then

            Throw New Exception("The supplied User policy already exists for the item")

         End If

      Next policy


      list.Add(policyToAdd)

      Return CType(list.ToArray(GetType(Policy)), Policy())

   Else

      policies = New Policy(0) {}

      policies(0) = policyToAdd

      Return policies

   End If

End Function 'AddNewPolicy

 

applyToDev.cmd

@echo off 
setlocal enableextensions enabledelayedexpansion


set _server=DEVWS01
set _serverURL=http://%_server%/ReportServer

echo Applying AD Accounts against server %_server%

for /f %%s in ( ADUsersDemo.txt ) do (
	
	set _user=%%s
	set _ADAccount=!_user!

	echo Processing AD Account !_ADAccount!

	rs -i AddItemSecurity.rss -s %_serverURL% -v userName=!_ADAccount! -v roleName="Browser" -v itemPath="/" -v keepCurrentPolicy="True"
)

endlocal

ADUsersDemo.txt
LABDOM\WSDEMO01$
LABDOM\WSDEMO02$

Source Control

GitHub

Placed in Github here

 

Listening

Listening to a little Liyah

SQL Server – Error – “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.sysowners’ and the index name ‘nc1’. The duplicate key value”

 

Background

Trying to add Reporting Services to an existing SQL Server v2014 Install this afternoon, and finally ran into a problem that is worthy of talking about.

 

Steps

We are on MS SQL Server 2014 Standard Edition

  1. Added Reporting Services to an existing install
  2. And, now going through the Change Database Steps
    • Specifically, the Progress and Finish Step
    • While processing the SQL Code within the “Running database script” step, we ran into an Error

 

reportdatabasedatabaseconfigured-progressandfinish-20170130-0140pm

 

 

Error

Here is the error message

Error Image

exceptiondetails

Error Text

 


System.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.sysowners' and the index name 'nc1'. The duplicate key value is (spotlight).
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
   at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)

 

TroubleShooting

SQL Server Profiler

Ran SQL Server Profiler and captured the error pasted below:

Trace

Image

sqlserverprofiler-20170130-0154pm-brushed-up

 

Textual

The create unique Index Statement terminated because a duplicate key was found for the object ‘dbo.sysowners’ and the index named ‘nc1’.  
The duplicate key value is (spotlight).

 

msdb

Knowing that msdb is the foundational database took a look at it.

sysusers

Code


use [msdb]
go

select *

from   sysusers tblSU

--where  lower(tblSU.[name]) like '%spot%'

order by 
		lower(tblSU.[name])


 

Output

sys-sysusers-20170130-0321pm

 

Explanation

  1. In the msdb database
    • We have two distinct users Spotlight and spotlight
    • This is possible as out server collation

 

Validate Server Collation

Code


print 'Collation: ' + cast( serverproperty('collation') as varchar(60))

Output

servercollation-20170130-0324pm

 

Validate Database ( msdb ) Collation

Code


print 'Collation - SQL Instance: ' + cast( serverproperty('collation') as varchar(60))

print 'Collation - Database - msdb - ' + CONVERT (varchar, DATABASEPROPERTYEX('msdb','collation'));  

print 'Collation - Database - ReportServer - ' + CONVERT (varchar, DATABASEPROPERTYEX('ReportServer','collation'));  

print 'Collation - Database - ReportServerTempdb - ' + CONVERT (varchar, DATABASEPROPERTYEX('ReportServerTempDB','collation'));  


Output

collation-view-20170130-0330pm

 

Explanation

  1. On our embryonic database, msdb, it is OK to have users whose name only vary based on case
    • BTW, for this server whose collation is binary, case materializes
  2. But, on new to become database which is case-insensitive, it is not OK

 

 

Listening

To me it is a cute problem, but don’t ask my lover

As, she is the only one who makes herself pretty for me

Bellamy Brothers
Let Your Love Flow
Link

Microsoft Connect

Opened up a Connect Item

  1. Title :- Reporting Services – Configuration – Index Creation error on table dbo.sysowners – The index name nc1
    ID :- 3120021
    Date Created :- 2107-Jan-30th
    Status :- Active
    Link

SQL Server – Reporting Services – Invoking from .Net Application – Permission Errors

Background

We have provisioned Microsoft SQL Server Reporting Services and now testing it out by having a .Net Application connect to it, ask for a report to be ran and consume the report.

 

Errors

There are two sets of errors that we will look at.

We will like at both the errors that are exposed on the .Net Client code and the ones logged on the server.

 

.Net Client Error

  1. The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later

 

Reporting Services Back End Error

There are a couple of growing pains that we are experiencing and those are:

  1. Execute permission denied on the Object
  2. Permissions granted to user are insufficient for performing the operation

 

Indepth

Error – Execute Permission on Stored Procedure

Image

reportserverservice_log_20170124_0934_log

Tabulated

Error
webserver!ReportServer_0-5!20f0!01/24/2017-12:48:37:: i INFO: Processed folder ‘/ASSIST’
processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘InstitutionList’. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘rptInstitutionList’, database ‘AssistDW’, schema ‘dbo’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
 processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: e ERROR: An exception has occurred in data set ‘InstitutionList’. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘InstitutionList’. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘rptInstitutionList’, database ‘AssistDW’, schema ‘dbo’.
 processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources …
processing!ReportServer_0-5!20f0!01/24/2017-12:48:42:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘InstitutionList’. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘rptInstitutionList’, database ‘AssistDW’, schema ‘dbo’.

 

 

Resolution

Grant select and execute permission on the specific Objects or Schema.

Sample

use [AssistDW]; GRANT EXECUTE on  SCHEMA :: [dbo] to [LAB\svcMarketing];
use [AssistDW]; GRANT SELECT  on  SCHEMA :: [dbo] to  [LAB\svcMarketing];

Error – Call to GetSystemPropertiesAction() :: Permissions granted to user ‘WebPoolUsr‘ are insufficient for performing this operation.

Tabulated

Tabulated

Error
library!ReportServer_0-11!263c!01/25/2017-12:24:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: , Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ‘LAB\WebAppPool’ are insufficient for performing this operation.;

 

 

Image

reportserverservice_log_accessdeniedexception__20170124_0156pm_log

 

In our case, we granted permission at the Report Manager Home Level, but it was broken at the specific folder level

Broken Permission

Only the BUILTIN\Administrators have permission at the ASSIST Folder Level

roleassignment-folder-assist-20170125-1242pm

 

Revert to Parent Security

We chose to revert to the Parent Security.

Previously, we validated that things was good at the Parent Level.

roleassignment-folder-assist-reverttoparent-20170125-1243pm

After Reverting

roleassignment-folder-assist-afterrevert-20170125-1243m

 

Google Drive – Google Sheets – Pasting Tables

 

Background

We all take for granted how seamlessly Microsoft Products work together.

Take for instance, I use Microsoft SQL Server Management Studio to run a query and I will like to prepare a document from the query’s result.

I simply copy the Output Grid’s content, launch Microsoft Word or Excel, and pasted it.

Through the magic of OLE or whatever they call it these days, the data is well received and formatted in the receiving Office App.

 

Google Drive

These days my main sharing tool is WordPress for public consumption and Google Docs for private data.

 

Here is the genesis of our Problem

SQL Server Management Studio ( SSMS )

Grid

Here is output of a query I ran in SSSM

ssms

Explanation

  1. Nice and visually appealing for an Engineer

 

Google Drive – Google Docs

Here is what things look like when pasted into Google Docs..

copiedintogoogledocs-20170120-1031am

 

What to do

  1. Looked for Convert to Table
  2. Insert Text as table
  3. Import Text

 

Nothing helpful.

 

Solution – 01

Google Drive – Google Sheets

Created a new file, rather than Document went with Sheets

copiedintogooglesheets-2017012-1039am

 

Explanation

  1. Making progress
    • Kept the grid or columns paradigm
    • That is things are not jumbled well, with text intertwined together

 

Google Drive – Copy From Google Sheets Into Google Docs

Here we copied the cells from Google Sheets Into our original Google Docs

copiedfromgooglesheetintodocs-20170120-1044am

 

Explanation

  1. Thankfully things are kept neatly arranged in a Columns
  2. Need to get rid of some extra columns and that is doable

 

Google Drive – Google Docs – Table – Delete Extra Columns

Got rid of the extra columns using the menu item Table / Delete Columns.

Steps

  1. Inside the Google Doc
  2. Select the extra columns
  3. And, use menu item Table / Delete Columns

 

googledocs-columnsdeleted-20170120-1053am

Format the Table

One of the great things about these Google Products such as Chrome and Google Drive is that they are extensible and have such a nice and rich 3rd party ecosystem.

 

Adds On

Table Formatter

I already have Table Formatted installed and so let us initiate it by accessing Google Docs menu items “Add-ons” \ “Table Formatter”.

 

Google Docs – Menu – Add-Ons

addson

 

Add-On – Table Formatter – Default Templates

Here are some of the Default Templates available

tableformatter-20170120-1057am

Customize Table with Add-On – Table Formatter

Select the Google Docs’s tale and choose the one of the Formatting Choices.

Here is our colored layout.

googledocs-tableformatted-20170120-1103am

 

Summary

  1. Copy SSMS Grid Data into Clipboard
  2. Create a new Google Sheet ou use existing one
    • Paste copied into Sheet
  3. Create a new Google Doc
    • Select data from Google Sheet
    • Copy into Clipboard
    • And, paste into Google Doc
  4. In Google Docs
    • Using 3rd Party Add Ons such as Table Formatter format Table

Solution – 02

Outline

  1. Copy SSMS Grid output into Clipboard
  2. Use Microsoft Excel
    • Launch Microsoft Excel
    • Copy Grid’s data into Excel
    • If you like the way Excel Formats Table, use Excel’s Table Formatting functionality
      • Using F8, make Sheet’s column into actual table
        • Be sure you have Column Headers and all
  3. Create or use New Google Docs
    • Copy Table’s content from MS Excel
    • Paste into Google Doc

 

In MS SSMS, Copying with Headers

ssms-copywithheaders-20170120-1126am

Explanation

 

In MS Excel, Pasted SSMS Grid

excel-pastedgrid-20170120-115am

 

In MS Excel, Create Table

Here is the panel displayed upon clicking on F8 and thus initiating the “Create Table” options

createtable-20170120-1119am

Explanation

  1. Please pay attention to the “My table has headers” option

 

In MS Excel, Formatted Table

excel-createtabled-20170120-1120am

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

 

 

SQL Server – Reporting Services–Configuration- Backend DB Instance Change

Preface

If you end up having to change the computer name that backends your Reporting Services (RS), you will have to let your RS know about it.

 

Steps

Configuration Files

Unfortunately, you have to do more than hand change the configuration files.

The reason being the fact that your Database Connection settings are encrypted.

One might wonder why the secrecy, and MSFT might have one think through the fact that two types of authentication are supported.

And, those are Integrated Authenthication and the SQL Native authentication.

SQL Native Authentication relies on clear text username and passwords.

 

 

rsreportserver.config

rsreportserver-config-20161213-0637PM

 

Explanation

  1. DSN
    • The element that contains the backend database is appropriately name DSN, Datasource Name
    • As aforementioned it is encrypted
  2. URL Reservation
    • There are a couple of other elements to tend to and those have to do with the HTTP Endpoints
      • There are two important end points and those are ReportServeWebService and ReportManager
      • From the screenshot above we can see that they are both listening on the local host’s port 80
      • There IIS Application names are also shown

 

 

Reporting Services Configuration

 

  1. Launch Reporting Services Configuration Manager
  2. Navigate to the Database tab
    • Click on the Change Database button
  3. The “Report Server Coniguration Wizard” appears

 

Database

RSConfig-Database-brushedup-20161213-0625PM

 

 

Report Server Configuration Wizard

Action

The Action tab appears….

RSConfig-Database-ChangeDatabase-20161213-0626PM

 

Please choose the “Choose an existing report server database”.

 

Database Server

RSConfig-Database-ConnectToDatabase (brushed up)-20161213-0638PM

 

Please replace the former computer name with the revised name.

And, be sure to validate your connection by clicking on the “Test Connection” button.

 

Database

 

SelectAReportServerDatabase-brushedup-20161213-0639PM

 

Please be sure to transverse to the appropriate Report Server database; by default the name of that database will be ReportServer.

 

Credentials

Credentials-20161213-0640PM

 

In our case, we kept everything as is.

 

Summary

Summary-brushedup-20161214-0627AM

 

The summary screen offers you the last opportunity to review your selections.

Please pay particular attention to your “SQL Server Instance” and “Report Server Database”.

 

Progress and Finish

Next comes the actual processing of your request…

ProgressAndFinish

 

You will like to see that all tasks came back successful.