Premium per user - Loop through workspace and get Connection Strings

TCavins 

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.