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

Microsoft Access – Querying SQL Server Table – Schema Stability Lock

Background

A quick follow-up to a post over the weekend.

The forwarding post is titled “SQL Server – Index Rebuild – Blocked / Blocking” and it is here.

In that post we spoke of how a scheduled Business Back Office Job was hung and could not proceed.

Using Adam Mechanic’s sp_whoIsActive, we discovered we had were indeed experiencing session blocking, but not an actual deadlock, which in fact would have triggered a vote as to which session to abort

We stopped the blocking database maintenance job which is an Index Defrag job.  The Index Defrag job simply calls Ola Hallengren’s IndexOptimize Stored Procedure.

TroubleShooting

TroubleShooting – Day 1

Adam Machanic

dbo.sp_WhoIsActive

Code

exec  [dbo].[sp_WhoIsActive]

Image
Session

SQL Text

Explanation
  1. Sessions
    • Login :- app
      • We are familiar with the app account
      • The sessions are the bottom two
    • SQL :- OpenRowSet
      • A SQL Server Profiler Trace we initiated to track the ongoings
    • Status :- suspended // wait_info :- ASYNC_NETWORK_IO
      • used_memory
        • 10, 000 KB ( 10 MB)

 

TroubleShooting – Day 2

Microsoft

Dynamic Management Views

sys.dm_exec_sessions
SQL

declare @appNameMSFTODBC sysname
declare @appNameMSFTOffice sysname
declare @appNameMSFTSSMS sysname
declare @appNameMSFTJavaJDBC sysname

declare @clienInterfaceName sysname

set @appNameMSFTODBC = 'Microsoft® Windows® Operating System'
set @appNameMSFTOffice = 'Office'

set @appNameMSFTSSMS = 'Microsoft SQL Server Management Studio - Query'
set @appNameMSFTJavaJDBC = 'Microsoft JDBC Driver for SQL Server'

set @clienInterfaceName = 'Microsoft JDBC Driver 4.0'

select 
		  tblDES.session_id
		, tblDES.program_name
		, tblDES.transaction_isolation_level
		, tblDES.open_transaction_count
		, tblDES.host_name
		, tblDES.client_interface_name
		, tblDES.client_version
		, tblDES.[status]
		, tblDES.[row_count]
		, tblDES.[prev_error]
		, tblDES.reads
		, tblDES.last_request_start_time
		, tblDES.last_request_end_time
		, [timeSinceLastCommunicationInMinutes]
			= datediff
				(
					  minute
					, tblDES.last_request_end_time
					, getdate()
				)

from  sys.dm_exec_sessions tblDES

where  tblDES.session_id >= 50

and	   (
			(
						
				   ( tblDES.program_name like @appNameMSFTODBC )
				or ( tblDES.program_name like '%' + @appNameMSFTOffice + '%' )

			)
			and
			(

				       ( tblDES.program_name != @appNameMSFTSSMS )
				   and ( tblDES.program_name != @appNameMSFTJavaJDBC )
			)

	   )


Output

 

Microsoft SQL Server Profiler

Image

Explanation

Here is the conversation captured:

  1. SQL:BatchStarting
    • SELECT Config, nValue FROM MSysConf
  2. SQL:BatchStarting
    • SELECT “dbo”.”oe_dep_audit”.”oe_dep_emp_location”,”dbo”.”oe_dep_audit”.”oe_dep_emp_ssn”,”dbo”.”oe_dep_audit”.”oe_year”,”dbo”.”oe_dep_audit”.”oe_dep_no”,”dbo”.”oe_dep_audit”.”oe_dep_session_ID”,”dbo”.”oe_dep_audit”.”oe_dep_record_type”,”dbo”.”oe_dep_audit”.”oe_dep_record_flag” FROM “dbo”.”oe_dep_audit”
      • Gets all the records in table
  3. SQL:BatchStarting
    • SELECT CASE DATABASEPROPERTYEX( DB_NAME(), ‘Updateability’) WHEN ‘READ_ONLY’ THEN ‘Y’ ELSE ‘N’ END
  4. RPC:Completed
    • SQL
      • declare @p1 int
        set @p1=1
        exec sp_prepexec @p1 output,N’@P1 char(2),@P2 char(9),@P3 char(4),@P4 int,@P5 char(10),@P6 char(1),@P7 char(1)’,N’SELECT “oe_dep_emp_location”,”oe_dep_emp_ssn”,”oe_year”,”oe_dep_no”,”oe_dep_session_ID”,”oe_dep_record_type”,”oe_dep_record_flag”,”oe_dep_name”,”oe_dep_birthdate”,”oe_dep_relationship”,”oe_dep_SSN”,”oe_dep_sex”,”oe_dep_disabled”,”oe_dep_medical”,”oe_dep_dental”,”oe_dep_optical”,”oe_dep_legal”,”oe_dep_PCP”,”oe_dep_current_patient”,”oe_dep_deleted” FROM “dbo”.”oe_dep_audit” WHERE “oe_dep_emp_location” = @P1 AND “oe_dep_emp_ssn” = @P2 AND “oe_year” = @P3 AND “oe_dep_no” = @P4 AND “oe_dep_session_ID” = @P5 AND “oe_dep_record_type” = @P6 AND “oe_dep_record_flag” = @P7′,’01’,’000000000′,’2012′,1,’a733167067′,’B’,’ ‘
        select @p1
    • Prepares a fetch Statement
  5. RPC:Completed
    • SQL
      • exec sp_execute 2,’01’,’00140′,’2013′,3,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,3,’a08938′,’O’,’A’,’01’,’00140′,’2013′,3,’a57784′,’O’,’A’,’01’,’00140′,’2013′,3,’a94593′,’O’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’O’,’A’,’01’,’00140′,’2013′,4,’a577848305′,’O’,’A’,’01’,’0014′,’2013′,4,’a94593′,’O’,’ ‘,’01’,’00154′,’2005′,100,’R50001′,’O’,’ ‘,’01’,’00154′,’2006′,1,’N6000′,’B’,’ ‘

 

Microsoft Network Monitor

Filter

//IP Address
(

    ( IPv4.Address == 10.1.20.182 )

)
and 
(

	(
          not ( Conversation.ProcessName == "Ssms.exe")
    )

	and
        ( 
             not ( Conversation.ProcessName == "PROFILER.exe")
        )
)

Traffic
Image

Explanation
  1. Using a Network monitor tool we can see that there is quite a bit of ongoing Network Activity between the client node running MS Access and the Database Server
  2. The protocols are plain TCP and TDP
  3. And, the ports are the default SQL Server Port of 1433 and the ephemeral ports from the Source Node

 

Summary

When the table queried from MS Access is reasonably large, the database connection is kept opened.  And, the DB table is locked with an object stability lock.

It is a designed behavior by MS Access and it reduces the amount of local resources on the client host.

Matthew Prince On Cloudflare’s Role

Background

CloudFlare’s Matthew Prince offers an educative post on the various official players that participate in our Current Internet Ecosystem.

He covers the role the Hacking community has chosen to play in who gets to stay up.

And, how there really is only a few big major global players.

And, finally, how everyone pretty much waits for the Legislative Bodies to set Rules and the Courts to jurisdicate.

 

Matthew Prince

Link

Where Do You Regulate Content on the Internet?

There are a number of different organizations that work in concert to bring you the Internet. They include:

  • Content creators, who author the actual content online.
  • Platforms (e.g., Facebook, WordPress, etc.), where the content is published.
  • Hosts (e.g., Amazon Web Services, Dreamhost, etc.), that provide infrastructure on which the platforms live.
  • Transit Providers (e.g., Level(3), NTT, etc.), that connect the hosts to the rest of the Internet.
  • Reverse Proxies/CDNs (e.g., Akamai, Cloudflare, etc.), that provide networks to ensure content loads fast and is protected from attack.
  • Authoritative DNS Providers (e.g., Dyn, Cloudflare, etc.), that resolve the domains of sites.
  • Registrars (e.g., GoDaddy, Tucows, etc.), that register the domains of sites.
  • Registries (e.g., Verisign, Afilias, etc.), that run the top level domains like .com, .org, etc.
  • Internet Service Providers (ISPs) (e.g., Comcast, AT&T, etc.), that connect content consumers to the Internet.
  • Recursive DNS Providers (e.g., OpenDNS, Google, etc.), that resolve content consumers’ DNS queries.
  • Browsers (e.g., Firefox, Chrome, etc.), that parse and organize Internet content into a consumable form.

There are other players in the ecosystem, including:

  • Search engines (e.g., Google, Bing, etc.), that help you discover content.
  • ICANN, the organization that sets the rules for the Registrars and Registries.
  • RIRs (e.g., ARIN, RIPE, APNIC, etc.), which provide the IP addresses used by Internet infrastructure.

Any of the above could regulate content online. The question is: which of them should?

Vigilante Justice

The rules and responsibilities for each of the organizations above in regulating content are and should be different. We’ve argued that it doesn’t make sense to regulate content at the proxy, where Cloudflare provides service, since if we terminate a user the content won’t go away it will just be slower and more vulnerable to attack.

That’s true, and made sense for a long time, but increasingly may not be relevant. The size and scale of the attacks that can now easily be launched online make it such that if you don’t have a network like Cloudflare in front of your content, and you upset anyone, you will be knocked offline. In fact, in the case of the Daily Stormer, the initial requests we received to terminate their service came from hackers who literally said: “Get out of the way so we can DDoS this site off the Internet.

You, like me, may believe that the Daily Stormer’s site is vile. You may believe it should be restricted. You may think the authors of the site should be prosecuted. Reasonable people can and do believe all those things. But having the mechanism of content control be vigilante hackers launching DDoS attacks subverts any rational concept of justice.

Increasing Dependence On A Few Giant Networks

In a not-so-distant future, if we’re not there already, it may be that if you’re going to put content on the Internet you’ll need to use a company with a giant network like Cloudflare, Google, Microsoft, Facebook, Amazon, or Alibaba.

For context, Cloudflare currently handles around 10% of Internet requests.

Without a clear framework as a guide for content regulation, a small number of companies will largely determine what can and cannot be online.

Freedom of Speech < Due Process

The issue of who can and cannot be online has often been associated with Freedom of Speech. We think the more important principle is Due Process. I, personally, believe in strong Freedom of Speech protections, but I also acknowledge that it is a very American idea that is not shared globally. On the other hand, the concept of Due Process is close to universal. At its most basic, Due Process means that you should be able to know the rules a system will follow if you participate in that system.

Due Process requires that decisions be public and not arbitrary. It’s why we’ve always said that our policy is to follow the guidance of the law in the jurisdictions in which we operate. Law enforcement, legislators, and courts have the political legitimacy and predictability to make decisions on what content should be restricted. Companies should not.

What We Would Not Do

Beginning in 2013, Cloudflare began publishing our semi-annual Transparency Report. At the time we choose to include four statements of things that we had never done. They included:

  • Cloudflare has never turned over our SSL keys or our customers’ SSL keys to anyone.
  • Cloudflare has never installed any law enforcement software or equipment anywhere on our network.
  • Cloudflare has never terminated a customer or taken down content due to political pressure.
  • Cloudflare has never provided any law enforcement organization a feed of our customers’ content transiting our network.

We included them as “warrant canaries” because we thought they could help us push back against the request that governments may try to force us to make. That’s worked and all four of the warrant canaries have survived in every transparency report since 2013.

We’re going to have a long debate internally about whether we need to remove the bullet about not terminating a customer due to political pressure. It’s powerful to be able to say you’ve never done something. And, after today, make no mistake, it will be a little bit harder for us to argue against a government somewhere pressuring us into taking down a site they don’t like.

Establishing a Framework

Someone on our team asked after I announced we were going to terminate the Daily Stormer: “Is this the day the Internet dies?” He was half joking, but only half. He’s no fan of the Daily Stormer or sites like it. But he does realize the risks of a company like Cloudflare getting into content policing.

There’s a saying in legal circles that hard cases make bad law. We need to be careful of that here. What I do hope is it will allow us all to discuss what the framework for all of the organizations listed above should be when it comes to content restrictions. I don’t know the right answer, but I do know that as we work it out it’s critical we be clear, transparent, consistent and respectful of Due Process.

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.

Visual Studio 2010 – Upgrade from RTM to SP1

Background

Just as yourself, got a lot to do, but occasionally get pulled back in.

 

SQL Server 2008-R2 Upgrade

My manager has signed us up to upgrade all SQL Servers to the most recent version, 2016. Or at minimum the version that precedes the latest; btw, that is 2014.

The SQL Instance that I am looking at currently is v2008-R2 and the OS is 2008/R2.

That latest SQL Server supported on Windows 2008-R2 is SQL Server 2014.

 

Upgrade 2008-R2 to 2014

Trying to upgrade v2008-R2 to v2014, but ran into a constraint right away.

Features Rules

Textual

This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection.

To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649.

Image

Download Visual Studio 2010 SP1

Tried a drive by visiting https://www.microsoft.com/en-us/download/details.aspx?id=23691.

Redirected to https://www.microsoft.com/en-us/download/details.aspx?id=23691.

Download Visual Studio 2010 SP1 – Download 23691

Textual

We are sorry, this download is no longer available.

Image

 

Download Links

Source Link Works
go.microsoft.com/fwlink/?LinkID=220649 Link No
http://www.microsoft.com/en-us/download/details.aspx?id=23691 Link No
download.microsoft.com/download/E/B/A/EBA0A152-F426-47E6-9E3F-EFB686E3CA20/VS2010SP1dvd1.iso  Link  No
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=75568aa6-8107-475d-948a-ef22627e57a5&displaylang=en Link  No
 my.visualstudio.com/Downloads?q=visual%20studio%202010%20service%20pack%201  ( All ) Link  Yes
my.visualstudio.com/Downloads?pid=2300 ( Visual Studio 2010 Service Pack 1 ) Link  Yes

 

Downloaded

Please download the online installer availed here.

BTW the link noted above is from my.visualstudio.com web site.

And, you need a registered user.

If you have yet to register for my.visualstudio.com, please do so.

It is a free.

 

Downloaded Results

Here are results that matches “Visual Studio Service Pack 1

Downloaded

Visual Studio 2010 Service Pack 1 – Details

File Specification

The file’s name is mu_visual_studio_2010_sp1_web_installer_x86_651694.exe.

It is a relatively small file, 795 KB.

It is an online installer and it is a bootstrap as it simply connects to the website and downloads the actual install media.

 

Install Visual Studio 2010 – SP1

Screen Shot

Welcome

Microsoft Visual Studio 2010 Service Pack 1 Setup

Installation Progress

Installation Is Complete