Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II
Resolver II

PowerShell Script to export Database Connection strings in XMLA

Hi there,

I am working on a automation project, where i have a requirement to update the connection strings and credentials via powershell.

The process flow would likely to be 

>Connect SQL Server Analysis Services

>Select an Database

>Export the connection details into a xmla file.


Currently we are doing it manually via SSMS

> Opening SSMS --> Connect to SQL Server Analysis Services

>Select required Database

>Expand Connections folder --> Select and Right click a connection

>Right Click --> Script Connection as --> CREATE OR REPLACE To --> New Query Editor Window


This will generate a xmla scirpt of that connection. I want to achieve this process via powershell.



Super User
Super User

How is this question related to Power BI?  I assume you are aware of the Power BI REST API?

Its not directly related.. Yes I am aware of Power BI Rest Api, but it doesn't work with Onprem report server.

So I need to achieve this without using webservice

Run two DMV queries. 




How can i run these queries ?

You can run them from any xmla client tool, including Powershell.

Invoke-ASCmd -Server "powerbi://<workspace>" -Database "<dataset>" -Query "select * from `$SYSTEM.TMSCHEMA_PARTITIONS" | Out-File -FilePath c:\users\<user>\Downloads\dmv.xml

Hi @lbendlin 

I tried your code, but it throws error like System Cube not available. I am accessing this command on Onprem Report server.

Not sure about the syntax there but it should work the same if you properly specify server and cube

If powershell is not a requirement maybe you could think to use Tabular Editor and a combiantion of Advanced Scripting and Command Line interface.

I've not tested with your usecase but I did something similar in the past:

Riccardo Perico
BI & Power BI Enginner @ Lucient Italia

Blog | GitHub

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your suggestion, but currently I can't use Tabular Editor due to some limitations.

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.