Trying to replace connection string settings with Powershell

Question

I’m an amateur at Powershell but I’m trying to figure out a way to use Powershell to find a connection string value using a wild card and replace it with a whole new connection string. A colleague and I threw together a script to find a server name inside of a connection string and replace it with a new server name. Now I need to replace the whole contents inside the string with a whole new one. Bonus is we would like to do this for both a xml config file and a json config file.

I would first like to search for a specific text so that I can find only the connection that pertains to a specific database. Once that is found it want to replace the whole text inside of specific wild card pattern.

Example: Look for the value of “SpecificServiceName” in

<add name="DbConnection" connectionString="USER ID=SomeId;PASSWORD=abc123;DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername.mydomain.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SpecificServiceName)));PERSIST SECURITY INFO=True" providerName="Oracle.ManagedDataAccess.Client" />

If that is found then I would like to replace everything inside of the connectionString value with something new. So, look for connectionString=”*” and replace the “*” with

"USER ID=myNewUserId;PASSWORD=newPassword;DATA SOURCE=(DESCRIPTION_LIST = (LOAD_BALANCE = off)(FAILOVER = on)(DESCRIPTION =(CONNECT_TIMEOUT = 5)(TRANSPORT_CONNECT_TIMEOUT = 3)(RETRY_COUNT = 3)(ADDRESS_LIST =(LOAD_BALANCE = on)(ADDRESS = (PROTOCOL = TCP)(HOST = primaryserver.domain.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = SERVICENAME_SVC)))(DESCRIPTION =(CONNECT_TIMEOUT = 5)(TRANSPORT_CONNECT_TIMEOUT = 3)(RETRY_COUNT = 3)(ADDRESS_LIST =(LOAD_BALANCE = on)(ADDRESS = (PROTOCOL = TCP)(HOST = drserver.domain.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = SERVICENAME_SVC))));PERSIST SECURITY INFO=True" providerName="Oracle.ManagedDataAccess.Client"

Any suggestions or examples you can point me to for something like this?

Here is what we used to find a specific server name inside a connection string. It works well for just replacing a server name but I’m trying to do something similar but with the above scenario.

#After this runs, run it again to double check that all the words to replace were actually replaced. You can just run it, and then choose n, or close the script and check the ResultFile.csv. If its empty, then all of them should be updated.
 
$folderPath = "D:\Inetpub\websites"
$wordToReplace = "oldServerName"
$wordToUpdate = "newServerName"
$exportCSVPath = "D:\Script Files\ResultFile.csv"
 
#Send list of files to csv
$filesToUpdate = Get-ChildItem -Path $folderPath -include ('*.config', '*.json') -Recurse | Select-String $wordToReplace -List | Select Path | Export-CSV $exportCSVPath
 
 
#Ask for validation
Write-Output "Replacing $($wordToReplace) with $($wordToUpdate) in $($folderPath)"
$response = Read-Host "Check D:\ResultFile.csv and make sure everything listed should be updated! Confirm (y/n)"
 
#If response = y
if($response -eq "y"){
 
#Get list again, for some reason, the above list gets deleted...maybe because we are sending to CSV?
$files = Get-ChildItem -Path $folderPath -include ('*.config', '*.json') -Recurse | Select-String $wordToReplace -List | Select Path
 
#Print out each file name and update
foreach($file in $files){
Write-Output "Updating file: $($file.Path)"
 
#probably a better way to do upper vs lower, but didnt spend time researching...for some reason this isnt case insensitive, but the above ones are...
Get-ChildItem $file.Path -Recurse | ForEach {(Get-Content $_).Replace($wordToReplace.ToUpper(), $wordToUpdate)  |  Set-Content $_ }
 
Get-ChildItem $file.Path -Recurse | ForEach {(Get-Content $_).Replace($wordToReplace.ToLower(), $wordToUpdate)  |  Set-Content $_ }
}
}
else{
Write-Output "Update Aborted"
}
 
#just pauses to see output
$response = Read-Host

Appsettings Example

    {
      "FolderLocation": {
        "Input": "D:\\ImportFiles\\AppName",
        "Export": "D:\\ImportFiles\\AppName\\Export",
      },
      "FileName": {
        "Input": "InputFileName.csv",
        "Export": "ExportFileName.txt"
      },
      "ConnectionStrings": {
        "DbConnection": "user id=MyUserId;password=ABC123;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Server1Name.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyServiceName)))",
"OtherConnection": "user id=MyUserId;password=ABC123;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Server1Name.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyServiceName)))"
      },
      "Logging": {
        "IncludeScopes": false,
        "LogLevel": {
          "Default": "Trace",
          "Microsoft": "Warning",
          "Microsoft.Hosting.Lifetime": "Information"
        }
      },
      "AllowedHosts": "*"
    }

Answer 1

Continuing from my comment, you really should manipulate XML and JSON not as regular plain-text.

Assuming your (XML) .config files look like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="DbConnection" connectionString="USER ID=SomeId;PASSWORD=abc123;DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername.mydomain.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SpecificServiceName)));PERSIST SECURITY INFO=True" providerName="Oracle.ManagedDataAccess.Client" />
  </connectionStrings>
</configuration>

and your .json files are like this:

{
    "configuration": {
        "connectionStrings": {
            "add": {
                "name": "DbConnection",
                "connectionString": "USER ID=SomeId;PASSWORD=abc123;DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername.mydomain.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SpecificServiceName)));PERSIST SECURITY INFO=True",
                "providerName": "Oracle.ManagedDataAccess.Client"
            }
        }
    }
}

Without those Read-Host lines in the code (I think you should do all the user questions BEFORE entering the code), here's how I would do it:

$folderPath          = 'D:\Inetpub\websites'
$exportCSVPath       = 'D:\somewhere\UpdateResults.csv'
$wordToSearch        = 'SpecificServiceName'
$newConnectionString = 'USER ID=myNewUserId;PASSWORD=newPassword;DATA SOURCE=(DESCRIPTION_LIST = (LOAD_BALANCE = off)(FAILOVER = on)(DESCRIPTION =(CONNECT_TIMEOUT = 5)(TRANSPORT_CONNECT_TIMEOUT = 3)(RETRY_COUNT = 3)(ADDRESS_LIST =(LOAD_BALANCE = on)(ADDRESS = (PROTOCOL = TCP)(HOST = primaryserver.domain.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = SERVICENAME_SVC)))(DESCRIPTION =(CONNECT_TIMEOUT = 5)(TRANSPORT_CONNECT_TIMEOUT = 3)(RETRY_COUNT = 3)(ADDRESS_LIST =(LOAD_BALANCE = on)(ADDRESS = (PROTOCOL = TCP)(HOST = drserver.domain.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = SERVICENAME_SVC))));PERSIST SECURITY INFO=True" providerName="Oracle.ManagedDataAccess.Client"'

# get a list of the files
$configFiles = Get-ChildItem -Path $folderPath -Include ('*.config', '*.json') -File -Recurse

$result = foreach ($file in $configFiles) {
    # do NOT use Write-Output because that will mess up the $result
    Write-Host "Processing file '$($file.FullName)'"

    # create a flag to track if the file is updated or not
    $updated = $false
    # prepare an object for the CSV
    $objOut = [PsCustomObject]@{
                  File    = $file.FullName
                  Updated = $updated
                  Status  = $null
              }

    # what filetype?
    if ($file.Extension -eq '.json') {
        try {
            $json = (Get-Content -Path $file.FullName -Raw) | ConvertFrom-Json -ErrorAction Stop
            $json.configuration.connectionStrings.add | Where-Object { $_.connectionString -like "*$wordToSearch*" } | ForEach-Object {
                $_.connectionString = $newConnectionString
                $updated = $true
            }
            if ($updated) { 
                $json | ConvertTo-Json -Depth 99 | Set-Content -Path $file.FullName -Force   # Depth 99 for safety..
            }
            # fill the output objects details
            $objOut.Updated = $updated
            $objOut.Status  = 'OK'
        }
        catch {
            # set the error in the output object
            $objOut.Status  = "Error: $($_.Exception.Message)"
        }
    }
    else {  
        # assume XML
        try {
            $xml = [System.Xml.XmlDocument]::new()
            $xml.Load($file.FullName)
            $xml.DocumentElement.connectionStrings.add | Where-Object { $_.connectionString -like "*$wordToSearch*" } | ForEach-Object {
                $_.connectionString = $newConnectionString
                $updated = $true
            }
            if ($updated) { 
                $xml.Save($file.FullName) 
            }
            # fill the output objects details
            $objOut.Updated = $updated
            $objOut.Status  = 'OK'
        }
        catch {
            # set the error in the output object
            $objOut.Status  = "Error: $($_.Exception.Message)"
        }
    }
    # output the object so it gets collected in variable $result
    $objOut
}

# output on screen
$result

# save the results as CSV file
$result | Export-Csv -Path $exportCSVPath -NoTypeInformation

Needless to say that you should try this on a copy of the contents of the D:\Inetpub\websites folder first!

P.S. PowerShell creates valid JSON, but the format is quite ugly. If you want to prettify that, you can use the function I posted earlier Format-Json

---

Using your example JSON, change this part in the if ($file.Extension -eq '.json') {..} block

 $json.configuration.connectionStrings.add | Where-Object { $_.connectionString -like "*$wordToSearch*" } | ForEach-Object {
    $_.connectionString = $newConnectionString
    $updated = $true
}

into

$json.connectionStrings | Where-Object { $_.DbConnection -like "*$wordToSearch*" } | ForEach-Object {
    $_.DbConnection = $newConnectionString
    $updated = $true
}

---

In case you cannot rely on the element's name being DbConnection, you can use this loop instead:

# get the actual element name and value, filter the values that pass the comparison
$json.connectionStrings.PsObject.Properties | Where-Object { $_.Value -like "*$wordToSearch*" } | ForEach-Object {
    $json.connectionStrings.$($_.Name) = $newConnectionString
    $updated = $true
}