Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
chrislemm
Partner - Contributor III
Partner - Contributor III

Update Password in DataConnections automatically

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:

DataConnecitons_1.png

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.

1 Solution

Accepted Solutions
Levi_Turner
Employee
Employee

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.

View solution in original post

1 Reply
Levi_Turner
Employee
Employee

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.