Qlik Community

Qlik Healthcare User Group

A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights.

New Contributor III

How are you using QS Data Connections when using user accounts and service accounts

We are in the process of getting our Qlik Sense deployment rolled out and I just ran into an issue today that I'm not sure how to solve.

We have 3 separate environments (Dev, TST and PRD).  Now I have created a script to determine what environment you are in, be it dev, tst or prd.  This allows us to use txt files that store ODBC connections and txt files for variables.  So no matter what environment you are in Qlik Sense knows where to look for those files or where to store files.  This works great in QlikView.

The issue I'm having now though is with Data Connections, specifically for ODBC connections.  In dev we want our developers to use their own credentials for the ODBC connections, but in tst and prd we use Service Accounts.  This is so our auditing group can differentiate who or what system is connecting to the databases.  We don't want to use the developers credentials in tst and prd as they aren't actually connecting, but also we have to change our passwords every 90 days.

So the issue I'm running into is that I setup a data connection in all 3 environments.  So we have data connection ODBC_QS_CLARITY.  Now this also appends my user id to the name (very annoying WTH).  So the actual name of the Data Connection is now ODBC_QS_CLARITY (jkofoot).  So in tst and prd this is setup to use our service account, no issues.  But if we want developers to use these ODBC connections but use their credentials, they have to edit the connection and when they do, their userid now gets appended to the data connection name.  So now we would have a name like ODBC_QS_CLARITY (jkofoot) (asmith).  
Also we want to store the connection string in a txt file which we reference by knowing what environment we are coming from.  This worked awesome in QlikView because then each developer could hide their connection string as it included the username and password in the connection string.  But now it is just a Lib statement that doesn't have the userid/password in it.

Basically just want to know how other groups are handling having developers use their own credentials for connecting to databases, but using service accounts for connecting to databases in tst or prd.  Basically I'm not seeing a good way of doing it, without just resorting to using service account for everyone.  Don't want to do that though as it has the potential to give more access to the developer than they should have.

1 Reply

Re: How are you using QS Data Connections when using user accounts and service accounts

Hi Jeremy

I assume you have the QDF framework setup and referenced in your scripts.

To go between environments we set a variable for the environment:  SET vG.ETL = 'Dev';

And then one for our Data Connection:  SET vG.LibraryConnection='cx~DatabaseName';

You could then just update the connection variable name as you move through each environment, or create an IF statement  based on the variable vG.ETL to run the appropriately named connection.