TSL 1.2 and OLE DB Connection not working

[AliceB]

Hi Everyone.

Database system department has changed the database server and they have passed from a TLS 1.0/1.1 to a TLS 1.2 one.

We are working with a QS Enterprise august 2023 (supported version) and a OLE DB connection (Microsoft OLE DB provider for SQL Server) in a Windows 2016 server.

We have tried all possible kinds of OLE DB connectors QS offers but no one is working (connection errors) even if:

• We have created the regedit folders to enable the TLS 1.2 protocol (How to Check and Enable TLS 1.2 - 365 CloudIT).
• We have installed the OLE DB Driver for SQL Server (OLEDB Connection not working with TLS 1.2 - Qlik Community - 1717492).
• We have tried to create a Microsoft SQL Server connection, cause it allows to set a TLS 1.2 protocol; it's connecting but we can't use this kind of connection due to different data formats that are generating lots of errors (especially date formats in conditions).

DB system department has temporary enabled the TLS 1.0, so QS connection is now working but for security rules we have to pass to a TLS 1.2.

Next step we want to try is migrating our QS to a Windows 22 server, in order to create a PRE environment.

We are looking for more suggestions, before migrating QS (even if we are aware that creating a PRE environment is necessary).

Thanks in advance. Regards,

Alice B.

[diegozecchini]

Hi Alice! Before proceeding with the migration to a Windows Server 2022 environment you can try some steps

Firstly I would ensure that your server 2016 has all the necessary updates to fully support TLS 1.2. Specifically:

Install the latest Cumulative Updates for Windows Server 2016. (https://www.catalog.update.microsoft.com/)
Confirm that the following registry keys are set properly for enabling TLS 1.2:
H K L M\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2
Client: Enabled = 1, DisabledByDefault = 0
Server: Enabled = 1, DisabledByDefault = 0
Restart the server after changes.

Then MS offers a dedicated OLE DB driver that is optimized for modern SQL Server features, including TLS 1.2. Follow these steps:

Install the latest Microsoft OLE DB Driver for SQL Server on your QS server. (https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver1...
Update the connection string in Qlik Sense to use the new driver.

Use a connection string like:

Provider=MSOLEDBSQL;Server=your_server_name;Database=your_database_name;Trusted_Connection=Yes;Encrypt=Yes;TrustServerCertificate=No;

Ensure the Encrypt=Yes parameter is specified, as it enforces TLS encryption.

If the OLE DB driver doesn't work, consider using the ODBC driver for SQL Server:
https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-serv...
Test the ODBC connection in Qlik Sense to see if it resolves the TLS 1.2 compatibility

[AliceB]

Spoiler

Hi, @diegozecchini.
Thanks for your exhaustive answer.

We have tried that string connection, even if we've missed the "TrustServerCertificate=No;" so we will try it again after updating Microsoft OLE DB Driver for SQL Server from the link you've sent.

I'll keep you posted.
Regards,

Alice B.

[diegozecchini]

ok, let me know, regards, Diego