How to Automate Connection to the Configuration Database of Connectivity Hub

Procedure

To automate connection to the configuration database of Connectivity Hub, use the following procedure after installing Connectivity Hub:

1. Create a PowerShell script (ConnectToCHDB.ps1) file and copy/paste the following content into it:

   DBConnectionScript
   Copy

   Param(
       [Parameter(Mandatory=$true)]    
       [string]$User,
       
       [Parameter(Mandatory=$true)]    
       [string]$Password,
    
       [Parameter()]     
       [string]$DBServer = $env:COMPUTERNAME,
       
       [Parameter(Mandatory=$true)]     
       [string]$DBName,
   
       [Parameter()]     
       [string]$DBUser,
   
       [Parameter()]        
       [string]$DBPassword
   )
   
   if ($DBUser -eq "" -or $DBUser -eq $null)
   {
       $DBAuthMode = "Service"}
   else
   {
       $DBAuthMode = "Impersonate"}  
   
   $CHServer = "http://localhost:55001"  
   $baseAPIUrl = "$CHServer/api/databaseConnections/"$connectionRequest = "undefined?dbOperation=Connect" 
   
   $securePassword = ConvertTo-SecureString $Password -AsPlainText -force    
   $credential = New-Object -Typename System.Management.Automation.PSCredential -ArgumentList $User,$securePassword
   
   $body = @{dbAuth=$DBAuthMode
       type="ConfigurationDatabase"    dbConnMode="Basic"    displayConnectionString=""    dbServer=$DBServer
       dbName=$DBName
       dbUser=""    dbPass=""} | ConvertTo-Json
       
   $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"    
   $headers.Add("Content-Length", $body.Length)
   $headers.Add("Accept", "application/json, text/javascript, */*; q=0.01")
   $headers.Add("Content-Type", "application/json")
   $reqUrl = $baseAPIUrl+$connectionRequest    
   
   Write-Host "Connecting to $DBName database on $DBServer server..."$connectResult = Invoke-RestMethod -Method 'Post' -Uri $reqUrl -Credential $credential -Headers $headers -Body $body 
   
   # verify if database needs update
   $reqUrl = "$CHServer/api/healthcheck/dbversion" 
   $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"   
   $headers.Add("Accept", "application/json, text/javascript, */*; q=0.01")
   
   Write-Host "Check if database needs upgrade..."try {
      Invoke-RestMethod -Method 'Get' -Uri $reqUrl -Credential $credential -Headers $headers
   } catch {
       # Dig into the exception to get the Response details.
       Write-Host "StatusCode:" $_.Exception.Response.StatusCode.value__ 
       Write-Host "StatusDescription:" $_.Exception.Response.StatusDescription
       $exception = $_.ErrorDetails
       if ($exception.Message.Contains("requires update"))
       {
           Write-Host "database needs upgrade..."        $reqUrl = "$CHServer/api/DatabaseConnections?type=ConfigurationDatabase"        Write-Host "Get config database..."        $dbconfig = Invoke-RestMethod -Method 'Get' -Uri $reqUrl -Credential $credential -Headers $headers
   
           $dbId = $dbconfig.id
           $configJson = $dbconfig  | ConvertTo-Json
   
           $headers.Add("Content-Length", $configJson.Length)
           $headers.Add("Content-Type", "application/json")
   
           $reqUrl = "$CHServer/api/DatabaseConnections/$dbId`?dbOperation=Upgrade"        Write-Host "Update config database..."        Write-Host "Calling $reqUrl"        try {
           Invoke-RestMethod -Method 'PUT' -Uri $reqUrl -Credential $credential -Headers $headers  -Body $configJson
           } catch {
               Write-Host "StatusCode:" $_.Exception.Response.StatusCode.value__ 
               Write-Host "StatusDescription:" $_.Exception.Response.StatusDescription
               Write-Host "ErrorDetails:" $_.ErrorDetails.Message
           }
   
           #get and update cache dbs
           $reqUrl = "$CHServer/api/DatabaseConnections?type=CacheDatabase" 
           $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"   
           $headers.Add("Accept", "application/json, text/javascript, */*; q=0.01")
   
           Write-Host "Getting all cache databases..."        $cachedbs = Invoke-RestMethod -Method 'Get' -Uri $reqUrl -Credential $credential -Headers $headers
           $count = $cachedbs.Length
           Write-Host "Found $count cache databases..."        Foreach($cachedb in $cachedbs)
           {
               $dbId = $cachedb.id
               $configJson = $cachedb  | ConvertTo-Json
   
               $headers.Add("Content-Length", $configJson.Length)
               $headers.Add("Content-Type", "application/json")
   
               $reqUrl = "$CHServer/api/DatabaseConnections/$dbId`?dbOperation=Upgrade"            Write-Host "Update cache database: $dbId..."            Write-Host "Calling $reqUrl"            try {
                   Invoke-RestMethod -Method 'PUT' -Uri $reqUrl -Credential $credential -Headers $headers  -Body $configJson
               } catch {
                   Write-Host "StatusCode:" $_.Exception.Response.StatusCode.value__ 
                   Write-Host "StatusDescription:" $_.Exception.Response.StatusDescription
                   Write-Host "ErrorDetails:" $_.ErrorDetails.Message
               }
           }  
       }
   }
   Write-Host "Installation completed" -ForegroundColor "green"

2. Launch a PowerShell command line prompt as an Administrator.
   
   

3. Navigate to the folder where the ps1 file was created.
   
   
4. Execute the script by running the following command, and note:
   1. The DBServer parameter is optional.
      1. The default value is the local computer name.
   2. The DBUser and DBPassword parameters are optional, and can be specified to connect to the database using the Impersonate Authentication mode. 

.\ConnectToCHDB.ps1 -User "Service_Account_Name" -Password "Service_Account_Password" -DBName "Configuration_Database" -DBServer "Database_Server_Name"