Get SSRS Datasources from ReportServer

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

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.