Qlik Sense Enterprise on Windows: OLE DB Test Failed error

[NadiaB]

Creating an OLE DB connection in Qlik Sense on-prem fails at the Test connection step with:

Test failed

Resolution

To verify the connection is successful, first confirm the connection works using a UDL file. For step-by-step instructions refer to Test SQL database connectivity with test.udl file. Once you receive a Test connection succeeded with the UDL file, close all the prompts and open the file with a text editor. This file contains the new connection string. 

For the test in our example, the only extra parameter enabled in the connection string that is not possible to put in the connection UI is Trust Server Certificate=True. All other values are set to empty or null and we exclude them. 

With that information, you can now create a connection with the Engine API explorer:

1. Open the dev-hub
2. Select Engine API explorer
3. Click Connect to engine
4. Then select the app from the Select an app drop-down list.
5. In the Select API calls section, on the second row Doc object, in the list select method select Create connection
6. Provide a JSON in the request as the one below. It will be necessary to replace the connection string ( parameters that have no values can be removed but if kept make sure to adjust the string to get a valid JSON, so if there are parameters for example File Name=""; this can just be removed from the connection string)
7. Select Execute

Note: Make sure to keep the OLEDB CONNECT TO and the brackets in the connection string value.  

JSON:

{
"handle": 1,
"method": "CreateConnection",
"params": {
"qConnection": {
"qId": "",
"qName": "OLEDBSQL",
"qConnectionString": "OLEDB CONNECT TO [Provider=MSOLEDBSQL19.1;Persist Security Info=False;Data Source=ServerNameorIP;Trust Server Certificate=True;]",
"qType": "OLEDB",
"qUserName": "user",
"qPassword": "Password",
"qModifiedDate": "",
"qMeta": {
"qName": ""
},
"qLogOn": 0
}
}
}

Cause 

Limited options to add extra parameters to the OLE DB connection through the UI. The connection string field is only available in "Edit" mode, not in creation mode.

Related Content

Test SQL database connectivity with test.udl file
Create a connection Method

Environment

• Qlik Sense Enterprise on Windows (All versions)