WMI – Reporting Services Configuration Manager – Event Viewer

Background

When troubleshooting Reporting Services Configuration Manager WMI calls it can be useful to see whether WMI activities are occurring.

 

TroubleShooting

Event Viewer

Enable Log

WMI calls are logable in the Event Viewer.

But, they are not logged by default.

To enable logging please follow the steps listed below:

Obtaining WMI Events Through Event Viewer
Link

  1. Open Event Viewer. On the View menu, click Show Analytic and Debug Logs
  2. Locate the Trace channel log for WMI under Applications and Service Logs | Microsoft | Windows | WMI Activity
  3. Right-click the Trace log and select Log Properties. Click the Enable Logging check box to start the WMI event tracing
  4. WMI events appear in the event window for WMI-Activity. Double-click an event in the list to see the detailed information. You can view an event in XML View or in Friendly View format.

Logged Events

Tabulate

# Event
1 GroupOperationId = 159602; OperationId = 159602; Operation = IWbemServices::Connect; ClientMachine = QADB; User = dadeniji.adeniji; ClientProcessId = 5024; NamespaceName = \\QA\root\Microsoft\SqlServer\ReportServer
2 GroupOperationId = 159603; OperationId = 159604; Operation = Start IWbemServices::CreateInstanceEnum – __NAMESPACE; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer
3 GroupOperationId = 159605; OperationId = 159605; Operation = IWbemServices::Connect; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\QADB\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
4 GroupOperationId = 159606; OperationId = 159607; Operation = Start IWbemServices::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
5 ProviderInfo for GroupOperationId = 159606; Operation = Provider::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ProviderName = ReportingServicesWMIProvider; ProviderGuid = {0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF}; Path = C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Explanation

  1. Event #1 :- IWebServices Create Instance Enum
    • NamespaceName :- \\QA\root\Microsoft\SqlServer\ReportServer
  2. Event #2 :- IWebServices Create Instance Enum
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer
  3. Event #3 :- IWebServices Connect
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  4. Event #4 :- IWebServices Instance Enumerate
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  5. Event #5 :- Com Object Instantiated
    • COM Object ID :- 0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF
    • COM File :- C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Summary

From logging WMI Calls we are able to see the inner workings of the WMI Provider class and IWbemServices interface.

In the case of Sql Server Reporting Services (SSRS) it is an version specific dll ( C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll ).

SQL Server Reporting Services (SSRS) – Error – “Invalid Class”

Background

As part of a much larger work done that we are undertaken, got swallowed into a Windows Management Instrumentation ( WMI ) quicksand.

 

Manifestation

The errors stands up upon launching SQL Server Reporting Services ( SSRS ) and trying to connect to one of the local instances.

 

Error

Report Services Configuration Connection

Launch

Image

SSRS_Connect_20170816_0744PM

 

Explanation

  1. Report Server Instance dropdown
    • empty & greyed out
  2. Connect button
    • disabled

Find

Upon clicking the find button

Image

InvalidClass_20170816_0307PM

 

Explanation

  1. When we clicked the find button to “discover” SSRS Instances on the entered host, we received the error message “Invalid class

 

Troubleshooting

 

WMI Explorer

WMI Explorer allows us to simulate the same WMI queries made by the Application.

 

Clarification

  1. We have 2 SQL Server Instances on the box
    • A named instance, Datacap
    • A default instance
  2. Instances
    • Instance – Datacap
      • Upgrade History :- Recently upgraded
      • Edition :- Enterprise Edition :- Core-Based Licensing
    • Instance – Default
      • Upgrade History – Left as is

 

Images

The screen shot below is what we captured as we navigated the Namespace.

 

Version – DATACAP – v10

 

wmiExplorer_DataCap_v10__20170816_0711PM

Explanation
  1. Tab :- Instances ( 0 )

 

Version – DATACAP – v12

wmiExplorer_DataCap_20170816_0705PM

 

Explanation
  1. Tab :- Instances ( 1 )
    • Instances :- MSReportServer_Instance.Instance Name=’DATACAP’
    • Edition :- Enterprise Edition : Core-Base Licensing
    • InstanceID  :- MSR12.DATACAP
    • IsSharePointIntegrated :- False

 

Version – DEFAULT – v10

wmiExplorer_DefaultInstance_v10DOT15_20170816_0711PM [BrushedUp]

Explanation
  1. Tab :- Instances ( 1 )
    • Instances :- MSReportServer_Instance.Instance Name=’MSSQLSERVER’
    • Edition :- Enterprise Edition
    • InstanceID  :- MSR10_15.MSSQLSERVER
    • IsSharePointIntegrated :- False

 

Summary

Admittedly it is difficult to fully tell of our experiences from a single tool along with a couple of screenshots.

Yet in summary it seems Report Services Configuration Manager is crippled due to one missing instance.

Later we will come back and talk about other diagnostics tools one can use to debug WMI difficulties.

SSRS – Validating Smart Host – Using Mozilla Thunderbird

Background

A few posts ago, we provisioned a Smart Host.

Let us quickly test it out.

 

Lineage

Here are other posts in this multi-series:

  1. SSRS – Setting up Smart Host
    Link

Scenario

To make sure that we understand the specificity of Microsoft’s Office 365 SMTP service, we will install a free, durable email client; specifically Mozilla Thunderbird.

 

Wireshark

Filter

  1. tcp.port == 25 || tcp.port == 465 || tcp.port == 587

Mozilla Thunderbird

Download

Download Mozilla Thunderbird from here.

Installation

Installation is straightforward.

Configuration

Images

Session – 1

Configuration
SMTP Server Settings

 

Output
Netstat

WireShark

 

Session – 2

Configuration
SMTP Server Settings

Output
Netstat

WireShark

WireShark – SMTP ( Destination Port 587 ) – 01
WireShark – Explanation
  1. Connection to SMTP Host
WireShark – SMTP ( Destination Port 587 ) – 02

WireShark – Explanation
  1. Reply from SMTP Host

 

Session – 3

Configuration
SMTP Server Settings

WireShark

WireShark – Explanation
  1. We see a fuller conversation

 

Item Port :- 465 /
Connection :- SSL/TLS
Port :- 567 /
Connection Start :- SSL/TLS
Port :- 567 /
Connection Start :- STARTSSL
Server Name smtp.office365.com smtp.office365.com smtp.office365.com
Port 465 587 587
Connection Security SSL/TLS SSL/TLS STARTSSL
Authentication Password Normal Password Normal Password Normal Password
Results SYN/SENT  Truncated Conversation  Full Conversation

 

Network Ports

  1. Port :- 25
    • SMTP
      • Server to Server email
        • Message relay port
      • (-)
        • Not Authenticated
        • Blocked by a lot of firewall
  2. Port :- 465
    • SMTP
      • (-)
        • Not widely adopted
  3. Port 587
    • SMTP
      • Mail Submission Port
      •  (+)
        • Authenticated emails

 

References

  1. Microsoft
    • Office
      • Support
        • POP and IMAP settings for Outlook Office 365 for business
          Link
        • How to set up a multifunction device or application to send email using Office 365
          Link
    • technet
      • blogs.technet.com
        • Andrew Stobart – Useful Wireshark Filters for Mail Flow Troubleshooting
          Link
  2. Mozilla.Org
    • Mozilla Support
      • cannot send mail. Connected to smtp.office365.com but times out.
        Link
  3. Stanford University
    • Stanford | University IT
      • How to Configure Thunderbird for Office 365 Using IMAP
        Link
  4. StackOverflow
    • Network Ports
      • What is the difference between ports 465 and 587?
        Link
  5. JSCAPE
    • Managed File Transfer and Network Solutions
      • John Carl Villanueva
        • Still Confused With SMTP Ports? Read This
          Link
  6. FastMail
    • SSL vs TLS vs STARTTLS
      Link

 

SSRS – Setting up Smart Host

 

Background

A year or so ago we setup subscriptions to a couple of Reports that we are providing through SQL Server Reporting Services.

The subscriptions go out through daily email.

Ever so often things just break.

I was recently informed that Emails have not been going for over a week now.

Last time I blamed it on other processes that are using that same host.

Hoping today I can do same and go on about my business.

But, no such luck.

 

Environment

Here is our topology

  1. Reporting Services
    • Reporting Services is running on a local server in our intranet.
  2. Database Server
    • Database Server is running in our Colocation’s Data Center
  3. Email Server
    • The email server is Microsoft’s Office365.com

 

Troubleshooting

Thinking out loud

As always don’t have a clue what changed.

Could it be…

  1. Tightened Security
    • Can emails only go out from certain hosts
    • Do I need an actual username and password combination
    • Firewall
      • Local
        • Is it Windows Firewall
      • Corporate
        • Is it a Corporate Firewall
    • Is it Antivirus Configuration

 

Remediation

Proposal

Not sure what is getting in the way of SSRS getting the emails out.

But, a likely workaround is use a local functional SMTP server as a bridge.

 

Local SMTP Server

Installation

Launch “Server Manager” and we will choose to add “SMTP Server Tools” as a Feature.

 

Step

  1. Tab – Features
    • If “SMTP Server” feature is not checked, please place a check mark next to it
    • Dependencies
      • The “Add role services and features required for SMTP Server” window appear
        • The features listed are “Web Server (IIS)” and “Remote Server Administrative Tools”
  2. Tab – Web Server ( IIS )
    • Shows Web Server literature
  3. Tab – Confirmation
    • Confirmation that IIS and Remote Server Administrative Tools will be augmented
  4. Tab – Progress
    • As installation is proceeding each step is chronicled
  5. Tab – Results
    • The status of each component installed is noted

Images

Add Features Wizard – Select Features
Initial Screen

Before Adding “SMTP Server….

SelectFeatures_SMTP_201708087_0420PM

 

Post Checking “SMTP Server”

Adding “SMTP Server “….

SelectFeatures_SMTP_201708087_0421PM

Add Features Wizard – Add role services and features required for SMTP Server?

Dependencies are listed.

And, they include Web Server ( IIS ) and Remote Server Administrator Tools.

SelectFeatures_AddFeaturesWizard_201708087_0420PM

 

Web Server ( IIS)

Components :-

  1. Internet Information Services ( IIS ) 7.0
    • ASP.Net
    • Windows Communication Foundation

 SelectFeatures_SMTP_WebServer_IIS_201708087_0421PM

 

Confirm Installation Selections

Confirm Installation.

In our case:

  1. Web Server ( IIS )
    • Health and Diagnostics
      • ODBC Logging
    • Remote Server Administrator Tools
      • SMTP Server Tools

 

SelectFeatures_SMTP_WebServer_IIS_RoleServices_Confirmation_201708087_0423PM

Installation Progress

Installation is progress…

 

SelectFeatures_SMTP_InstallationProgress_201708087_0424PM

 

 

Installation Results

Installation Succeeded.

SelectFeatures_SMTP_WebServer_IIS_RoleServices_InstallationResults_201708087_0435PM

 

 

Configuration

Customization

  1. Tab – General
    • Enable Logging
      • It is most useful to turn on logging during initial setup and follow-up troubleshooting sessions
  2. Tab – Access
    • Group – Connection
      • Select which computers may access this session
        • All, except the list below
    • Group – Relay Restrictions
      • Only the list below
        • Self ( for now )
          • 127.0.0.1
  3. Tab – Messages
    • Send copy of non-delivery report to
      • Mail Administrator
        • Hopefully a monitored distribution list
    • Bad mail directory
      • Default
        • C:\Bad Mail
      • Non-system drive folder
        • Hopefully, you take the opportunity to change the folder to a non-system drive
  4. Tab – Delivery
    • Group box – Outbound Security
      • Authentication Choices
        • Anonymous
        • Basic Authentication
        • Windows Integration
      • In our case :-
        • Anonymous ( NO )
          • Are trying to get away from Anonymous as our hosting platform, Microsoft Office, requires user authentication
        • Integrated Windows Authentication ( NO )
          • We do not have cross-domain relationship between us and Microsoft’s Hosted Outlook
        • Basic Authentication ( YES )
      • TLS
        • We enabled TLS
    • Group box – Outbound Connections
      • TCP Port
        • 587
          • This is the default mail submission port. When a mail client or server is submitting an email to be routed by a proper mail server, it should always use this port.
            Unless you’re explicitly blocked by your upstream network or hosting provider.
            This port, coupled with TLS encryption, will ensure that email is submitted securely and following the guidelines set out by the IETF”

            John Carl Villanueva ( Link )
    • Group box – Advanced Delivery
      • Fully Qualified Domain Name
        • Especially for domains that have SPFs set up
      • Smart Host
        • smtp.office365.com
      • Attempt direct delivery before sending to smart host
        • Unchecked
      • Perform reverse DNS lookup on incoming messages
        • Unchecked
  5. LDAP Routing
    • Not going to need to use LDAP Routing for user authentication
  6. Grant Operator permissions to these Windows User Accounts

 

Screenshot

SMTP Virtual Server – Properties – General

General_20170808_0840AM

 

 

SMTP Virtual Server – Properties – Access

Access_20170807_0752PM

 

SMTP Virtual Server – Properties – Access – Connection
Initial

Access_ConnectionControl_20170807_0753PM

 

SMTP Virtual Server – Properties – Access – Relay Restrictions
Initial

Access_RelayRestrictions_20170807_0754PM

 

Add Computer

List

  1. Single Computer
    • IP address:- 127.0.0.1

 

Access_RelayRestrictions_AddComputer_20170807_0755PM

Completed

Access_RelayRestrictions_Computer_20170809_0114PM

 

SMTP Virtual Server – Properties – Messages
Initial

Messages_20170807_0756PM

 

Completed

Messages_20170807_0757PM (BrushedUp)

 

 

SMTP Virtual Server – Properties – Delivery
Initial

Delivery_20170807_0757PM

 

SMTP Virtual Server – Properties – Delivery – Outbound Security
Initial

Delivery_OutboundConnections_20170807_0444PM

 

 

Complete

Delivery_OutboundSecurity_20170807_0758PM (BrushedUp)

 

SMTP Virtual Server – Properties – Delivery – Outbound Connections
Initial

Delivery_OutboundConnections_20170807_0445PM

 

Completed

Delivery_OutboundConnections_20170807_0758PM

 

 

SMTP Virtual Server – Properties – Delivery – Advanced Delivery
Initial
Completed

AdvancedDelivery_20170807_0759PM [BrushedUp]

 

Conclusion

We have an SMTP Server setup.

We will come back and unit test it out and once verified, we will point Sql Server Reporting Services ( SSRS ) to route emails through it.

 

References

  1. jscape
    • John Carl Villanueva
      • Still Confused With SMTP Ports? Read This
        Link

SQL Server – Reporting Services – Connecting Locally – Day 1

Background

Hardening security via applying SSL Certs on a couple of Reporting Services Hosts and wanting to test them on same host, but “No Go“.

 

TroubleShooting

Windows Event Viewer

Checked Windows Event Viewer

Security

Security – Headers

Image

Tabulate
  1. Event ID = 4625
    • Keywords :- Audit Failure
    • Source :- Microsoft Windows Security auditing
    • Event ID :- 4625
    • Task Category :- Logon

Security – Details

Image

 

Tabulate
  1. Event ID = 4625
    • Security ID :- NULL SID
    • Logon Type :- 3
      • Logon Type 3 is Network
    • Status :- 0xC000006D
    • Event ID :- 4625
    • Task Category :- Logon

Summary

Basically, we were prompted thrice to enter our username and password. But, unable to connect.

 

 

Internet Explorer

Checked to make that we are still unable to connect when we run in Administrator Mode.

Task Manager

To verify that IE is running in Administrator mode launched Task Manager and included the “Elevated” attribute.

Select Columns

Results

Image

Explanation

For each IE Session, we are seeing two processes.
Why two processes each time we start a new IE Session?

 

Remediation

Registry

Outline

There are a couple of options and those are:

  1. BackConnectionHostNames
  2. DisableLoopbackCheck

 

BackConnectionHostNames

Worknotes

Launch regedit and access the registry key “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0”.

Search for BackConnectionHostNames

Add all FQDN that the server’s resource will be self referred to.
Each entry should be entered in its own line.

  1. Type :- REG_MULTI_SZ
  2. Data :- ????

Images

Adding Entry

Entry Added

 

DisableLoopbackCheck

Worknotes

Launch regedit and access the registry key “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa“.

Search for DisableLookback.

Make sure it exists as:

  1. Type :- REG_DWORD
  2. Data :- 1

Image

Script

Script – BackConnectionHostNames


@echo off
@echo on

set "_registryBranch=HKLM\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0"
set "_registryItem=BackConnectionHostNames"
set "_registryDataType=REG_MULTI_SZ"

rem ****************************************************************************************
rem please change to match your domain name
rem ****************************************************************************************
set "_domainName=labdomain.org"

set "_registryValue=%COMPUTERNAME%.%_domainName%"

echo "Value - Current"
reg query %_registryBranch% /v %_registryItem%

reg add %_registryBranch% /v %_registryItem% /t %_registryDataType% /d %_registryValue% /f

echo "Value - New"
reg query %_registryBranch% /v %_registryItem%

Script – DisableLoopbackCheck


@echo off
rem set "_registryBranch=HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa"
set "_registryBranch=HKLM\SYSTEM\CurrentControlSet\Control\Lsa"
set "_registryItem=DisableLoopbackCheck"
set "_registryDataType=REG_DWORD"
set "_registryValue=1"

echo "Value - Current"
reg query %_registryBranch% /v %_registryItem%

reg add %_registryBranch% /v %_registryItem% /t %_registryDataType% /d %_registryValue% /f

echo "Value - New"
reg query %_registryBranch% /v %_registryItem%

Summary

This problem is nothing.  It has been in the OS since Windows 2003.

And, so I suppose it is not really a problem, I just wished it was surfaced differently; than having to type my password thrice and still can’t get in.

 

References

  1. Microsoft
    • Microsoft Support
      • You receive error 401.1 when you browse a Web site that uses Integrated Authentication and is hosted on IIS 5.1 or a later version
        Link
  2. Nik Patel
    • Disable the Loopback Check for Specific Host Names on all SharePoint Web and Application Servers
      Link
  3. Michael Hanes
    • Use BackConnectionHostNames instead of DisableLoopbackCheck in production
      Link
  4. Harber.net
    • DisableLoopbackCheck & SharePoint: What every admin and developer should know
      Link

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