Sql-server – Use Powershell to query SSAS connection string

powershellsql-server-2016ssas

I want to retrieve the existing connection string from SSAS for each OLAP Cube or Database. The data will be used in a control/audit report.

I've found that Powershell allows me to drill into SSAS like a directory and I can see what I'm after, but don't know how to query it.

in Powershell I can drill down to here:

PS SQLSERVER:\SQLAS\LAP123\Default\databases\EnvironmentalAnalysisService\Datasources> 

And I can see the details I want:

Name                      Isolation     Max Connections Connection String                       
----                      ---------     --------------- -----------------                       
Environmental Data Mart   ReadCommitted              10 Provider=SQLNCLI11.1;Data               
                                                        Source=LAP123;Integrated                
                                                        Security=SSPI;Initial                   
                                                        Catalog=EnvironmentalDataMart

In this case Server; LAP123 & Database: EnvironmentalDataMart

How can I get the items I want from Powershell like a query?

Best Answer

Woohoo!!!

# Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

## Connect and get the edition of the local server
$connection = “localhost”
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

foreach ($d in $server.Databases )
{   

    Write-Output ( “Database: {0}, String {1}:” -f $d.Name, $d.DataSources.ConnectionString)        

} # Databases

returns:

Database: EnvironmentalAnalysisService, String Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=EnvironmentalDataMart:
Database: PerformanceAnalysisService, String Provider=SQLNCLI11.1;Data Source=DEV-EDW;Integrated Security=SSPI;Initial Catalog=PerformanceDataMart:

PS SQLSERVER:\SQLAS>