topic Re: Get all connection string for reports from power bi report server reportserver database in Report Server

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

Anonymous — Mon, 03 Feb 2020 23:48:10 GMT

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?

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

CDavies — Tue, 04 Feb 2020 07:40:24 GMT

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"

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

gregHM — Thu, 16 Jul 2020 13:29:25 GMT

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?

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

saglamtimur — Fri, 17 Jul 2020 06:51:16 GMT

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 👍

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

Anonymous — Wed, 09 Feb 2022 07:52:52 GMT

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,

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

josef78 — Thu, 10 Feb 2022 21:06:53 GMT

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

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

DMarcio — Mon, 23 May 2022 14:04:50 GMT

It worked for me. Thanks, CDavies

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

Gopikrishna-J — Mon, 16 Oct 2023 13:47:46 GMT

Hi ,

I tried using above the OData connector with the report server API method and code, but it's throwing below error message when apply the change on Power Query Editor, could you please help me in this fixing this error. Thanks in advance 🙂