QVD files store connection string in plain text!

[pljsoftware]

Hi all!

I have noticed a very strange thing using QVD files... Inside the file, I can read in plain text my QlikView script, including the connection string with password!

How it can be possible?

[fosuzuki]

Wow, I did not know this.

I think you should try to get in touch with QlikTech...

Regards,

Fernando

[erichshiino]

HI, Fernando.

Did you get the same results?

Which version are you all using?

Regards,

Erich

[fosuzuki]

Hi Erich, yes same problem. Using QV10SR2.

Miguel, at first I did not believe (sorry Luca, nothing personal... ), but it is true. Try opening the QVD file in notepad. In the XML strucutre, there is something like this:

     <LineageInfo>

       <Discriminator>Provider=SQLOLEDB.1;Persist Security Info=True;User ID=XXXX;Initial Catalog=catalog;Data Source=XXXX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXX;Use Encryption for Data=False;Tag with column collation when possible=False;Password=XXXX</Discriminator>

       <Statement>Data:

          SQL SELECT....

Fernando

[Miguel_Angel_Baeyens]

Fernando,

I'm afraid I'm missing something. I already did, but I don't see those XML tags (LineageInfo, Discriminator) in my QVD files. Are you doing a LOAD and then STORE or you STORE directly from a SQL statement? I always LOAD then STORE.

Thanks and regards,

Miguel

EDIT: My God... I'm sorry Fernando, I was kind of dumb. I do see the tags you mention, and they indeed are plain text even when I've set them scrambled in the Edit Script dialog, Settings tab in the bottom part. It's definitely a support case.

Thanks Jonathan for noting!

BTW using 10 SR4. That does not happen with version 9 (x64 SR4), and hence my confussion, since I have QVD created with a lot of different versions of QlikView.

EDIT2: No so blind: I do see the Lineage and Discriminator fields, and sometimes the user, but never the password neither plain text nor scrambled. Using both OLEDB and ODBC drivers. Can you please elaborate on how are you creating your connection?

Update: ODBC: when user and password is stored in the ODBC (so there's no need to specify when selecting with the Connect... button, no passwords are visible). OLEDB: I can see the user in plain text (but it does is plain text in the connection string), but never the password. My tests connecting to a Microsoft SQL Server.

[fosuzuki]

Miguel, no problem. Glad to know I wasn't wrong...   Same here: I use scrambled password in script, but it appears unscrambled in QVD... Also checked old QVD files generated by QV9SR4 and there isn't such XML section.

[johnw]

EDIT: LOL, I AM kind of dumb!  I didn't RUN the script, and I was grabbing QVDs from our development system, where some of them are quite old.  The new QVD does indeed have plain text user ID and password despite me asking for it to be scrambled.  Nasty little security hole there!

Original Post: OK, I guess it's my turn to be "kind of dumb".  I don't see those XML tags, user ID or password in my QVDs, or at least in the ones I've looked at so far.  I'm using V10 SR3.  Here's our simplest QVD script with the scrambled password:

DIRECTORY QVD;

ODBC CONNECT TO UPIDB2P (XUserId is NSUDeYRNJbaMXUVMXDbA, XPassword is NQcXLYFMTbcMXUVMXDLA);

[Cost System Start Date]:
LOAD date(date#(START,'YYYYMMDD')) as "Cost System Start Date"
;
SQL SELECT SUBSTR(BPDATA,50,8) AS START
FROM SYSTLC.ATBP101R
WHERE BPID = 'COST SYSTEM'
FOR FETCH ONLY
;
DISCONNECT;

STORE [Cost System Start Date] INTO CostSystemStartDate.qvd (QVD);

And here's the resulting QVD:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<QvdTableHeader>
   <QvBuildNo>7646</QvBuildNo>
   <CreatorDoc></CreatorDoc>
   <CreateUtcTime>2011-05-02 23:07:21</CreateUtcTime>
   <SourceCreateUtcTime></SourceCreateUtcTime>
   <SourceFileUtcTime></SourceFileUtcTime>
   <SourceFileSize>-1</SourceFileSize>
   <StaleUtcTime></StaleUtcTime>
   <TableName>Cost System Start Date</TableName>
   <Fields>
     <QvdFieldHeader>
       <FieldName>Cost System Start Date</FieldName>
       <BitOffset>0</BitOffset>
       <BitWidth>8</BitWidth>
       <Bias>0</Bias>
       <NumberFormat>
         <Type>3</Type>
         <nDec>0</nDec>
         <UseThou>0</UseThou>
         <Fmt>M/D/YYYY</Fmt>
         <Dec></Dec>
         <Thou></Thou>
       </NumberFormat>
       <NoOfSymbols>1</NoOfSymbols>
       <Offset>0</Offset>
       <Length>14</Length>
     </QvdFieldHeader>
   </Fields>
   <Compression></Compression>
   <RecordByteSize>1</RecordByteSize>
   <NoOfRecords>1</NoOfRecords>
   <Offset>14</Offset>
   <Length>1</Length>
</QvdTableHeader>
  ‘š  5/1/2008 

[fosuzuki]

Hi all,

I also found this same problem in the QVW files, at the end of the file you can find some XML text with the LineageInfo and Discriminator...

It seems that is was removed from QV11, but I don't have a QV11 instance to check it:

http://community.qlik.com/message/148804#148804

[johnw]

Fernando Suzuki wrote:

I also found this same problem in the QVW files, at the end of the file you can find some XML text with the LineageInfo and Discriminator...

Yep, there it is.  Good grief, QlikTech.  Seriously?

[Miguel_Angel_Baeyens]

That's a big one, indeed.