Get SSRS Datasources from ReportServer

Question

Housekeeping on an old SSRS, this particular server is 2008R2.

I have the query below which gets me the name, location, creation and modification overview for all the reports. I would also like to include the 'Connection String' at least, and some other details if possible from the data sources properties. But I can't find it in ReportServer. I have seen people use PowerShell to get it out of SSRS web service, I was hoping to find a T-SQL solution.

How can I display Data Source details with a SQL query?

--List all the reports on SSRS via "ReportServer" database 

USE [ReportServer]
GO
SELECT Name
--, [ItemID] --Primary key
, [Path]
, [Description]
--, [CreatedByID] --need link to get anything usable from here
, Created.UserName as CreatedByUser
, [CreationDate]
--, [ModifiedByID] --need link to get anything usable from here
, Modified.UserName as ModifiedByUser
, [ModifiedDate]


FROM [dbo].[Catalog]
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Created
        on Catalog.CreatedByID = Created.UserID
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Modified
        on Catalog.ModifiedByID = Modified.UserID

WHERE [Type] = 2 -- value per foundation Source http://sqlsrv4living.blogspot.com/2014/01/ssrs-get-list-of-all-reports-using.html

ORDER BY [Path], Name

Answer 1

Get SSRS Datasources from ReportServer

I would also like to include the 'Connection String' at least

How can I display Data Source details with a SQL query?

TSQL for getting the Connection String of SSRS Data sources

-- Transact-SQL script to get connection string of all SSRS Shared Datasources.

/*
Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,
to document the usage or to search for a specific server/database.

Please remark: Querying the ReportServer database directly is not a supported way.
Works with SSRS 2005 and higher version ReportServer databases.
Requieres select rights on the "Catalog" table in ReportServer database.
*/

-- Connection strings of all SSRS Shared Datasources
;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY 
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;

Here's a query to add the data source name as well using your TSQL:

USE [ReportServer]
GO

SELECT CATALOG.NAME
    ,CATALOG.[Path]
    ,DataSource.NAME datasource
    ,CATALOG.[Description]
    ,Created.UserName AS CreatedByUser
    ,CATALOG.[CreationDate]
    ,Modified.UserName AS ModifiedByUser
    ,CATALOG.[ModifiedDate]
FROM [dbo].[Catalog]
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Created ON CATALOG.CreatedByID = Created.UserID
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID
JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID
JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID
WHERE CATALOG.[Type] = 2
ORDER BY [Path]
    ,NAME

Answer 2

You can't.

When you upload data source and report definitions to the report server it does store their information in the dbo.Catalog table which you can see using Convert(Xml, Convert(Varbinary(Max), Content)) and as it's XML you can use XPath to pull out the fields you're interested in.

The problems really begin once you modify anything on the SSRS web GUI.

If you change the connection string within a shared data source, or modify a report's data source, then SSRS will leave the catalog entry as-is and instead add an entry to the dbo.DataSource table with an encrypted ConnectionString to hold the updated content.

While you can't decrypt it here you might be able to extract some of the connection strings and then flag outdated ones; but I haven't been able to find how it stores links between the old/new content.

This has also been been discussed before on Ask SSC where they reached basically the same conclusion https://ask.sqlservercentral.com/questions/20328/decrypting-datasources-in-ssrs.html

And there's a collection of partial scripts for calling the web API and WMI on SSC itself when they discussed the same http://www.sqlservercentral.com/Forums/Topic1312030-150-1.aspx

I'd encourage you to flip the problem and try to come up with a PowerShell solution because with the increasing complexity of SQL Server it's simply not feasible to try to do everything within T-SQL anymore.

Answer 3

Using the ReportingServicesTools module for Powershell to get data source details is probably the best (get-RsDatasource), as it's able to extract and decrypt the ConnectString for a datasource (without password or username info in it), and also give the username it might be using in a separate field. It does this through the SOAP provider.

A Datasource stored in the Catalog table (type=5) does also usually have its ConnectionString stored in the [Content] field, which you can then try and pull out with a XPath query. But on my server, I have a couple of these datasources that have their connection info somehow stored somewhere else, so the SQL-based query returns no connection string info for them. The ReportingServicesTools get-RsDatasource function will return the connection string for these, however.

I also have a bunch of 3rd-party system-supplied reports that have their own custom provider. These don't store a connection string in their RDL or in the ReportingServices..Catalog or ReportingServices..Datasource tables. get-RsDatasource returns bupkus for these reports' connections.

I imagine that if I was slightly more motivated, I'd add in some more stuff to my t-sql query to try and grok the connection info out of the RDL as well. But except for the app reports, our internally developed reports all use shared datasources, so my motivation for this is low.

ymmv.

Answer 4

Have a left outer join for datasources , in case you have dashboard reports for links or so , also to be part of the audit report.

SELECT CATALOG.NAME
    ,CATALOG.[Path]
    ,DataSource.NAME datasource
    ,CATALOG.[Description]
    ,Created.UserName AS CreatedByUser
    ,CATALOG.[CreationDate]
    ,Modified.UserName AS ModifiedByUser
    ,CATALOG.[ModifiedDate]
FROM [dbo].[Catalog]
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Created ON CATALOG.CreatedByID = Created.UserID
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID
left outer JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID
LEFT outer JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID
WHERE CATALOG.[Type] = 2 
ORDER BY CATALOG.[ModifiedDate] desc, [Path] 
    ,NAME