Forum Discussion
Premium per user - Loop through workspace and get Connection Strings
I have a premium per user license and a workspace set up for premium per user.
I'm testing out ways to pull out the connections strings, the actual table name, view name etc that is used to populate the datasets used within the report.
My end goal is to loop through all of the reports in the workspace, get their datasets and then get the connection string specifics from the metadata.
Can anyone provide a link, example walking through how to do this?
I can pull the server and database name but the connection string is blank.
11 Replies
To extract connection details like server, database, and table/view names from Power BI datasets in a Premium Per User (PPU) workspace, you’ll need to use the Power BI REST API in combination with the XMLA endpoint. The REST API alone won’t expose full connection strings — that’s only accessible via XMLA.
Why the Connection String Appears Blank
The Power BI REST API (GetDatasets, GetDatasources) can return server and database names, but not full connection strings. This is by design for security reasons. To access detailed metadata like table/view names and full connection strings, you need to:
- Use the XMLA endpoint (available with Premium or PPU workspaces)
- Connect via SSMS, Tabular Editor, or TOM (Tabular Object Model) in a script
Recommended Approach
1. Enable XMLA Read Access
- In Power BI Service:
Go to Admin Portal > Tenant Settings > XMLA Endpoint
Set to Read-Only or Read-Write for your workspace.
2. Connect to XMLA Endpoint
- Use SSMS or Tabular Editor.
- Connect to:
powerbi://api.powerbi.com/v1.0/myorg/
3. Extract Metadata
- Once connected, you can:
- Browse data sources
- View table and view names
- Inspect M queries and DAX models
- You can also script this using TOM in PowerShell or C# to loop through models and extract metadata.
4. Loop Through Workspaces and Datasets (REST API)
- Use the following endpoints:
- GET /groups → list workspaces
- GET /groups/{groupId}/datasets → list datasets
- GET /datasets/{datasetId}/datasources → get server/database
- But again, these won’t expose full connection strings.
Thanks. I've connected to it via SSMS and ran the following. Is there a way to run something similar to get the name of every report and datasource within it?
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions />
<Properties>
<PropertyList>
</PropertyList>
</Properties>
</Discover>
Hi TCavins,
Pleaase refer the below link for more details it will resolve the issue:
Datasets - Get Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Thank you.
Hi TCavins,
Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.
Thank you.
I am unable to do anything with the Tabular Object Model. Anything that I take from examples, the server.Connect says invalid connection string.
I am able to create a token and authenticate with the API, but that does not give me the name of the views/tables etc that are in the datasources for each report.Hi TCavins,
I have done the repro at my and got the below output:
Below code should run in Powershell
Connect-PowerBIServiceAccount $workspaceId = "Enter your Workspacae ID here" $datasets = Get-PowerBIDataset -WorkspaceId $workspaceId foreach ($ds in $datasets) { Write-Host "===============================" Write-Host "Dataset: $($ds.Name)" Write-Host "===============================" # Datasources $urlDS = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$($ds.Id)/datasources" $sources = Invoke-PowerBIRestMethod -Url $urlDS -Method GET | ConvertFrom-Json $sources Write-Host "`nData Sources:" foreach ($s in $sources.value) { Write-Host " Type: $($s.datasourceType)" Write-Host " Server: $($s.connectionDetails.server)" Write-Host " Database: $($s.connectionDetails.database)" Write-Host "" } Write-Host "`n" }Thank you.
Hi TCavins,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
v-saisrao-msft I am unable to get any sample to work that uses the Tabular Object Model to connect to the XMLA endpoint. Everything gives me a connection error that I've tried. I've tried with user credentials and a service principal account.
Hi TCavins,
Since you are still receiving the error, the next best step is to raise a support ticket so that the Microsoft backend engineering team can investigate and resolve the issue for you.
You can raise a support ticket using the link below:
Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn
Thank you.
