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.

User's image

Answer

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

User's image

Regards，

Zoe Hui

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

Share via

Retrieve connection string from PowerBI reports

1 answer

Your answer