topic Dynamic Connection String in QlikView

Dynamic Connection String

sohb2015 — Mon, 21 Nov 2016 12:02:28 GMT

Hello All,

I have received a requirement from my client where due to security reason he wants to create dynamic connection string instead of using the direct user id and password. I am aware of the fact that we can use $(include=file location) to use a file for connection string. However, the requirement is such that a table/excel to be created where the password will be stored and maintained by the client alone. I have to store the password in a variable and use that variable in the connection string. I can't use $(include) statement. How could I achieve this.

Thanks

Re: Dynamic Connection String

jonathandienst — Mon, 21 Nov 2016 12:10:12 GMT

If you are storing the password in any way, are you securing the document as well so that only that user can run it? If it is only reloaded and used by that user, put this into a folder on its own and secure using network security. If you plan to secure text or Excel output, I would look again to network security as the solution. But perhaps I have not properly understood your requirement?

Re: Dynamic Connection String

Peter_Cammaert — Mon, 21 Nov 2016 12:35:33 GMT

That's fairly simple to implement.

Use regular Script statements to load a single row from an Excel
Use the peek() function to extract username/password values from that single row and store them in variables. In the example I use vUsername and vPassword.
Use $-sign substitution to replace username and password at the end of your connection string when executing a CONNECT TO statement

That's about it. You can best start from an existing CONNECT TO statement with unscrambled credentials. For an OLE DB connection, there are user ID and password parameters that accept unscrambled values. For example (using the SQL Server Native Client):

CONNECT TO [Provider=SQLNCLI11;Server=$(vServerAddress);Database=$(vDataBase);Uid=$(vUsername);

Pwd=$(vPassword);];

Best,

Peter

Re: Dynamic Connection String

sohb2015 — Tue, 22 Nov 2016 12:30:51 GMT

Thanks Peter.

However, the userid and password are encrypted, hence while reloading,error occurred : incorrect username or password.

What do you suggest.

Thanks