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]
--, [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

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'
     AS rd)
    (SELECT SDS.name AS SharedDsName
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

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

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

USE [ReportServer]

    ,DataSource.NAME datasource
    ,Created.UserName AS CreatedByUser
    ,Modified.UserName AS ModifiedByUser
FROM [dbo].[Catalog]
    SELECT [UserID]
    FROM [dbo].[Users]
    ) AS Created ON CATALOG.CreatedByID = Created.UserID
    SELECT [UserID]
    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
  • 1
    The connection string query you found at the 'Script Center', does get some (about 50% of mine) of the connection strings. I added the data source name to my query as well after posting the question. All in all this is a good answer that meets my criteria, thank you. May 12 '16 at 12:39
  • 1
    The script does a good job of getting the Data Source names, but the connection strings will likely be outdated/incorrect. See the answer from Cody Konior about why that is.
    – Mike
    Jun 23 '20 at 6:19

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.

  • 1
    The solution by JUICED_IT, gets connection string details. May 12 '16 at 12:33
  • 1
    Yes, their solution does what I described by extracting the definition with XML/XPath. But if anyone has overridden them in the GUI you'll get old and incorrect results. If that's not important for your purposes then great. May 12 '16 at 12:44
  • In the end I think I am going to manually harvest the connection details from the GUI. I have lots of reports using a handful of data sets, so for my current need manually harvesting and joining to the SQL will let me move forward, with the least pain. I will export to Excel for individual tracking/follow-up, so I can join the data source details in Excel. May 12 '16 at 12:44
  • This is a very frustrating problem that the XML will not update. I even published a new RDS file with the correct connection string. But the XML won't update, only the portal connection string gets updated. As one answer here suggests, The rstools module cmdlet works nicely for retrieving the accurate connection string for a single data source. I supposed there is a way to loop through them all. PS > get-rsdatasource -ReportServerUri "http://ssrsdev/ReportServer" -path "/Data Sources/03-PROD/myDataSourceName" Jun 23 '20 at 16:27

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.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.