topic odbc connection string question in QlikView

odbc connection string question

Anonymous — Tue, 19 Jan 2016 15:30:04 GMT

Hi,

Does anyone know if the encrypted UserID and Password have a time component or perhaps some unique Qlikview app ID embedded? I setup a brand new SQL Server and created a user account which will be used for all Qlikview apps. I noticed the UserID and Password are all different for each of the apps in the ODBC connection string. Even for the same app, the UserID and Password maybe different when I create it. I would have thought the ODBC connecting string to remain constant as there is only one database account and password to use.

Anyone can share some knowledge on this?

Thanks much

Re: odbc connection string question

Anonymous — Thu, 21 Jan 2016 19:00:00 GMT

Is there anyone out there who can provide some feedbacks or comments on this?

Thanks

Re: odbc connection string question

maxgro — Fri, 22 Jan 2016 12:46:52 GMT

I can share my experience.

I sometimes used an external include with the connection and I send the include to other developers. The connection works for them.

In a connection with scrambled password it's possible to change some parameters (example the initial catalogue for a sql server database) without the need to recreate the string.

I never got an expired connection message from a qlik connection.

Re: odbc connection string question

PradeepReddy — Fri, 22 Jan 2016 13:01:22 GMT

create one connection string and use the same in all applications.

For easy maintenance store the connection string in .QVS file and include the same file in all applications.

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 14:05:30 GMT

OK. I just did a couple of tests. I have an ODBC named "ODBC_SQL" on my machine which points to my

SQL Server database. This odbc has been configured to point to say "MyDatabase" in SQL Server. Then

I created two tests QVW files and they both point to ODBC_SQL. Here are the actual connect string produced

by QV::

//Test_ODBC_1.qvw

ODBC CONNECT TO ODBC_SQL (XUserId is ALYIeYZOVbcKGZNJ, XPassword is XXGaVYJOWLZKXZRGRLYcGSBOcC);

//Test_ODBC_2.qvw

ODBC CONNECT TO ODBC_SQL (XUserId is BUBGcYZOVbcKGZBe, XPassword is FTbcTYJOWLZKXZRGRLYcGSBOfH);

As you can see, both the encrypted login and password are different even though the SAME credential was used when

connecting to ODBC_SQL. Obviously they both worked. So I swapped the connect string in both QVW files and they still worked. What confused me was, hence the reason I posted the question originally, why the encryted strings are diffferent? I guess if I had 10 QVW files, I could pick any of these connect strings to use, right?

If you can show me how to use an external include file or use QVS to store the connection, i would appreciate it. Otherwise I can do some research. My background is database administration and development. I started working with QV recently.

Thanks

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 14:21:33 GMT

OK. I found this link

https://community.qlikview.com/thread/143983

and created a QVS file with my connect string in it - that's it. I opened my QVW file and went the script editor

and did Insert - Include Statement. Ran the QVW and got this error:

SQL##f - SqlState: IM002, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

ODBC CONNECT TO ODBC_SQL (XUserId is ALYIeYZOVbcKGZNJ, XPassword is XXGaVYJOWLZKXZRGRLYcGSBOcC)

Re: odbc connection string question

maxgro — Fri, 22 Jan 2016 15:54:31 GMT

I use include for all my connections and never had problems.

Today I received a conn string from another developer, add the include file and it worked.

Your error seems related to a missing DSN in the ODBS (system DSN). Check ODBC_SQL presence in ODBC

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 16:02:16 GMT

My ODBC_SQL dsn is there. This is the same one that I used to create those two test QVW files I mentioned earlier.

So it's there. It works if I don't use QVS. maxgro, I noticed in your rejply, you mentioned system DSN. I have it as

user DSN. Does it matter? Well I will create one as system dsn and see what happens.

Re: odbc connection string question

quwok — Fri, 22 Jan 2016 16:13:33 GMT

That should not matter for your experiment. A User DSN is specific to your user account and a System DSN is available to everybody on the machine.

Re: odbc connection string question

Fri, 22 Jan 2016 16:15:26 GMT

Are you testing on the server or in development ?

Please check the ODBC DSN created in the host you are trying.

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 16:15:41 GMT

It turns out QV wont' let you choose System DSN. When I un-checked "Show user DSNs", nothing shows up in my

Data Sources even though I just created a system dsn.

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 16:17:06 GMT

I'm testing this on my development machine. Will this only work on the server side when you use QVS?

Re: odbc connection string question

quwok — Fri, 22 Jan 2016 16:23:41 GMT

The observation is because you have set it up as a User DSN and not as a System DSN. If you deselect the check box, it removes the User DSN from the list. It should show if you set up it up as System DSN in your ODBC manager.

Re: odbc connection string question

quwok — Fri, 22 Jan 2016 16:26:20 GMT

The connection string should work as long as you have a DSN to the data source called ODBC_SQL on the machine you are running the script on.

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 16:30:55 GMT

I'm attaching a screenshot of my system dsn data sources. As you can see, I have "ODB_SQL_SYSTEM".

Yet when I unchecked "Show use DSNs", my system DSN's are not showing up. I have a total of 4 system

dsn's on my dev box. What am I missing or doing wrong??

Re: odbc connection string question

Peter_Cammaert — Fri, 22 Jan 2016 16:38:34 GMT

Back to your original question:different UserID/Password combinations for different CONNECT strings and different apps, all to connect to the same DB.

The UserID and Password parameters in an ODBC connection string can be specified in two ways:

in clear text, which means that everyone with access to the script can read these secret values
or scrambled (not encrypted), a process that is performed by QlikView in QV and for QV-use only.

The idea seems to be that the scrambling is intended solely to hide these parameters from prying developer eyes. Underneath, and whenever the scirpt engine needs to connect to a DB through ODBC, these values are first unscrambled and then passed on to the ODBC driver. A bad database implementation may even send these values in clear text across the network, but most professional RDBMS do their own message encryption before sending those values to the remote database engine.

And you're right, the scrambling process uses some variable factor to perform the scrambling. This is commonly called a salt. I don't know if this is a time value or not. But that doesn't really matter, does it?

Best,

Peter

Re: odbc connection string question

quwok — Fri, 22 Jan 2016 16:38:51 GMT

Are these 32-bit or 64-bit data sources?

Maybe try ticking the "Force 32-Bit" and see if they appear.

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 16:47:01 GMT

FINALLY !!. Got it to work. It needs to be a x64 DSN. It works either a user or system dsn !

Thanks for that tip, Kwok Chong.

Re: odbc connection string question

Peter_Cammaert — Fri, 22 Jan 2016 16:51:11 GMT

Don't use User DSNs on a server. If you do, you first need to login as the service account that is running the QlikView services. Otherwise, the reload batch job will be unable to "see" the ODBC definitions that you defined for a different user.

Also keep in ming that on a 64-bit machine there are two ODBC definition lists: one 32-bit and one 64-bit. They do not mix!

Best,

Peter

Re: odbc connection string question

Anonymous — Fri, 22 Jan 2016 17:00:34 GMT

Peter,

I wasn't aware there is a difference in definition between "scrambling" and "encrypting"? I thought they meant the same

thing but under the hood, the algorithm used maybe different. End result is you're changing a clear text to a non-clear text. I always encrypt application credential used in a connect string when storing in a text file and have a process to decrypt it just right before it hits the database server. So when I saw the UserID and Password QV produced which looks very similar to the algorithm I used, I thought they were "encrypted". But regardless, I now know it really doesn't matter whether they are the same or not or why they are different each time I create them, I can use it in any QV app that needs that connection. However QV does it, it has to produce a user id and password that the database server

permits you to connect to.

I would be interested to know a bit more of the diffference between scrambling and encrypting in QV.

Thanks