Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Aug 30, 2024 1:43:41 AM
Aug 30, 2024 1:43:41 AM
Creating an OLE DB connection in Qlik Sense on-prem fails at the Test connection step with:
Test failed
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:
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
}
}
}
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.
Test SQL database connectivity with test.udl file
Create a connection Method