CODEWITHSUNDEEP
powershell
Cataster
Cataster

Reputation: 3541

How to replace Password characters with * in connection string?

I have not received the right help on this question

so i am posting here for another option i have in mind:

i have the following script that changes connection strings of level 1100 and 1400 cubes/databases

$newConnectionString = "Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;Persist Security Info=True;Session Character Set=UTF8"

$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
}

from these statements:

$lt1200 = $($cubeName.DataSources[0].ConnectionString)
Write-Host "$lt1200`r`n" -Fore yellow

$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;Persist Security Info=True;Session Character Set=UTF8

i should only get back this as output:

Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Persist Security Info=True;Session Character Set=UTF8

since i cant find a way to refresh the data source except by reconnecting to the server and print out the connection string without password, i am looking to regex replace the password with the following scenarios:

  1. replace the password value with all stars

Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=********;Persist Security Info=True;Session Character Set=UTF8

  1. keep the first and last characters of the password value but replace the middle with all stars

Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=p*******3;Persist Security Info=True;Session Character Set=UTF8

  1. keep the 1st 3 values of the password and replace the rest with stars

Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=pas********;Persist Security Info=True;Session Character Set=UTF8

i know it would be something like this, but i am not sure what the regex would be for above scenarios:

$lt1200 = $($cubeName.DataSources[0].ConnectionString) -Replace($_ "Password=*?;", "Password=********");

Upvotes: 0

Views: 1143

Answers (2)

Jakobii
Jakobii

Reputation: 606

You probably should look into the SqlConnectionStringBuilder .net class. it can parse connection strings and convert them into object. you wont need regex to find the password and you can replace the password with whatever you'd like easily.

$builder = [System.Data.SqlClient.SqlConnectionStringBuilder]::New('Connection Timeout=120;User Id=UID1;Data Source=datasource.com;Password=password123553;')

$builder.Password

Upvotes: 1

Jakobii
Jakobii

Reputation: 606

replace with same length as password. not very elegant but it works

function Hide-ConnectionStringPassword {
    param(
        [string]$ConnectionString
    )

    $re = [regex]::new("Password=(.*);")
    $match = $re.Match($ConnectionString)

    [string]$password = $match.Groups[1].Value
    [string]$stars = "*" * $password.Length
    return $ConnectionString -replace 'Password=.*;', "Password=$stars;"
}

Hide-ConnectionStringPassword "Source=datasource.com;Password=password123553;"

outputs:

Source=datasource.com;Password=**************;

Upvotes: 1

Related Questions