Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)

Andrew_Delaney — Mon, 08 Jul 2024 09:50:12 GMT

The following steps are only applicable to Qlik Sense deployments originally installed with versions prior to the June 2019 release. For any Qlik Sense deployments installed with later versions, follow standard steps for patching Qlik Sense and do not perform the steps in this article.

In these steps we will occasionally ask you to run Powershell code.

Executing PowerShell code:

Copy the code and save it in a ps1 file, in example: certificates_backup.ps1
Open elevated command line and navigate to the location where the script was saved.
Start PowerShell by executing following command: Powershell
Run the script by executing following command: .\<name_of_the_script>.ps1 in example: .\certificates_backup.ps1

Environment

Qlik Sense Enterprise on Windows, June 2019 thru November 2019

Resolution

After upgrading to June 2019 or above, check the Root certificate by running the C2 Validator tool on ALL NODES. If CA and Critical display 'Missing', please follow the instructions below.
Stop all services on ALL NODES in the Qlik Sense cluster.
Back up all current Qlik Sense certificates from the CENTRAL NODE by executing the following PowerShell code:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

function ExportCertificatesFromStore(
    [string] $name,
    [string] $location)
{
    $success = 1
    $oid = "1.3.6.1.5.5.7.13.3"
    
    $localStore = new-object System.Security.Cryptography.X509Certificates.X509Store $name, $location
    $localStore.Open("MaxAllowed")

    $mypwd = ConvertTo-SecureString -String "MyPassword" -Force -AsPlainText

    try
    {
        $certs = $localStore.Certificates
        foreach ($cert in $certs)
        {
            $extensions = $cert.Extensions
            foreach($extension in $extensions)
            {
                if ($extension.Oid.Value.Equals($oid))
                {
                    Get-ChildItem -Path cert:\$($localStore.Location)\$($localStore.Name) | Where-Object { $_.PrivateKey.CspKeyContainerInfo.Exportable } | Export-PfxCertificate -FilePath "$($localStore.Name)_$($localStore.Location).pfx" -Password $mypwd
                    break;
                }
            }
        }
    }

    catch
    {
        write-host "An error occurred while removing certificates" -ForegroundColor Red
        write-host $_.Exception.GetType().FullName -ForegroundColor Red
        write-host $_.Exception.Message -ForegroundColor Red
        $success = 0
    }

    finally
    {
        $localStore.Close()
    }
    
    if ($success -ne 1)
    {
        exit 20
    }
}

function ExportCertificates()
{
    ExportCertificatesFromStore "Root" "LocalMachine"
    ExportCertificatesFromStore "My" "LocalMachine"
    ExportCertificatesFromStore "My" "CurrentUser"
}

ExportCertificates
write-host "Done."
exit 0

NOTE: For information on how to execute above code please refer to “Executing PowerShell code” section at the top. Modify $mypwd variable to define custom password. Make sure certificates were backed up after running the script:

Root_LocalMachine.pfx – root CA
My_LocalMachine.pfx – server
My_CurrentUser – client

NOTE: If you happen to have more certificates with the same values in Issued To, Issued By and Friendly Name columns and you are unable to identify the correct certificate, please refer to Identifying Qlik Sense root CA and server certificates in certificate store.

Remove current Qlik Sense root CA certificate from CENTRAL NODE by executing the following PowerShell code:
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass function RemoveCertificatesFromStore( [string] $name, [string] $location) { $success = 1 $oid = "1.3.6.1.5.5.7.13.3" $localStore = new-object System.Security.Cryptography.X509Certificates.X509Store $name, $location $localStore.Open("MaxAllowed") try { $certs = $localStore.Certificates foreach ($cert in $certs) { $extensions = $cert.Extensions foreach($extension in $extensions) { if ($extension.Oid.Value.Equals($oid)) { write-host "Deleting certificate from" $localStore.Name $localStore.Location write-host " Subject:"$cert.Subject write-host " Issuer:"$cert.Issuer write-host " Serial:"$cert.SerialNumber $localStore.Remove($cert) break; } } } } catch { write-host "An error occurred while removing certificates" -ForegroundColor Red write-host $_.Exception.GetType().FullName -ForegroundColor Red write-host $_.Exception.Message -ForegroundColor Red $success = 0 } finally { $localStore.Close() } if ($success -ne 1) { exit 20 } } function CleanCertificates() { RemoveCertificatesFromStore "Root" "LocalMachine" } CleanCertificates write-host "Done." exit 0
Make sure script completes without any errors. Otherwise remove Qlik Sense root CA certificate manually (please see Recreating Qlik Sense root CA certificate (manual back-up and removal of existing certificates).
Remove all current Qlik Sense certificates from NON-CENTRAL NODES by executing the following PowerShell code:
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass function RemoveCertificatesFromStore( [string] $name, [string] $location) { $success = 1 $oid = "1.3.6.1.5.5.7.13.3" $localStore = new-object System.Security.Cryptography.X509Certificates.X509Store $name, $location $localStore.Open("MaxAllowed") try { $certs = $localStore.Certificates foreach ($cert in $certs) { $extensions = $cert.Extensions foreach($extension in $extensions) { if ($extension.Oid.Value.Equals($oid)) { write-host "Deleting certificate from" $localStore.Name $localStore.Location write-host " Subject:"$cert.Subject write-host " Issuer:"$cert.Issuer write-host " Serial:"$cert.SerialNumber $localStore.Remove($cert) break; } } } } catch { write-host "An error occurred while removing certificates" -ForegroundColor Red write-host $_.Exception.GetType().FullName -ForegroundColor Red write-host $_.Exception.Message -ForegroundColor Red $success = 0 } finally { $localStore.Close() } if ($success -ne 1) { exit 20 } } function CleanCertificates() { RemoveCertificatesFromStore "Root" "LocalMachine" RemoveCertificatesFromStore "My" "LocalMachine" RemoveCertificatesFromStore "My" "CurrentUser" } CleanCertificates write-host "Done." exit 0
Make sure script completes without any errors. Otherwise remove certificates manually (please see Recreating Qlik Sense root CA certificate (manual back-up and removal of existing certificates).
Remove all current Qlik Sense certificates from NON-CENTRAL NODES stored locally by deleting all files from following location:

C:\ProgramData\Qlik\Sense\Repository\Exported Certificates\.Local Certificates
On ALL NODES navigate to C:\Program Files\Qlik\Sense\Repository (or corresponding nondefault location) and open Repository.exe.config file.
Make sure Certificates.SelfSignedRoot.BasicConstraintsCA key has value set to true, in example:

<add key="Certificates.SelfSignedRoot.BasicConstraintsCA" value="true" />

If the above key is not present, add it within <appSettings> section, in example:
(…) <add key="BackgroundWork.CountLimit" value="3" /> <add key="Certificates.SelfSignedRoot.BasicConstraintsCA" value="true" /> <add key="DatabaseCommandTimeout" value="00:01:30" /> (…)
NOTE: If you are installing a patch on November 2018 track, name of the key is: <add key="CertificatesSelfSignedRootBasicConstraintsCA" value="true" />
On the CENTRAL NODE, start Qlik Sense Repository Database service.
On CENTRAL NODE, from an elevated command line navigate to C:\Program Files\Qlik\Sense\Repository (or corresponding nondefault location) and run:
repository.exe -bootstrap -iscentral
When bootstrap mode has reached Entering main startup phase.., start Qlik Sense Service Dispatcher service and make sure that the Bootstrap mode has terminated. Press ENTER to exit.. final message is shown

Note: If this message is not shown, open Windows Task Manager, find Qlik Sense Repository Service in the Processes tab and end it by right-clicking on it and selecting End task.

To make sure new certificate is in use, run the C2 Validator tool,
On CENTRAL NODE restart Qlik Sense Service Dispatcher and start all remaining services.
On the NON-CENTRAL NODES, depending on the setup, perform either step a) or b) below:
- Account running the Qlik Sense services has administrator privileges:
  1. [Applicable ONLY for April 2019 track]: Delete host.cfg file from C:\ProgramData\Qlik\Sense\
  2. Start Qlik Sense Repository Service.
  3. Open the Qlik Management Console (QMC) and redistribute the certificates according to Redistributing a certificate
  4. Restart Qlik Sense Repository Service and start all remaining services on the node to make sure they are using the newly distributed certificates.
- Account running the Qlik Sense service does not have administrator privileges:
  1. [Applicable ONLY for April 2019 track]: Delete host.cfg file from C:\ProgramData\Qlik\Sense\
  2. At the command line, navigate to C:\Program Files\Qlik\Sense\Repository (or corresponding nondefault location), and run:
    repository.exe -bootstrap
  3. When the Waiting for certificates to be installed.. message is displayed, redistribute the certificates according to Redistributing a certificate
Once the bootstrap mode has terminated, start the Qlik Sense Service Dispatcher, then start the Qlik Sense Repository Service, and finally the remaining Qlik Sense services.

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based ba

ken4runner — Thu, 17 Sep 2020 12:55:47 GMT

can you confirm some of the issues that this certificate problem are known to cause?
what are some of the symptoms that the system will exhibit when this issue is present?

thanks - Ken

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)

AkashPohare_HB — Sat, 04 Jan 2025 14:52:43 GMT

Hello @Sonja_Bauernfeind ,

Is it possible to replace the Qlik generated certificates mentioned here(https://help.qlik.com/en-US/sense-admin/November2024/Subsystems/DeployAdministerQSE/Content/Sense_DeployAdminister/QSEoW/Deploy_QSEoW/Backing-up-certificates.htm ) by the third party trusted certificates.

Our cybersecurity team has flagged the Qlik generated certificates as the expiry date is more than 2 years and also those are self signed certificates and must be replaced by the trusted non self signed certificates.

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)

Sonja_Bauernfeind — Tue, 07 Jan 2025 07:36:52 GMT

Hello @AkashPohare_HB

They cannot be replaced as they are needed for service communication. You can replace the certificate used in the front end (the certificate the proxy uses for end-user access), but even then the original certificates must remain as they will still be used between the services.

I recommend you raise an idea for this in our Ideation section.

All the best,
Sonja

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)

AkashPohare_HB — Wed, 08 Jan 2025 10:31:14 GMT

Thanks @Sonja_Bauernfeind for the confirmation.

article Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates) in Official Support Articles

Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)

Environment

Resolution

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based ba

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)

Re: Recreating Qlik Sense root CA certificate when upgrading to June 2019 and above (script based back-up and removal of existing certificates)