QVD Encryption

You can encrypt sensitive data in QVD files with customer supplied key pairs which allows you to control who gets access to your data. The encryption keys are managed through certificates, that must be stored in a certificate store for the user running the QlikView Distribution Service (QDS).

The encryption is configured in the settings.ini file where encryption is enabled and the certificate thumbprint is added. QVD encryption is not enabled by default.

The engine reads and then uses the thumbprint to get the key from the Windows CNG key store. The engine then generates a new data encryption key (DEK) which is used to encrypt the data.

The following is encrypted:

• Data (tables and fields)

The QVD header is not encrypted. Encryption parameters are stored in the QVD header as extra meta-data.

Older versions of Qlik Sense and QlikView returns an error when reading encrypted QVDs files.

Encryption certificates overview

Encryption keys are best managed through certificates. The certificates must be stored in a certificate store for the user running the QlikView Distribution Service (QDS).

The encryption certificate functions as a shell around the encryption key. The key can be fetched even if the certificate has expired, and therefore there is no need to renew an expired encryption certificate.

Encryption keys

The encryption solution uses two types of keys:

• Data encryption keys
• Key encryption keys

Data encryption keys

Data encryption keys (DEK) are auto-generated keys for AES-256 encryption of the data. A new key is generated for each object that is encrypted.

Key encryption keys

Key encryption keys (KEK) are private and public key pair for secure, asymmetric encryption of the data encryption keys. The public key is used to encrypt the data and the private key is used to decrypt the data encrypted by the public key.

The key used for key encryption is specified in the settings.ini file. It is stored in a Microsoft Cryptography Next Generation (CNG) Key Storage Provider. It is contained in a certificate stored in a Windows Certificate Store.

Using QVD encryption

This is the common workflow for using the QVD encryption feature in QlikView.

1. Create an encryption certificate: Creating encryption certificates using Windows PowerShell
2. Enable QVD encryption and specify the key: Enabling QVD encryption
3. For multi-node deployments, export the encryption certificate: Exporting encryption certificates using Windows PowerShell
4. For multi-node deployments, import the encryption certificate on all nodes: Importing encryption certificates using Windows PowerShell

Encrypting QVD files shared with Qlik Sense

If you have QVD files used in both QlikView and Qlik Sense Enterprise on Windows, make sure that the same thumbprint is defined for both products.

Enabling QVD encryption

The Qlik associative engine is configured by defining the encryption key thumbprint in the settings.ini file. Enable QVD encryption by defining enableEncryptQvd=1. Then copy the value of the Thumbprint field from the certificate and paste it into the encryptionKeyThumbprint field in settings.ini.

Do the following:

1. Open the Certificate Manager tool (certmgr.msc).
2. Locate the certificate.
3. Right click the certificate and select Open.

4. On the Details tab, select the Thumbprint field and copy the value.

5. Locate the appropriate settings.ini file.

   For QlikView Desktop, the settings.ini file can be found in C:\Users\<user>\AppData\Roaming\QlikTech\QlikView.

   For QlikView Server, the settings.ini file can be found in C:\Windows\System32\config\systemprofile\AppData\Roaming\QlikTech\QlikViewBatch.

6. In the settings.ini file, enable QVD encryption: enableEncryptQvd=1. Then paste the Thumbprint value into the encryptionKeyThumbprint field.

   Example:  

   enableEncryptQvd=1 encryptionKeyThumbprint=563888bb6aea55eb0d33d9d8b909e0d2ef26ffbd

7. Save the settings.ini file.

QlikView accepts Secure Hash Algorithm 1 (SHA-1) thumbprints in the 40-digit hexadecimal string form without spaces.

Example:  

If your certificate thumbprint contain spaces, like 56 38 88 bb 6a ea 55 eb 0d 33 d9 d8 b9 09 e0 d2 ef 26 ff bd, you enter it in the encryptionKeyThumbprint field as follows:

Managing encryption certificates

There are many tools available for managing certificates but this documentation will focus on creating and distributing certificates using Windows PowerShell and Microsoft Management Console.

If other tools are used, the requirements are:

• a RSA key is used
• the key is stored in a CNG KeyStorageProvider
• the certificate is stored in a certificate store for the user running the Engine

Creating encryption certificates using Windows PowerShell

It is not necessary to use certificates issued by a certificate authority (CA), you can also issue and sign your own self-signed certificates. Encryption certificates that you create must be stored in a certificate store for the user running the QlikView Distribution Service (QDS).

To create the new encryption certificate, use the New-SelfSignedCertificate cmdlet to create a self-signed certificate.

Syntax: Windows Server 2016 and later

Syntax: Windows Server 2012 R2

New-SelfSignedCertificate cmdlet parameters Windows Server 2016 and later

The following parameters should at minimal be defined when creating the certificate using PowerShell for Windows Server 2016 and later.

-Subject

Specifies the string that appears in the subject of the new certificate. This cmdlet prefixes CN= to any value that does not contain an equal sign. For multiple subject relative distinguished names (also known as RDNs), separate each subject relative distinguished name with a comma (,). If the value of the relative distinguished name contains commas, separate each subject relative distinguished name with a semicolon (;).

-Subject <Certifcate name>

-KeyAlgorithm

Specifies the name of the algorithm that creates the asymmetric keys that are associated with the new certificate. Must be RSA.

-KeyAlgorithm RSA

-KeyLength

Specifies the length, in bits, of the key that is associated with the new certificate.

-KeyLength <Key length, e.g.4096>

-Provider

Specifies the name of the KSP or CSP that this cmdlet uses to create the certificate. Should be Microsoft Software Key Storage Provider.

-Provider "Microsoft Software Key Storage Provider"

-KeyExportPolicy

Specifies the policy that governs the export of the private key that is associated with the certificate. The acceptable values for this parameter are:

• Exportable
• ExportableEncrypted (default)
• NonExportable

-KeyExportPolicy ExportableEncrypted

-CertStoreLocation

Specifies the certificate store in which to store the new certificate. If the current path is Cert:\CurrentUser or Cert:\CurrentUser\My, the default store is Cert:\CurrentUser\My. Otherwise, you must specify Cert:\CurrentUser\My for this parameter.

-CertStoreLocation "cert:\CurrentUser\My"

New-SelfSignedCertificate cmdlet parameters Windows Server 2012 R2

The following parameters should at minimal be defined when creating the certificate using PowerShell for Windows Server 2012 R2.

-DnsName

Specifies one or more strings to put into the Subject Alternative Name extension of the certificate. The first DNS name is also saved as Subject Name and Issuer Name.

-DnsName <Certifcate name>

-CertStoreLocation

Specifies the certificate store in which to store the new certificate. If the current path is Cert:\CurrentUser or Cert:\CurrentUser\My, the default store is Cert:\CurrentUser\My. Otherwise, you must specify Cert:\CurrentUser\My for this parameter.

-CertStoreLocation "cert:\CurrentUser\My"

New-SelfSignedCertificate defaults Windows Server 2012 R2

The following defaults apply for the New-SelfSignedCertificate cmdlet in Windows Server 2012 R2:

• Key algorithm: RSA
• Key length: 2048
• Extended key usage (EKU): Client authentication and Server authentication
• Key usage: Digital signature, Key encipherment (a0)
• Validity: one year

Example: creating a data encryption certificate using PowerShell for Windows Server 2016 and later

In this example, the user called test is creating a self-signed exportable encrypted certificate with the subject MyTestCert and a key length of 4096 bits. The certificate is to be stored in Cert:\CurrentUser\My.

Type the following command in Microsoft PowerShell:

PS C:\Users\test> New-SelfSignedCertificate -Subject MyTestCert -KeyAlgorithm RSA -KeyLength 4096 -Provider "Microsoft Software Key Storage Provider" -KeyExportPolicy ExportableEncrypted -CertStoreLocation "cert:\CurrentUser\My"

Result:

When the certificate has been created, the following is displayed in Microsoft PowerShell:

Exporting encryption certificates using Windows PowerShell

To export a encryption certificate, use the Export-PfxCertificate cmdlet.

Syntax:  

Export-PfxCertificate cmdlet parameters

The following parameters should at minimal be defined when exporting the certificate.

-cert

Specifies the path to the certificate to be exported.

-cert cert:\currentuser\My\<certificate thumbprint>

-FilePath

Specifies the path for the PFX file to be exported.

-FilePath <FileName>.pfx

-Password

Specifies the password used to protect the exported PFX file. The password should be in the form of secure string. This parameter must be specified, or an error will be displayed.

-Password <Password or variable>

Example: exporting a data encryption certificate

In this example the user called test will export the encryption certificate previously created to a PFX file.

1. First, create a secure string of the plain text password string and store it in the $mypwd variable. For this he is using the ConvertTo-SecureString cmdlet.

   Type the following command in Microsoft PowerShell:

   PS C:\Users\test> $mypwd = ConvertTo-SecureString -String "MyPassword" -Force -AsPlainText

2. Then proceed with the actual exporting of the encryption certificate with thumbprint 563888bb6aea55eb0d33d9d8b909e0d2ef26ffbd using the Export-PfxCertificate cmdlet. The password variable created in the previous step is called to protect the exported PFX file. Type the following command in Microsoft PowerShell:

   PS C:\Users\test> Export-PfxCertificate -cert cert:\currentuser\My\563888bb6aea55eb0d33d9d8b909e0d2ef26ffbd -Filepath MyTestCert.pfx -Password $mypwd

Result:

When the certificate has been exported, the following is displayed in Microsoft PowerShell:

Backing up encryption certificates using Microsoft Management Console

You should always have a back up of the certificate. If the certificate is lost from the server, or in case of a hard disk failure, you may not be able to open your encrypted app. It is your responsibility to keep safe the certificate backup for as long as it is needed.

You can use the same procedure as for exporting when backing up your certificate, see Exporting encryption certificates using Windows PowerShell.

Another way of backing up your encryption certificates is to do it with Microsoft Management Console. The below example shows how to export or back up your SSL certificate with a private key using Microsoft Management Console.

Do the following:

1. On the Windows Server where the SSL certificate is installed, open the Microsoft Management Console: type mmc in the Windows search menu and open it.
2. In the Console window, click File > Add/Remove Snap-in.
3. In the Add or Remove Snap-ins window, select Certificates from the Available snap-ins pane on the left side and then click Add >.
4. In the dialog, select My user account and then click Next.
5. In the Add or Remove Snap-ins window, click OK.

6. In the Console window, in the Console Root pane on the left side, expand Certificates (Current user) and locate the certificate that you want to export or back up.

7. In the center pane, right-clock on the certificate that you want to export or back up, and then click All Tasks > Export.
8. In the Certificate Export Wizard, on the Welcome to the Certificate Export Wizard page, click Next.
9. On the Export Private Key page, select Yes, export the private key, and then click Next.

10. On the Export File Format page, select Personal Information Exchange – PKCS #12 (.PFX) and then check Include all certificates in the certification path if possible.

    Warning noteDo not select Delete the private key if the export is successful.

    Click Next.

11. On the Security page, check the Password box, then create and confirm the password.

    Information noteThis password will be required when you import or restore the certificate with private key.

    Then check the Group or user name box. If applicable, select the Active Directory user or group account to which you want to assign access to the certificate with private key. Then click Add.

    Click Next.

12. On the File to Export page, click Browse to specify the save location and the file name of the back up file and then click Save.

    Back on the File to Export page, click Next.

13. On the Completing the Certificate Export Wizard page, verify that the settings are correct and then click Finish.
14. You should receive a message stating that the export was successful, and the SSL certificate with private key is now saved to the location that you selected .

Importing encryption certificates using Windows PowerShell

To import an encryption certificate on for example other machines, use the Import-PfxCertificate cmdlet.

Syntax:  

Import-PfxCertificate cmdlet parameters

The following parameters should at minimal be defined when importing the certificate.

-CertStoreLocation

Specifies the path of the store to which certificates will be imported. If this parameter is not specified, then the current path is used as the destination store.

-CertStoreLocation cert:\currentuser\My

-FilePath

Specifies the path for the PFX file.

-FilePath <FileName>.pfx

-Exportable

Optional.

Specifies whether the imported private key can be exported. If this parameter is not specified, then the private key cannot be exported.

-Exportable

-Password

Specifies the password for the imported PFX file in the form of a secure string.

-Password $mypwd

Example: importing a data encryption certificate

In this example the user called test2 will import the encryption certificate with thumbprint 563888BB6AEA55EB0D33D9D8B909E0D2EF26FFBD previously exported to a PFX file.

1. First, create a secure string of the plain text password string and store it in the $mypwd variable. For this he is using the ConvertTo-SecureString cmdlet.

   Type the following command in Microsoft PowerShell:

   PS C:\Users\test2>  $mypwd = ConvertTo-SecureString -String "MyPassword" -Force -AsPlainText

2. Then proceed with the actual importing of the PFX file using the Import-PfxCertificate cmdlet. The password variable created in the previous step is called to access the PFX file. Type the following commands in Microsoft PowerShell:

   PS C:\Users\test2>  Import-PfxCertificate -CertStoreLocation cert:\currentuser\My -FilePath MyTestCert.pfx -Exportable -Password $mypwd

Result:

When the certificate has been exported, the following is displayed in Microsoft PowerShell:

Restoring encryption certificates using Microsoft Management Console

You can use the same procedure as for importing when restoring your certificate, see Importing encryption certificates using Windows PowerShell.

If you backed up your certificate using Microsoft Management Console, as described in Backing up encryption certificates using Microsoft Management Console, then follow the example below to restore your SSL certificate.

Do the following:

1. On the Windows Server where you want to install the SSL certificate, open the Microsoft Management Console: type mmc in the Windows search menu and open it.
2. In the Console window, click File > Add/Remove Snap-in.
3. In the Add or Remove Snap-ins window, select Certificates from the Available snap-ins pane on the left side and then click Add >.
4. In the dialog, select My user account and then click Next.
5. In the Add or Remove Snap-ins window, click OK.

6. In the Console window, in the Console Root pane on the left side, expand Certificates (Current user), right-click on the Personal folder, and then select All Tasks > Import.

7. In the Welcome to the Certificate Import Wizard window, click Next.

8. On the File to import page, Click Browse to locate and select the PFX file that you want to import, and then click Next.

   Information noteMake sure to select All files (*.*) in the file type drop-down of the File Explorer window, as it by default is set to search for X.509 Certificate (*.cert,*.crt) file types only.

9. On the Private key protection page, type the password that was created when the SSL certificate was exported / backed up.

   Then check the Mark this key as exportable box. This means you can back up or export the SSL certificate when needed.

   Then also check the Include all extended properties box.

   Click Next.

10. On the Certificate Store page, select Place all certificates in the following store and then click Browse.

    In the Select Certificate Store window, select Personal and click OK.

    Back on the Certificate Store page, click Next.

11. Verify that all settings are correct on the Completing the Certificate Import Wizard page, and then click Finish.
12. You should receive a message stating that the import was successful, and the SSL certificate with private key is now saved to the Personal store (folder).