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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Continued Contributor
Continued Contributor

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:

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

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors