Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

How to generate xuser & xpassword in txt file

Dear Community:

Now we have a issue, customer want to read the xuserid & xpassword from txt file or excel file, then connect to db thru ODBC, thus , have any solution or tool can change userid and password to xuserid and xpassword ?

QV Server: 11.20 SR9

Best Regards...

  Kent Liao

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: How to generate xuser & xpassword in txt file

the xuserid and xpassword are encoded not encrypted. I would recommend you use an external script to store them encoded in the text files and then load them in QlikView using a regular load from file and connect to ODBC. Altought it is not advisable to store regular user/passwords in a text file in the server.

However, if you want QlikView to LOAD the the user/password pair and encode them to pass to the odbc connection string, you can add the following .vbs file (import thru edit module Ctrl-M) in the script editor and test, this is something I have never used in the past, but you can try:

LOAD

    Base64Encode(useridfromtextfile) as UseridTOConnect,

    Base64Encode(passwordidfromtextfile) as PasswordidTOConnect

FROM yourtextfile;

so Basically you are scrambiling them from the source .

--save this file into a directory and import it to your qview document to call the functions before connecting to odbc

Function Base64Decode(ByVal base64String)

  'rfc1521
  '1999 Antonin Foller
  Const Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"

  Dim dataLength, sOut, groupBegin

 

  'remove white spaces, If any
  base64String = Replace(base64String, vbCrLf, "")

  base64String = Replace(base64String, vbTab, "")

  base64String = Replace(base64String, " ", "")

 

  'The source must consists from groups with Len of 4 chars
  dataLength = Len(base64String)

  If dataLength Mod 4 <> 0 Then

   Err.Raise 1, "Base64Decode", "Bad Base64 string."

   Exit Function

  End If

 

  ' Now decode each group:
  For groupBegin = 1 To dataLength Step 4

   Dim numDataBytes, CharCounter, thisChar, thisData, nGroup, pOut

   ' Each data group encodes up To 3 actual bytes.
  numDataBytes = 3

  nGroup = 0

   For CharCounter = 0 To 3

   ' Convert each character into 6 bits of data, And add it To
   ' an integer For temporary storage.  If a character is a '=', there
   ' is one fewer data byte. (There can only be a maximum of 2 '=' In
   ' the whole string.)

  thisChar = Mid(base64String, groupBegin + CharCounter, 1)

   If thisChar = "=" Then

  numDataBytes = numDataBytes - 1

  thisData = 0

   Else

  thisData = InStr(1, Base64, thisChar, vbBinaryCompare) - 1

   End If

   If thisData = -1 Then

   Err.Raise 2, "Base64Decode", "Bad character In Base64 string."

   Exit Function

   End If

  nGroup = 64 * nGroup + thisData

   Next

  

   'Hex splits the long To 6 groups with 4 bits
  nGroup = Hex(nGroup)

  

   'Add leading zeros
  nGroup = String(6 - Len(nGroup), "0") & nGroup

  

   'Convert the 3 byte hex integer (6 chars) To 3 characters
  pOut = Chr(CByte("&H" & Mid(nGroup, 1, 2))) + _

   Chr(CByte("&H" & Mid(nGroup, 3, 2))) + _

   Chr(CByte("&H" & Mid(nGroup, 5, 2)))

  

   'add numDataBytes characters To out string
  sOut = sOut & Left(pOut, numDataBytes)

  Next

  Base64Decode = sOut

End Function


Function Base64Encode(inData)

  'rfc1521
  '2001 Antonin Foller,
  Const Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"

  Dim cOut, sOut, I

 

  'For each group of 3 bytes
  For I = 1 To Len(inData) Step 3

   Dim nGroup, pOut, sGroup

  

   'Create one long from this 3 bytes.
  nGroup = &H10000 * Asc(Mid(inData, I, 1)) + _

  &H100 * MyASC(Mid(inData, I + 1, 1)) + MyASC(Mid(inData, I + 2, 1))

  

   'Oct splits the long To 8 groups with 3 bits
  nGroup = Oct(nGroup)

  

   'Add leading zeros
  nGroup = String(8 - Len(nGroup), "0") & nGroup

  

   'Convert To base64
  pOut = Mid(Base64, CLng("&o" & Mid(nGroup, 1, 2)) + 1, 1) + _

   Mid(Base64, CLng("&o" & Mid(nGroup, 3, 2)) + 1, 1) + _

   Mid(Base64, CLng("&o" & Mid(nGroup, 5, 2)) + 1, 1) + _

   Mid(Base64, CLng("&o" & Mid(nGroup, 7, 2)) + 1, 1)

  

   'Add the part To OutPut string
  sOut = sOut + pOut

  

   'Add a new line For Each 76 chars In dest (76*3/4 = 57)
   'If (I + 2) Mod 57 = 0 Then sOut = sOut + vbCrLf
  Next

  Select Case Len(inData) Mod 3

   Case 1: '8 bit final
  sOut = Left(sOut, Len(sOut) - 2) + "=="

   Case 2: '16 bit final
  sOut = Left(sOut, Len(sOut) - 1) + "="

  End Select

  Base64Encode = sOut

End Function

Function MyASC(OneChar)

  If OneChar = "" Then MyASC = 0 Else MyASC = Asc(OneChar)

End Function

1 Reply
Not applicable

Re: How to generate xuser & xpassword in txt file

the xuserid and xpassword are encoded not encrypted. I would recommend you use an external script to store them encoded in the text files and then load them in QlikView using a regular load from file and connect to ODBC. Altought it is not advisable to store regular user/passwords in a text file in the server.

However, if you want QlikView to LOAD the the user/password pair and encode them to pass to the odbc connection string, you can add the following .vbs file (import thru edit module Ctrl-M) in the script editor and test, this is something I have never used in the past, but you can try:

LOAD

    Base64Encode(useridfromtextfile) as UseridTOConnect,

    Base64Encode(passwordidfromtextfile) as PasswordidTOConnect

FROM yourtextfile;

so Basically you are scrambiling them from the source .

--save this file into a directory and import it to your qview document to call the functions before connecting to odbc

Function Base64Decode(ByVal base64String)

  'rfc1521
  '1999 Antonin Foller
  Const Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"

  Dim dataLength, sOut, groupBegin

 

  'remove white spaces, If any
  base64String = Replace(base64String, vbCrLf, "")

  base64String = Replace(base64String, vbTab, "")

  base64String = Replace(base64String, " ", "")

 

  'The source must consists from groups with Len of 4 chars
  dataLength = Len(base64String)

  If dataLength Mod 4 <> 0 Then

   Err.Raise 1, "Base64Decode", "Bad Base64 string."

   Exit Function

  End If

 

  ' Now decode each group:
  For groupBegin = 1 To dataLength Step 4

   Dim numDataBytes, CharCounter, thisChar, thisData, nGroup, pOut

   ' Each data group encodes up To 3 actual bytes.
  numDataBytes = 3

  nGroup = 0

   For CharCounter = 0 To 3

   ' Convert each character into 6 bits of data, And add it To
   ' an integer For temporary storage.  If a character is a '=', there
   ' is one fewer data byte. (There can only be a maximum of 2 '=' In
   ' the whole string.)

  thisChar = Mid(base64String, groupBegin + CharCounter, 1)

   If thisChar = "=" Then

  numDataBytes = numDataBytes - 1

  thisData = 0

   Else

  thisData = InStr(1, Base64, thisChar, vbBinaryCompare) - 1

   End If

   If thisData = -1 Then

   Err.Raise 2, "Base64Decode", "Bad character In Base64 string."

   Exit Function

   End If

  nGroup = 64 * nGroup + thisData

   Next

  

   'Hex splits the long To 6 groups with 4 bits
  nGroup = Hex(nGroup)

  

   'Add leading zeros
  nGroup = String(6 - Len(nGroup), "0") & nGroup

  

   'Convert the 3 byte hex integer (6 chars) To 3 characters
  pOut = Chr(CByte("&H" & Mid(nGroup, 1, 2))) + _

   Chr(CByte("&H" & Mid(nGroup, 3, 2))) + _

   Chr(CByte("&H" & Mid(nGroup, 5, 2)))

  

   'add numDataBytes characters To out string
  sOut = sOut & Left(pOut, numDataBytes)

  Next

  Base64Decode = sOut

End Function


Function Base64Encode(inData)

  'rfc1521
  '2001 Antonin Foller,
  Const Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"

  Dim cOut, sOut, I

 

  'For each group of 3 bytes
  For I = 1 To Len(inData) Step 3

   Dim nGroup, pOut, sGroup

  

   'Create one long from this 3 bytes.
  nGroup = &H10000 * Asc(Mid(inData, I, 1)) + _

  &H100 * MyASC(Mid(inData, I + 1, 1)) + MyASC(Mid(inData, I + 2, 1))

  

   'Oct splits the long To 8 groups with 3 bits
  nGroup = Oct(nGroup)

  

   'Add leading zeros
  nGroup = String(8 - Len(nGroup), "0") & nGroup

  

   'Convert To base64
  pOut = Mid(Base64, CLng("&o" & Mid(nGroup, 1, 2)) + 1, 1) + _

   Mid(Base64, CLng("&o" & Mid(nGroup, 3, 2)) + 1, 1) + _

   Mid(Base64, CLng("&o" & Mid(nGroup, 5, 2)) + 1, 1) + _

   Mid(Base64, CLng("&o" & Mid(nGroup, 7, 2)) + 1, 1)

  

   'Add the part To OutPut string
  sOut = sOut + pOut

  

   'Add a new line For Each 76 chars In dest (76*3/4 = 57)
   'If (I + 2) Mod 57 = 0 Then sOut = sOut + vbCrLf
  Next

  Select Case Len(inData) Mod 3

   Case 1: '8 bit final
  sOut = Left(sOut, Len(sOut) - 2) + "=="

   Case 2: '16 bit final
  sOut = Left(sOut, Len(sOut) - 1) + "="

  End Select

  Base64Encode = sOut

End Function

Function MyASC(OneChar)

  If OneChar = "" Then MyASC = 0 Else MyASC = Asc(OneChar)

End Function