How to create a Qlik Oracle Wallet ODBC connection via the QRS Rest API

[Andreas_Giesbrecht]

This article documents how to successfully implement an Oracle Wallet into a POST call, which is a necessary step when setting up a Qlik Oracle ODBC connection using the Wallet authentication method via the Qlik QRS API (post /dataconnection).

You can use the following approach to achieve this.

The first three steps will show you what information you need for the POST call.

1. Create a manual Qlik Oracle connection via Wallet authentication in the Qlik Data Load Editor
   
   
   
   
2. Get the connection details in the form of a JSON via the following QRS call:
   

   get /dataconnection/{id}

   For details, review get /dataconnection/{id}
   
   In our example, the data connection ID is 67069b0e-ef40-4873-91a8-8a9c56d61ebd (locate the ID in the Qlik Sense Management Console Data Connections section). 
   
   To retrieve all necessary information for the POST call use the following PowerShell script, which will query the repository using the user "sa_repository" and domain "internal". 
   
   

   # Ignore SSL validation errors
   add-type @"
   using System.Net;
   using System.Security.Cryptography.X509Certificates;
   public class TrustAllCertsPolicy : ICertificatePolicy {
      public bool CheckValidationResult(
          ServicePoint srvPoint, X509Certificate certificate,
          WebRequest request, int certificateProblem) {
          return true;
      }
   }
   "@
   [System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
   # Force TLS 1.2
   [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
   # Headers and cert
   $hdrs = @{
      "X-Qlik-xrfkey" = "12345678qwertyui"
      "X-Qlik-User" = "UserDirectory=internal;UserId=sa_repository"
   }
   $cert = Get-ChildItem -Path "Cert:\CurrentUser\My" | Where-Object { $_.Subject -like '*QlikClient*' }
   if (-not $cert) {
      Write-Error "Qlik client certificate not found!"
      exit
   }
   # API URL
   $url = "https://localhost:4242/qrs/dataconnection/67069b0e-ef40-4873-91a8-8a9c56d61ebd?xrfkey=12345678qwertyui"
   # Make request
   try {
      $resp = Invoke-RestMethod -Uri $url -Method Get -Headers $hdrs -Certificate $cert
      $resp | ConvertTo-Json -Depth 10
   } catch {
      Write-Error "API call failed: $_"
   }​

   
   
   
3. The result of step two is a JSON structure that should look similar to this example:
   
   

   {
   "id": "67069b0e-ef40-4873-91a8-8a9c56d61ebd",
   "createdDate": "2024-07-08T10:15:25.144Z",
   "modifiedDate": "2025-04-25T09:34:56.948Z",
   "modifiedByUserName": "DOMAIN\\administrator",
   "customProperties": [],
   "owner": {
   "id": "0e756718-ddfa-457a-a219-256211c8dcb4",
   "userId": "administrator",
   "userDirectory": "DOMAIN",
   "userDirectoryConnectorName": "DOMAIN",
   "name": "Administrator",
   "privileges": null
   },
   "name": "Oracle_TEST (domain_administrator)",
   "connectionstring": "CUSTOM CONNECT TO \"provider=QvOdbcConnectorPackage.exe;driver=oracle;ConnectionType=wallet;port=1521;USETNS=false;TnsName=zx0ka5pcjb3oxzut_high;EnableNcharSupport=1;allowNonSelectQueries=false;QueryTimeout=30;useBulkReader=true;maxStringLength=4096;logSQLStatements=false;\"",
   "type": "QvOdbcConnectorPackage.exe",
   "engineObjectId": "c4a089ad-b853-4841-b96c-79d259fba388",
   "username": "ADMIN",
   "password": "Wallet%2wallet.zip%%2UEsDBF...", // truncated for readability
   "logOn": 0,
   "architecture": 0,
   "tags": [],
   "privileges": null,
   "schemaPath": "DataConnection"
   }​

   
   Note "ConnectionType=wallet"  in the connection string and "password": "Wallet%2wallet.zip%%2UEsDBF..." This contains the encoded information from the Oracle Wallet.

4. Now apply all information from steps two and three on the template for the post /dataconnection request. See post /dataconnection for details. This allows you to create the Oracle ODBC connection via an API call (using the same Oracle Wallet).

   The JSON could look something like this:
   
   

   {
   "name": "Oracle_TEST123",
   "connectionstring": "CUSTOM CONNECT TO \"provider=QvOdbcConnectorPackage.exe;driver=oracle;ConnectionType=wallet;port=1521;USETNS=false;TnsName=zx0ka5pcjb3oxzut_high;EnableNcharSupport=1;allowNonSelectQueries=false;QueryTimeout=30;useBulkReader=true;maxStringLength=4096;logSQLStatements=false;\"",
   "type": "QvOdbcConnectorPackage.exe",
   "username": "ADMIN",
   "password": "Wallet%2wallet.zip%%2UEsDBF...", // truncated for readability
   "logOn": 0,
   "architecture": 0,
   "schemaPath": "DataConnection",
   "tags": [],
   "customProperties": [],
   "owner": {
   "id": "0e756718-ddfa-457a-a219-256211c8dcb4",
   "userId": "administrator",
   "userDirectory": "DOMAIN",
   "userDirectoryConnectorName": "DOMAIN",
   "name": "Administrator"
   }
   }

   
   PowerShell script example:
   
   

   $hdrs = @{
      "X-Qlik-xrfkey" = "12345678qwertyui"
      "X-Qlik-User" = "UserDirectory=internal;UserId=sa_repository"
      "Content-Type" = "application/json"
   }
   $cert = Get-ChildItem -Path "Cert:\CurrentUser\My" | Where-Object { $_.Subject -like '*QlikClient*' }
   
   $body = @{
      name = "Oracle_TEST123"
      connectionstring = 'CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=oracle;ConnectionType=wallet;port=1521;USETNS=false;TnsName=zx0ka5pcjb3oxzut_high;EnableNcharSupport=1;allowNonSelectQueries=false;QueryTimeout=30;useBulkReader=true;maxStringLength=4096;logSQLStatements=false;"'
      type = "QvOdbcConnectorPackage.exe"
      username = "ADMIN"
      password = "Wallet%2wallet.zip%%2UEsDBF..."  # truncated 
      logOn = 0
      architecture = 0
      schemaPath = "DataConnection"
      tags = @()
      customProperties = @()
      owner = @{
          id = "0e756718-ddfa-457a-a219-256211c8dcb4"
          userId = "administrator"
          userDirectory = "DOMAIN"
          userDirectoryConnectorName = "DOMAIN"
          name = "Administrator"
      }
   }
   $jsonBody = $body | ConvertTo-Json -Depth 10
   
   $url = "https://localhost:4242/qrs/dataconnection?xrfkey=12345678qwertyui"
   try {
      $response = Invoke-RestMethod -Uri $url -Method Post -Headers $hdrs -Certificate $cert -Body $jsonBody
      $response | ConvertTo-Json -Depth 10
   } catch {
      Write-Error "POST failed: $_"
   }

You now have everything you need to create a new Oracle connection (Oracle_TEST123 as named in our example) using the post /dataconnection QRS REST API call.

Environment

• Qlik ODBC Connector Package

This article is provided as is. For additional assistance, post your query in our Integration and API forum or contact Services for customized solution assistance.