Reporting Services – Errors – ‘The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)’

Background

Post upgrade of Microsoft’s SQL Server, trying to access Reporting Services, ran into an error.

Error

Error Image

TheFeatureScaleOutDeploymentIsNotSupported.20181111.0342PM.PNG

Error Textual

The feature “Scale-out deployment” is not supported in this edition of Reporting Services.  (rsOperationNotSupported).

Trouble Shooting

Outline

  1. Confirm SQL Server Edition
  2. Review registered encryption keys for the Report Server Instance

Confirm SQL Server Edition

Launch SQL Server Query tool and issue a query that will help determine SQL Server Edition:

Code


select @@version as [version]

Output

Output Image

@@version.20181115.0551AM

Output Textual


Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)
Oct 18 2018 23:11:05
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Explanation

  1. We are running SQL Server
    • Marketing :- v2017
    • RTM/Service Pack :- RTM
    • Cumulative Patch :- CU12
    • Edition :- Standard Edition

Review registered encryption keys for the Report Server Instance

using same query tool you used to determine SQL Server’s Version Number, please review the contents of the ReportServer.dbo.keys table.

Code

select *

from [ReportServer].dbo.[Keys]

Output

Output Image

ReportServer.dbo.keys.01.20181111.0342PM [brushedup].png

Explanation

  1. We have three entries
    • Records
      • Installation ID
        • 00000000-0000-0000-0000-000000000000
          • We can skip for now
        • BDEBD908-9B5E-4971-B6A5-0E6EB267A2CA
          • InstanceName = SSRS
            • SSRS is the new instance name for SQL Server Reporting Services
            • And, we can confirm that it is the one post upgrade
          • InstanceName= MSSQLSERVER
            • MSSQLSERVER is pre-upgrade name
            • We can remove it

Remediation

Outline

  1. Backup database table
  2. Remove previous key

Backup database table

Let us backup ReportServer.dbo.keys

Code


if schema_id('dbBackup') is null
begin

create schema [dbBackup] authorization [dboo];

end

select *

into   [ReportServer].[dbBackup].[keys.20181115.0607AM]

from   [ReportServer].[dbo].[keys]

Remove Previous Key

Remove previous key from ReportServer.dbo.keys

Code


delete
from   [ReportServer].[dbo].[keys]
where  InstanceName = 'MSSQLSERVER'

BitLocker – Configuration – Error – “Unable to find the Reporting Services instance name”

Background

Recently we ran into an “ha ha” moment installing Microsoft BitLocker.

 

BitLocker Administration and Monitoring

Configuring Reports

Here is the “Configuring Reports” window.

ConfigureReports_20180720_1008AM

Error

Error Image

unableToFindTheReportingServicesInstanceNameMSSQLServer

Error Text

Unable to find the Reporting Services instance name <server-name>\MSSQLServer

Trouble Shooting

Reporting Services Configuration Tool ( RSConfigTool )

Launched SQL Server Reporting Services Configuration Tool ( RSConfigTool).

RSConfig_ReportServerStatus_20180720_1033AM.png

Ensured that the service is running and noted the Instance ID.

The Instance ID is SSRS.

Remediation

BitLocker Administration and Monitoring

Configuring Reports

For the instance name, please note the instance name recorded earlier.

That instance name is SSRS.

Configuration SSRSDB.20180720_1041AM

 

 

Sapien – WMI Explorer – Reporting Services Configuration Manager

Background

Wanted to utilize another WMI Query tool in addition to Microsoft’s own WMI tools.

 

Lineage

Posts

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

In the “SQL Server Reporting Services (SSRS) – Error – Invalid Class” post, we bemoaned the difficulty of fully espousing our wilderness experience based on screenshots from a single tool; that tool being Microsoft WMI Explorer.

The post is here.

Microsoft’s WMI Explorer is available at Codeplex and here is the specific URL.

 

Sapien

We wanted to try out one more tool and the one we chose is Sapien WMI Explorer.

Download

Please download WMI Explorer from here.

The current version is 2.2.74.

Requirements

  1. OS Version
    • Desktop :- Windows 7 / Windows 8 / Windows 8.1 / Windows 10
    • Server :- Windows Server 2008 R2 / Windows Server 2012 / Windows Server 2012 R2
  2. OS Bitness
    • 32 and 64 bit
  3. Powershell
    • Version :- Powershell Version 3.0
  4. Visual Studio 2012 Runtime

 

Install

Installation is straightforward.

Register

Connect to the Vendor’s web site and request a trial key.

 

Usage

Launch Sapien’s WMI Explorer and navigate the Class Browser tree.

As one chooses a specific Namespace, the classes and corresponding properties and methods for that class are shown in the right window.

We are interested in SQL Server Namespace and specifically the ReportServer space.

Please click on the Query button on the Ribbon Tab to view and edit queries.

Image

Pasted below are the images captured from our journey.

Hierarchy

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>
Instance – Default

Instance – DATACAP

 

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>\<Version>
Instance – {RS_DATACAP }\ Version {v12}

Instance – {RS_DATACAP }\ Version {v12} – Custom Query

Instance – {RS_DATACAP }\ Version {v12} – Query Results

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>\<Version>\Admin
Instance – {RS_DATACAP }\ Version {v12} \ Admin – Custom Query

Instance – {RS_DATACAP }\ Version {v12} \ Admin – Query Results

 

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

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 :- 587 /
Connection Start :- SSL/TLS
Port :- 587 /
Connection Start :- STARTSSL
Server Name smtp.office365.com smtp.office365.com smtp.office365.com
Port 465 587 587
Connection Security SSL/TLS SSL/TLS STARTTLS
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

 

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