cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Get all connection string for reports from power bi report server reportserver database

we try to get all connection string for all reports in power bi report server reportserver database. reportserver.dbo.DataModelDataSource table has all connection string, but it is encrypted, how we decrypt it?

 

 

6 REPLIES 6
josef78
Super User
Super User

Use standard PBIRS API, you can use e.g. PowerShell or Power BI Odata feed.

if odata feed, use https://servername/reports/api/v2.0/  URL and after navigate to PowerBIReports object

CDavies
Resolver I
Resolver I

Hi

 

Use the OData connector with the report server API.

 

Add your report server URL to this in the two places marked:

let

    Source = OData.Feed("https://<YOUR URL>/pbireports/api/v2.0/PowerBIReports", null, [Implementation="2.0"]),

    #"Expanded DataSources" = Table.ExpandTableColumn(Source, "DataSources", {"Id", "ModifiedBy", "ModifiedDate", "ConnectionString", "DataModelDataSource"}, {"DataSources.Id", "DataSources.ModifiedBy", "DataSources.ModifiedDate", "DataSources.ConnectionString", "DataSources.DataModelDataSource"}),

    #"Expanded DataSources.DataModelDataSource" = Table.ExpandRecordColumn(#"Expanded DataSources", "DataSources.DataModelDataSource", {"Type", "Kind", "AuthType", "Username", "ModelConnectionName"}, {"DataSources.DataModelDataSource.Type", "DataSources.DataModelDataSource.Kind", "DataSources.DataModelDataSource.AuthType", "DataSources.DataModelDataSource.Username", "DataSources.DataModelDataSource.ModelConnectionName"}),

    #"Added Conditional Column" = Table.AddColumn(#"Expanded DataSources.DataModelDataSource", "DS.Connection_String", each if [DataSources.ConnectionString] = null then "No Data Source" else [DataSources.ConnectionString]),

    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"CacheRefreshPlans", "AccessToken", "Roles", "ContentType", "Content", "ParentFolder", "Properties", "Comments", "AlertSubscriptions", "AllowedActions", "Policies", "DependentItems","Id", "ParentFolderId", "DataSources.Id", "DataSources.DataModelDataSource.ModelConnectionName"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DataSources.ConnectionString", "DS.ConnectionString"}, {"DataSources.DataModelDataSource.Type", "DS.Type"}, {"DataSources.DataModelDataSource.Kind", "DS.Kind"}, {"DataSources.DataModelDataSource.AuthType", "DS.AuthType"}, {"DataSources.DataModelDataSource.Username", "DS.Username"}, {"DataSources.ModifiedBy", "DS.ModifiedBy"}, {"DataSources.ModifiedDate", "DS.ModifiedDate"}, {"Name", "Report"}, {"Path", "Report Path"}}),

    #"Add Report URL" = Table.AddColumn(#"Renamed Columns", "Report URL", each "https://<YOUR URL>/PBIReports/powerbi" & [Report Path] & "?rs:embed=true")

in

    #"Add Report URL"

 

 

@CDavies  Hi, when i choose Odate feed (Get Data) from Power Bi, a message popup with credentials error, when i choose windows credentials, it says use anonamous, when i choose anonamous it throws an error,

 

does this work for anyone? I get an invalid character message after creating a .pbids with this....I also have a need to see all the unencrypted data sources and unable to get it to work?

It worked for me. Thanks, @CDavies  

Have a look at those links

 

List connection strings of all SSRS Shared Datasources 

decipher ReportServer.DataSource.ConnectionString 

Show connection string in the report? 




If my post solved your problem, mark my post as a solution to help others to quickly find it and also please give it a 👍

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.