SSRS – There are misconfigured data sources - Azure Cloud & AI Domain Blog

Background

Occasionally we may receive the following alert:

SSRS 2012: There are misconfigured data sources

Gentleman, Start your hacking

So let’s rip open the MP and see what’s going on. After going recursively bottom to top I finally understood that this is the DataSource being referenced in the monitor/probe:

And we can see these references:

<Assembly>SQLRS!Microsoft.SQLServer.2012.ReportingServices.Deployment.Assembly</Assembly> <Type>Microsoft.SQLServer2012.ReportingServices.Module.Deployment.AllInstancesAreDiscoveredMonitor</Type>

What in the world?!

If we open ProcMon and/or search the file system we will see the following file referenced:

SQL 2012:
- “C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Cabinets\Microsoft.SQLServer.2012.ReportingServices.Monitoring.357.cab”
SQL 2014:
- “C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Cabinets\Microsoft.SQLServer.2014.ReportingServices.Monitoring.283.cab”
SQL 2016:
- “C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Cabinets\Microsoft.SQLServer.2016.ReportingServices.Monitoring.530.cab”
Etc.

We can extract the contents of this file to find a few files within:

Now let’s extract all the files and copy them to the desktop. Then rename the manifest file to manifest.txt and open it with notepad.

We will see the following content:

{d032ca24-9972-b9da-d045-31cd2519c56c}=MP.Microsoft.SQLServer.2012.ReportingServices.Monitoring

{b1579809-8203-b518-8b4f-d9901688bfd7}=RES.Microsoft.SQLServer.2012.ReportingServices.Module.Monitoring.dll.{b1579809-8203-b518-8b4f-d9901688bfd7}

If we compare the file names above (“{b157..}) to the files names in the text file we will see a match. So let’s just manually rename the files ourselves. We only need to do this for the first file.

But wait, there’s more!

We need to repeat the above process in order to extract some necessary dependencies. So, let’s also grab and extract the following files, using the same process as above:

SQL 2012:
- “C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Cabinets\Microsoft.SQLServer.2012.ReportingServices.Discovery.96.cab”
SQL 2014:
- “C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Cabinets\Microsoft.SQLServer.2014.ReportingServices.Discovery.523.cab”
SQL 2016:
- “C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Cabinets\Microsoft.SQLServer.2016.ReportingServices.Discovery.401.cab”
Etc.

And now what? Champaign?

Not just yet, but sit tight. Let’s open the DLL with JustDecompile (or your most favorite decompile tool) and let’s see what’s inside.

When we see the counter above:

JustDecompile by Telerik will prompt us to load another dll (Microsoft.SQLServer.2012.ReportingServices.Module.Helper.dll):

This is one of the DLLs we extracted from the Discovery cab file.

Then it will continue to complain about some other DLLs:

Just click Skip.

Gotcha!

Now let’s go back to our management pack and see what it monitors:

This is the monitor:

And this is the bread n’ butter:

<TSQLCounterClassName>CountableStatistics</TSQLCounterClassName>

<TSQLCounterPropertyName>MisconfiguredDataSources</TSQLCounterPropertyName>

When going back to JustDecompile I can now search for this statistic and see how it’s calculated:

And this is the query:

SELECT COUNT(1) as RETURN_VALUE FROM [Catalog] AS c INNER JOIN DataSource AS ds ON ds.ItemID = c.ItemID WHERE ds.Link IS NULL AND ds.Extension IS NULL

Now, in order to see the name of the problematic DataSource let’s modify the query a bit:

SELECT * FROM [Catalog] AS c INNER JOIN DataSource AS ds ON ds.ItemID = c.ItemID WHERE ds.Link IS NULL AND ds.Extension IS NULL

Just to confirm this DataSource is indeed corrupt, when browsing SSRS I find:

The perpetrator has been found! Book him!

Credit goes to Reuven Singer

Oren Salzberg (PFE)