none
How to get the connection string WITHOUT password? RRS feed

  • Question

  • i am updating connection strings, and after that i want to display the new updated connecting string. however, the password is getting displayed as well. 

    the only way i can think of to view the new connection without the password is to disconnect and reconnect to the server. but i am looking for a more intuitive way than this. 

    here is the code i am using to update the connection strings for compatibility 1100 and 1400 databases/cubes

    $newConnectionString = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;"

    $AS = New-Object Microsoft.AnalysisServices.Server $AS.connect("$Server") $cubeName = $Analysis_Server.Databases.FindByName($Cube) $compatibility_lvl = $cubeName.CompatibilityLevel if ($compatibility_lvl -lt 1200) #1103 { $cubeName.DataSources[0].ConnectionString = $newConnectionString $cubeName.DataSources[0].Update() $lt1200 = $($cubeName.DataSources[0].ConnectionString) Write-Host "$lt1200`r`n" -Fore yellow } else { $TAS = new-Object Microsoft.AnalysisServices.Tabular.Server $TAS.Connect("$Server") $TAS.Databases[$Cube].model.datasources[0].ConnectionString = $newConnectionString $TAS.Databases[$Cube].Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull) $gt1200 = $($TAS.Databases[$Cube].model.datasources[0].ConnectionString) Write-Host "$gt1200`r`n" -Fore yellow }


    this is what i get as output

    Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;

    i should only get back this as output:

    Connection Timeout=120;User Id=UID1;Data Source=datasource.com;



    • Edited by cataster Thursday, July 4, 2019 5:12 AM
    Thursday, July 4, 2019 5:08 AM

Answers

  • Hi cataster,

    Based on my research, the two commands would work for you.

    $AS.Databases.FindByName($A).DataSources[0].Refresh()
    
    
    
    $TAS.Refresh($TAS.Databases[$Cube].model.datasources[0])

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 10, 2019 7:11 AM

All replies

  • Hi cataster,

    Here is the example code for your reference.

    $newConnectionString = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;"
    
    
    Write-Host ($newConnectionString -replace (($newConnectionString -split ";")[3]+";")) -Fore yellow

    In fact, if you could choose windows authentication for the data source, you don't have to provide useId and password again. 

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 4, 2019 8:49 AM
  • Hi cataster,

    Here is the example code for your reference.

    $newConnectionString = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;"
    
    
    Write-Host ($newConnectionString -replace (($newConnectionString -split ";")[3]+";")) -Fore yellow

    In fact, if you could choose windows authentication for the data source, you don't have to provide useId and password again. 

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Will, I am not looking for to replace the $newConnectionString...

    The reason I want to output /display the connection is because I want to verify that way that the connection has been changed correctly. 

    This solution does not help verify that because it's just outputting the connection string defined in the script...

    The solution has to involve these statements: 

    $lt1200 = $($cubeName.DataSources[0].ConnectionString)

    $gt1200 = $($TAS.Databases[$Cube].model.datasources[0].ConnectionString)

    If I disconnect the server then reconnect again, these statements will print the connection string without the password because by default the password is not supposed to be visible (MS docs)

    Instead of doing that, I want an intuitive way to refresh the server or something directly so that maybe the password will not be included ...
    Thursday, July 4, 2019 1:50 PM
  • Hi cataster,

    Please add these statements to your script and see if it works for you.

    $newConnectionString = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;"
    
    
    $AS = New-Object Microsoft.AnalysisServices.Server  
    $AS.connect("$Server")
    
    $cubeName = $Analysis_Server.Databases.FindByName($Cube)
    $compatibility_lvl = $cubeName.CompatibilityLevel
    
    if ($compatibility_lvl -lt 1200) #1103
    {
    	$cubeName.DataSources[0].ConnectionString = $newConnectionString
    	$cubeName.DataSources[0].Update()
    	
        # Add these statements to your code script
        $AS.Disconnect()
        $AS=New-Object Microsoft.AnalysisServices.Server
        $AS.Connect("$Server")
        $cubeName = $Analysis_Server.Databases.FindByName($Cube)
        # end 
    					
    	$lt1200 = $($cubeName.DataSources[0].ConnectionString)
    	Write-Host "$lt1200`r`n" -Fore yellow
    }
    else
    {
    	$TAS = new-Object Microsoft.AnalysisServices.Tabular.Server
    	$TAS.Connect("$Server")
    						
    	$TAS.Databases[$Cube].model.datasources[0].ConnectionString = $newConnectionString
    	$TAS.Databases[$Cube].Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)
       
        # Add these statements to your code script
        $TAS.Disconnect()
        $TAS= new-Object Microsoft.AnalysisServices.Tabular.Server
        $TAS.Connect("$Server")
        #end	
    	
    	$gt1200 = $($TAS.Databases[$Cube].model.datasources[0].ConnectionString)
    
    	Write-Host "$gt1200`r`n" -Fore yellow
    }
    
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 5, 2019 7:38 AM
  • Hi cataster,

    Please add these statements to your script and see if it works for you.

    $newConnectionString = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;"
    
    
    $AS = New-Object Microsoft.AnalysisServices.Server  
    $AS.connect("$Server")
    
    $cubeName = $Analysis_Server.Databases.FindByName($Cube)
    $compatibility_lvl = $cubeName.CompatibilityLevel
    
    if ($compatibility_lvl -lt 1200) #1103
    {
    	$cubeName.DataSources[0].ConnectionString = $newConnectionString
    	$cubeName.DataSources[0].Update()
    	
        # Add these statements to your code script
        $AS.Disconnect()
        $AS=New-Object Microsoft.AnalysisServices.Server
        $AS.Connect("$Server")
        $cubeName = $Analysis_Server.Databases.FindByName($Cube)
        # end 
    					
    	$lt1200 = $($cubeName.DataSources[0].ConnectionString)
    	Write-Host "$lt1200`r`n" -Fore yellow
    }
    else
    {
    	$TAS = new-Object Microsoft.AnalysisServices.Tabular.Server
    	$TAS.Connect("$Server")
    						
    	$TAS.Databases[$Cube].model.datasources[0].ConnectionString = $newConnectionString
    	$TAS.Databases[$Cube].Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)
       
        # Add these statements to your code script
        $TAS.Disconnect()
        $TAS= new-Object Microsoft.AnalysisServices.Tabular.Server
        $TAS.Connect("$Server")
        #end	
    	
    	$gt1200 = $($TAS.Databases[$Cube].model.datasources[0].ConnectionString)
    
    	Write-Host "$gt1200`r`n" -Fore yellow
    }
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    These statements are reconnection to the server...

    I know how to do that, I am.asking for a different way so that I dont have to reconnect to the server like this. 

    I am looking for something like a refresh command statement 

    Saturday, July 6, 2019 5:31 AM
  • These statements are reconnection to the server...

    I know how to do that, I am.asking for a different way so that I dont have to reconnect to the server like this. 

    I am looking for something like a refresh command statement 

    You meant to add this ?

      $cubeName.DataSources[0].ConnectionString = $newConnectionString
    	$cubeName.DataSources[0].Update()
    	
        # Add these statements to your code script
        $AS.Dispose()
        $AS.connect("$Server")
        # end 
    					
    	$lt1200 = $($AS.Databases.FindByName($Cube).DataSources[0].ConnectionString)
    	Write-Host "$lt1200`r`n" -Fore yellow

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 9, 2019 9:23 AM
  • For (TAB) compatibility level 1400+ there's no ConnectionString property on DataSource , it's stored as ConnectionDetails property in JSON format object
    Microsoft.AnalysisServices.Tabular.StructuredDataSource
    Microsoft.AnalysisServices.Tabular.ConnectionDetails
    Microsoft.AnalysisServices.Tabular.ConnectionAddress
    $srv.Databases[0].Model.DataSources[0].ConnectionDetails.Address
    So if you {get;set;} ConnectionString property which doesn't exist - anomalies are obviously expected.

    To get currently committed property value instead of reading assigned one (to a variable via operator =) from the object - get property directly via full or partial path (instead of operating with assigned variable objects which so far are just virtual modifiable structures and can be whatever is assigned to them, including different types)

    And yes - in order to make sure your session copy matches available publicly from server it's better to execute refresh (or whatever it's called in Tabular)
    Tuesday, July 9, 2019 1:35 PM
  • And yes - in order to make sure your session copy matches available publicly from server it's better to execute refresh (or whatever it's called in Tabular)

    yes, this is what im talking about. the question is, HOW do i refresh so that i dont have to disconnect and reconnect from the server? i can easily dispose and connect again and i wouldnt get the password but i want a more intuitive way, such as simply refreshing the connection

    $($AS.Databases.FindByName($Cube).DataSources[0].ConnectionString).refresh()
    
    
    $($TAS.Databases[$Cube].model.datasources[0].ConnectionString).refresh()

    but i know that refresh method doesnt exist and thats why i need help here on what can i do 


    Tuesday, July 9, 2019 2:28 PM
  • Hi cataster,

    Based on my research, the two commands would work for you.

    $AS.Databases.FindByName($A).DataSources[0].Refresh()
    
    
    
    $TAS.Refresh($TAS.Databases[$Cube].model.datasources[0])

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 10, 2019 7:11 AM
  • Hi cataster,

    Based on my research, the two commands would work for you.

    $AS.Databases.FindByName($A).DataSources[0].Refresh()
    
    
    
    $TAS.Refresh($TAS.Databases[$Cube].model.datasources[0])

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thank you, this works! this is what i was talking about the whole time!

    Monday, July 15, 2019 4:00 PM