Retrieve connection string from PowerBI reports

Question

Hi All,
I need to retrieve the connection string of the PowerBI reports. Could you help to get the query for it.

As I have seen in Catalog, Datasource and Extended Datasource, but unable to get the data related to PowerBI reports dashboards, their type is 13 in catalog table under Reports database.

Please find attached screenshot, need to get the connection string data through sql server query for all the PowerBI reports.

Answer 1

Hi @Sravanthi Godavarthy,

Please try with below code.

WITH  ItemContentBinaries AS 
(
SELECT
    Name
,   Type
,   CONVERT(VARBINARY(MAX), Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type = 2 
)
, ItemContentNoBOM AS (
SELECT
    Name
,   CASE WHEN LEFT(Content, 3) = 0xEFBBBF THEN CONVERT(VARBINARY(MAX),     SUBSTRING(Content, 4,LEN(Content)))
        ELSE Content 
        END AS Content
FROM ItemContentBinaries
)
, xmlcontent as (
SELECT
    Name AS DataSourceName
,   CONVERT(xml, Content) AS ContentXML
FROM ItemContentNoBOM 
)
Select     
DataSourceName
,   ContentXML
,ContentXML.value('(//*[local-name()="ConnectString"])[1]', 'nvarchar(max)')
from xmlcontent

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.