Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

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
Employee
Employee

Re: Update Password in DataConnections automatically

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
Employee
Employee

Re: Update Password in DataConnections automatically

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