Update Password in DataConnections automatically

chrislemm — Fri, 07 Sep 2018 06:19:59 GMT

Good morning Qlik-Community,

when adding a Data Connection in Qlik Sense you can use User + Password credentials to validate your access to f.e. a database. We have a customer where this login changes regularly. At the moment we have to change the Data Connection every time the account. To handle such things fully automatically I wanted to change the Login-Credentials in the Repository Database of Qlik Sense. So I checked the DataConnection-Table and found:

So I tried replacing Username and PasswordString. The problem here is that the password is stored encrypted and I have no clue how it's encrypted.

Update "DataConnections"
SET "Username" = 'username', "PasswordString" = 'unencryptedtext'
where <somecondition>

How is the password encrypted in the repository database? How does Qlik Sense edit the password, when I change the credentials inside the Data Connection in Qlik Sense?

Best greetings.

Re: Update Password in DataConnections automatically

Levi_Turner — Fri, 07 Sep 2018 13:56:32 GMT

Hey Christopher,

Going through the database layer isn't going to be supported, documented, or recommended. The values there are AES256 encrypted with a salt.

The recommended approach will be to go through the QRS API. Example code taken from https://github.com/levi-turner/QlikSenseScripts/blob/master/qlik_sense_pwd_change.ps1 with comments for clarity:

# Prompt for password
$Password = Read-Host -Prompt 'Input the  new password'


# Store the Base64 Encoded package in the Host.cfg for connection
$Data = Get-Content C:\ProgramData\Qlik\Sense\Host.cfg
# Convert the base64 encoded install name for Sense to UTF data
$FQDN = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($($Data)))
# Connect to Qlik Sense using an internal account (sa_api)
Connect-Qlik -ComputerName https://$($FQDN):4242 -Username INTERNAL\sa_api


# Expose all elements of the JSON response
$rawoutput=$true


# Get the DataConnection JSON for a particular data connection
$RESTapp = Invoke-QlikGet -path "/qrs/dataconnection/full?filter=(name eq 'monitor_apps_REST_app')"


# Filter out the ID value for later GET
$RESTappID = $RESTapp.id


# GET the DataConnection JSON
$RESTappDC = Invoke-QlikGet -path /qrs/dataconnection/$RESTappID


# Swap the password out in the JSON
$RESTappDC | Add-Member Password $Password -Force


# Convert to actual JSON
$RESTappDC = $RESTappDC | ConvertTo-Json


# PUT in the new password
Invoke-QlikPut -path https://$($FQDN):4242/qrs/dataconnection/$RESTappID -body $RESTappDC

This is leveraging Qlik-Cli (https://github.com/ahaydon/Qlik-Cli/) but can be adapted to any scripting or programming language.

The key element here is that we are passing the client certificate and connecting as an INTERNAL account.

Hopes that helps.

topic Update Password in DataConnections automatically in Management & Governance

Update Password in DataConnections automatically

Re: Update Password in DataConnections automatically