SSRS Data Sources

Overview

SSRS Data Sources shows what a report server connects to and how it gets in. Every row in the report server’s DataSource table is listed, and each is put into one of four kinds, because they behave completely differently:

Kind What it is
Shared A catalog item of its own that many reports can point at. Changing it changes every report using it
Uses a shared one A report’s data source that names a shared one. Its own credential settings are ignored; the shared source decides how the report connects
Embedded A data source defined inside one report. It keeps its own credentials and does not appear in the portal’s list of data sources
Subscription query The data source behind a data driven subscription’s query

The page is for three jobs:

  • Finding the shared data source that forty reports depend on and nobody documented.
  • Finding the embedded data sources that quietly hold stored credentials of their own.
  • Finding the data sources that cannot supply credentials when nobody is there, which break every subscription on the reports that use them.

What this page does not show

Connection strings. They are stored encrypted with the report server’s symmetric key, and reading the column yields bytes, not text. The page shows the provider, which says what kind of thing is at the other end, and the credential mode, which says how it gets in. It says this on every load.

Where to find it

This report is only shown for a database that holds a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Data Sources.
  • Toolbar: the Data sources button on SSRS Catalog Inventory, SSRS Shared Datasets, SSRS Report Parameters and SSRS Failed Executions.
  • Right click: Go to its data sources on the Catalog Inventory and Report Parameters grids and Go to its data source on the Shared Datasets grid, which open this page with that item’s data source rows already selected; and All data sources on this server on Catalog Inventory and Go to its data sources on the Failed Executions grid, which opens it with that report’s rows selected.
  • Double click: a bar on SSRS Shared Datasets, or on an item with data sources of its own in SSRS Catalog Inventory, opens this page with that item’s rows selected.
The SSRS Data Sources report: shared data sources ranked by how many reports use them
Shared data sources ranked by the number of reports using them, above every data source row on the server.

Requirements

  • A Reporting Services catalog database, normally called ReportServer. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist in the database being looked at.
  • SELECT on dbo.DataSource, dbo.Catalog and dbo.Subscriptions, and on dbo.DataSets where the catalog has it.

The two views

The toolbar switches the chart between By use and By kind. The grid below does not change with the view.

By use

The SSRS data sources chart
One bar per shared data source, longest for the one the most reports point at.

Only the shared data sources, ranked by how many different reports use each one, directly or through a shared dataset, and by name where two have the same count. A shared source nothing points at reads nothing uses it. One that something still points at, such as a shared dataset no report references, but that no report uses reads no report uses it. The references and embedded sources are left out of this view, because a bar of zero for them would mean “not that kind of row” rather than “nothing uses this”.

By kind

Every data source row, shared ones first, then the ones that use a shared source, then embedded, then subscription queries, in name order within each kind. The text on the right is the credential mode. The bar length is the number of subscriptions that depend on the row (see Subscriptions under Where the data comes from below), drawn at a minimum of one so every row has a bar.

Colors and outlines

Color Meaning
Green Nothing to report
Amber A shared source nothing points at, or a source that cannot supply credentials unattended
Red A reference to a shared source that is no longer in the catalog, or a source that cannot supply credentials unattended with subscriptions depending on it. For a shared source, those are the subscriptions on the reports that use it

Any row that is broken, unused or cannot run unattended also gets an amber outline around its bar.

The chart draws at most the first thirteen rows. When there are more, the note under the chart says how many are shown out of how many, and the grid holds them all. Hover over a bar for the item it belongs to, its kind and provider, its credentials, how many subscriptions depend on it and its notes.

Reading the grid

The SSRS data sources grid
One row per data source, with the credential mode and notes colored when there is a problem.
Column What it holds
Name The data source name. A shared data source’s own row has no name, so its catalog name is shown. (unnamed) when there is neither
Kind Shared, Uses a shared one, Embedded or Subscription query
Provider The data processing extension, as the report server stores it
Credentials Prompt, Stored, Integrated or None, with credentials stored added when a user name is saved against it. For a row that uses a shared source this reads from the shared source
Reports using it For a shared data source, how many different reports use it, through a data source of their own that links to it or through a shared dataset that queries it. A report is counted once however many ways it reaches the source, and a shared dataset is not a report. Blank for the other kinds. The cell holds the plain number, so the column sorts numerically
Belongs to The catalog path of the shared source or the report the row belongs to. For a row that uses a shared source, the report path and the shared source’s path joined by an arrow
Notes What the page found about the row

Credentials and Notes are drawn amber or red on the same rules as the chart colors.

What the notes say

  • It points at a shared data source that is no longer in the catalog, so the report cannot connect.
  • Subscriptions depend on it (for a shared source, through the reports that use it) and its credential mode cannot be supplied when nobody is there.
  • It cannot run unattended, so a subscription added to it would fail.
  • Nothing points at this shared data source.
  • No report uses this shared data source, although data source rows still point at it, such as a shared dataset that no report references.
  • It holds a stored user name and password, encrypted with the report server key, which are lost if the database is restored somewhere without that key.
  • Its connection string is an expression, so what it connects to can change per run.

Only Stored and None count as able to run unattended. Prompt has nobody to prompt, and Integrated has no Windows user to connect as when a schedule fires.

Findings

The headline counts the data source rows, how many are shared, how many use a shared one, how many are embedded, and how many different providers they use when there is more than one. The line under it always starts by saying that connection strings are not shown and why, then adds whichever of these apply:

  • Reports pointing at a shared data source that is no longer in the catalog.
  • Data sources that have subscriptions depending on them and cannot supply credentials unattended.
  • Shared data sources nothing points at.
  • Sources holding stored credentials, and that those are lost if the database is restored somewhere the report server key is not.
  • The shared data source the most reports use, when more than one does, and that a change to it reaches all of them.
  • What an embedded data source is, when there are any.
  • That the page reads the catalog tables directly, so it shows every item on the report server rather than the subset the portal would show you.

Actions

  • Click a bar to select that data source’s row in the grid.
  • Double click a bar to open SSRS Catalog Inventory with the item the data source belongs to selected, as Where it lives in the catalog does.
  • Right click a grid row, or a bar, for:
    • Copy the path it belongs to, Copy the data source name, and Copy the shared source it uses for a row that points at one
    • Where it lives in the catalog, which opens Catalog Inventory with the item the row belongs to selected, or The whole catalog for a row that belongs to no catalog item
    • All subscriptions on this server, when subscriptions depend on the row. It opens SSRS Subscriptions for the whole report server, not filtered to this data source
    • All shared datasets
  • Right click the chart away from a bar for Copy Chart to Clipboard.
  • The toolbar’s Shared datasets, Catalog and Subscriptions buttons open SSRS Shared Datasets, SSRS Catalog Inventory and SSRS Subscriptions.

Where the data comes from

One query against dbo.DataSource, joined to dbo.Catalog twice: once for the item the row belongs to and once for the shared source its Link column names.

  • Kind is decided from a few columns. A row with a SubscriptionID is a subscription query. A row whose owning item is a data source (catalog type 5) is shared. A row with a Link is a reference, and it is broken when the link target is not in the catalog. A row with no Link whose Flags column has lost its 0x2 bit is also a broken reference: that is what the report server leaves when the shared source it pointed at is deleted, because the delete sets Link to NULL rather than leaving it dangling. Anything else is embedded.

  • Reports using it counts distinct reports (catalog types 2, 4, 12, 13 and 14): those with a DataSource row whose Link names the shared source, and those with a dbo.DataSets row referencing a shared dataset whose own DataSource row links to it. That is the report part of the report server’s own dependent items list, which lists the shared datasets as well. The shared dataset part needs dbo.DataSets and is left out on a catalog without that table.

  • Nothing uses it is a separate count of every DataSource row whose Link names the shared source, whatever owns the row, so a shared source that only a shared dataset points at is not called unused. It is the same test SSRS Never Run uses.

  • Subscriptions counts the rows in dbo.Subscriptions that depend on the data source:

    • for any row, the subscriptions on the item it belongs to;
    • for a shared data source, also the subscriptions on every report whose own data source links to it, on every report that references a shared dataset which queries it, and the data driven subscriptions whose query uses it;
    • for a shared dataset’s own data source, also the subscriptions on the reports that reference that dataset.

    A subscription on a linked report counts against the report it links to. The shared dataset parts need dbo.DataSets and are left out on a catalog without that table.

  • Credentials stored is a test of whether UserName is null. The value itself is encrypted and is never selected, and neither are the connection string columns.

The batch runs at READ UNCOMMITTED, so reading the catalog does not take shared locks that block the report server writing to it.

Messages you may see

This report server connects to nothing. There are no data sources in the catalog, shared or embedded. A report server with reports and no data sources has reports that cannot run, so check SSRS Catalog Inventory.

The report server query did not finish in time. Press F5 to try again.

Frequently asked questions

Why can I not see the connection string? The report server encrypts it with its own symmetric key before storing it. Nothing reading the table can turn it back into text, so the page shows the provider and the credential mode instead.

A report uses a shared data source with Prompt credentials and has subscriptions, but its row is not red. Why? A row that uses a shared source takes its credentials from that shared source, so the page does not judge the row’s own credential setting. The shared source’s own row is where the credential mode shows, and that row is the one drawn red: its subscription count is the subscriptions on the reports that use it.

Why is Reports using it lower than the portal’s list of dependent items? The portal lists shared datasets alongside reports. This page counts reports only, including the ones that reach the data source through a shared dataset, and counts each report once.

Is a shared data source marked “nothing uses it” safe to delete? Nothing in the catalog points at it when the page loaded. That is a reference count rather than a run count, so it is not bounded by the execution log’s retention. A report published after the page loaded would not be counted, so press F5 before acting on it.