Qlik Sense on Windows: Setup Database Traffic Encryption

[Bastien_Laugiero]

Disclaimer: Encrypted communication between PostgreSQL database and Qlik Sense services is a supported setup. This article provides general guidance on how to enable encryption on PostgreSQL database server, but local adjustment must be applied to comply with local IT requirements. Please be aware that Qlik Support can not help setting up Database Traffic Encryption, while Qlik Consulting Services may be utilized for deployment implementation. 

Qlik Sense supports database traffic encryption using SSL/TLS, but it is not enabled by default. The Qlik Sense installer cannot use SSL encryption for establishing a connection to PostgreSQL. When SSL encryption is enabled, the installer does not recognize any already installed PostgreSQL databases, and as a consequence, installation cannot be completed. Password security and local IT policy around certificate need to be considered before enabling database encryption, as the implementation includes manual configuration of the Qlik Sense deployment.

Qlik recommends that the configuration in this section is performed by someone with sufficient skills in PostgreSQL database configuration.

This article covers two scenarios for enabling Database Traffic Encryption;

1. PostgreSQL database installed locally with the Qlik Sense installer
2. Qlik Sense referred to the existing database during the installation 

Upgrades: Prior to Qlik Sense Enterprise August 2022 release, the Qlik Sense installer cannot use SSL encryption for establishing a connection to PostgreSQL. So any upgrades will fail unless you are upgrading to August 2022 and later. Prior to upgrading, disable the encryption. You can enable it again after the upgrade is complete.

See Unable to upgrade Qlik Sense with missing 'SenseServices', 'QSMQ', and 'Licenses' database for respe...

Always take a complete backup of Qlik Sense deployment before altering system configuration, to allow restoring a working state in case of disaster. 

These scenarios apply the default Qlik Sense signed certificate to encrypt traffic for a PostgreSQL database. Qlik Sense signed certificate is commonly only fully trusted by Qlik Sense nodes, which means other usage may not comply with local IT policies.  It is recommended that a fully trusted certificate is used when applying encrypted database traffic for production environments. Consult the local IT department for details on retrieving a fully trusted certificate. 

 

Scenario 1: PostgreSQL database installed locally with the Qlik Sense installer 

This scenario assumes a standard Qlik Sense installation, where the Qlik Sense Repository Database is installed on the Qlik Sense central node as part of the Qlik Sense installation. 

1. Complete installation of Qlik Sense Enterprise on Windows as described in Qlik Sense Help for Administrators: Installing Qlik Sense Enterprise on Windows. 
2. Enable encryption as described in Qlik Sense Help for Administrators: Database traffic encryption

 

Scenario 2: Qlik Sense referred to the existing database during the installation 

This scenario assumes a custom Qlik Sense installation, where Qlik Sense is configured to use a dedicated PostgreSQL database as its Repository Database. 

1. Install and configure a standalone PostgreSQL database server as described in Qlik Sense Help for Administrators: Installing and configuring PostgreSQL 
2. Install Qlik Sense central node connected to an existing repository database as described in Qlik Sense Help for Administrators: Installing Qlik Sense on a single node
3. Install Qlik Sense rim nodes if required as described in Qlik Sense Help for Administrators: Installing Qlik Sense in a multi-node site 
4. Enable encryption PostgreSQL database and all Qlik Sense nodes as described in Qlik Sense Help for Administrators: Database traffic encryption

[QlikMaster1]

Hi,

Having run through the steps for encrypting database traffic after setting scram-sha-256 encryption and making the changes required, but referencing scram-sha-256 instead of md5, the service would not start.

Am I right in assuming that only md5 is currently supported as a database encryption method currently - this was tested on QlikSense August 2022 Patch 3.

I did manage to get the services to start with scram-sha256 with the original pga_hba listing the hosts, but replacing host with hostssl, but the environment could then not connect to any ports, but pgadmin would run and login fine.

[QlikMaster1]

I missed typed and should say that the services did start with the single line for scram-sha256 and stayed up, but when opening the link it tries to authenticate, but then drops out to a 404, I am guessing this is either due to lack of scram support or if your using a ca issued ssl for the site is this needed to be .pem format for the certificate and key ?

[QlikMaster1]

@Sonja_Bauernfeind  just wanted to check as the docs do not mention this, but on the pg_hba section for multi node where replication is in place, id assume you keep that in as its explicitly needed to allow the two or more standalone boxes to replicate, so removing it and leaving the one line I'd think would break things.

Some details on Single vs Multi node config would be good on the database traffic encryption front and also it seems that whilst the doc has been updated for scram-sha256 support it still references md5 on the host file line for config of traffic. This may confuse those thinking only md5 is supported, where I have found scram-sha-256 is supported, just not referenced. 

Finally for those on August 2022 Sense release, I found adding in the ssl enabled support for the license section was also needed to get access to the environment, so likely may well be needed for earlier version, but confirming and updating the doc would be good here too.  Only found this out by checking the repository logs for the environment and license errors appearing till the ssl = required was set in the code snippet and schedule dispatcher restarted.

[Sonja_Bauernfeind]

Hello @QlikMaster1 

Let me take this query to one of our subject matter experts! We'll look into verifying the doc.

All the best,
Sonja 

[Sonja_Bauernfeind]

Hello again @QlikMaster1 

The links in the article were outdated. I have updated them now. Qlik Sense Help for Administrators: Database traffic encryption should give you all the information that you need, including what sections need to be updated.

All the best,
Sonja

[QlikMaster1]

Hi @Sonja_Bauernfeind thanks for getting the links updated, but it seems that step 2 on the article here for setting up database encryption needs updating to include add following line in pg_hba either md5 : 

hostssl all all all md5

or SCRAM-SHA256 :

hostssl all all all scram-sha256

* depending on which encryption method you have chosen to use 🙂

[Sonja_Bauernfeind]

@QlikMaster1 Hello again!

I will get this reviewed and submit a ticket to our documentation team! Thank you.

All the best,
Sonja 

[kumarbala]

i am getting this error "the database could not be reached.ssl connection requested.no ssl enabled connection from this host is configured." how to setup ssl conenction