11 Replies Latest reply: Jan 30, 2018 10:03 AM by Craig Murray RSS

    QS S3 Connection for Non Public files

    Craig Murray

      Hi All,

       

      I cannot currently connect to a private S3 bucket using the QS connector, as this requires that the buckets be made public.

      Is there any way I can overcome this with the connector or indeed a web file / REST connection, without having to compromise on file security?

       

      Thanks

        • Re: QS S3 Connection for Non Public files
          Petter Skjolden

          Qlik Web Connectors has support for Amazon S3 with authentication and full security. You can download here Qlik Market

           

          The Amazon S3 support is free as it is still in beta I believe. When out of beta it will either be a standard connector which still we be free or a premium connector where you will have to pay a yearly license fee.

            • Re: QS S3 Connection for Non Public files
              Craig Murray

              Thanks Petter,

               

              I am currently using the S3 Connector in beta, within the Qlik Web Connectors package, and cannot connect, unless files are made public within S3, even with full authentication, so unfortunately this does not help.

                • Re: QS S3 Connection for Non Public files
                  Levi Turner

                  I am not following. I am having no issues accessing a private file on my end using the connector.

                   

                  S3 Validation:

                  Qx8fi5o.png

                   

                  Connector setup:

                  lncUyGh.png

                  5Rdryx9.png

                  Jp95apM.png

                  a7AP6SF.png

                    • Re: QS S3 Connection for Non Public files
                      Craig Murray

                      Thanks Levi, I had noted from other posts that this was not possible.

                       

                      I receive the below error, Access Key and Secret Key is correct.

                      I am going to investigate any end point and firewall issues now you have confirmed non public is indeed possible.

                       

                      Thanks

                       

                      S3 Connector - List Buckets.PNG

                        • Re: QS S3 Connection for Non Public files
                          Levi Turner

                          I'd personally test with Postman from the machine the Web Connector request (e.g. if running from a server, test with Postman there):

                          1iSyKls.png

                           

                          403 indicates that it's a permissions issue.

                           

                          You can technically do this in a load script using the WITH CONNECTION function to pass along values to the REST connector:

                          / These will expire soon so I am not too worried here

                          // Validated with Qlik Sense November 2017 Patch 1

                          // Setting the header values to pass later; scalable solution is to handle via include scripts which are updated by a script elsewhere

                          LET vAuthorization='AWS4-HMAC-SHA256 Credential=123456788/20171220/xx-xxxx-x/s3/aws4_request, SignedHeaders=host;x-amz-content-sha256;x-amz-date, Signature=ef99e277c8bac93f26a55bca893981a3bd537529acbfbb5595a0c2e5e2d3fc93';

                          LET vSha256='e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855';

                          LET vDate='20171220T235344Z';


                          LET URL_path = 'https://s3.amazonaws.com/qlikshare124124190zzd/201011.csv';

                          LET vURL_path = replace(URL_path,'"', chr(34)&chr(34));

                          // Real working one

                          //LIB CONNECT TO 'S3 (AWSv4) (qtsel_ltu)';

                          LIB CONNECT TO 'S3 (Dummy) (qtsel_ltu)';

                          RestConnectorMasterTable:

                          SQL SELECT

                          "col_1"

                          FROM CSV (header off, delimiter ",", quote """") "CSV_source"

                          WITH CONNECTION (

                          URL "$(URL_path)",

                          HTTPHEADER "Authorization" "$(vAuthorization)",

                          HTTPHEADER "X-Amz-Date" "$(vDate)",

                          HTTPHEADER "X-Amz-Content-Sha256" "$(vSha256)",

                          )

                          ;


                          [CSV_source]:

                          LOAD "col_1" as "col_1"

                          RESIDENT RestConnectorMasterTable;

                          DROP TABLE RestConnectorMasterTable;


                          The difficulty with S3 is that the more modern auth mechanisms on AWS / S3 need updated computed Authorization header values. You can scale this by using include scripts in Sense and some other script (e.g. I've used GitHub - mhart/aws4: Signs and prepares node.js (and browser) requests using AWS Signature Version 4) to handle the computation.

                            • Re: QS S3 Connection for Non Public files
                              Craig Murray

                              Thanks Levi,

                               

                              For the signature, please can I ask how you managed to compute this?

                              Signature.PNG

                               

                              Please could I also ask how you defined vSha256 for the signing algorithm?


                              Thanks again

                               



                                • Re: QS S3 Connection for Non Public files
                                  Craig Murray

                                  Levi, please ignore, I managed to get the signature and algorithm.

                                   

                                  My Lib connect string fails however, whilst using

                                   

                                  LIB CONNECT TO 'S3 (AWSv4) (qtsel_ltu);

                                   

                                  Thanks again

                                  --------


                                  Example code:


                                  LET vAuthorization='xxxx-xxx-xxxxxx Credential=xxxxxxxxxxxxxxxx/20180130/us-east-1/s3/aws4_request, SignedHeaders=content-type;host;x-amz-content-sha256;x-amz-date, Signature=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
                                  LET vSha256='e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855';
                                  LET vDate='20180130T105549Z';

                                  LET URL_path = 'https://s3.amazonaws.com/publytics-ftp-backup/axis360/Axis360_CIRC_Init.txt';
                                  LET vURL_path = replace(URL_path,'"', chr(34)&chr(34));

                                  LIB CONNECT TO 'S3 (AWSv4) (qtsel_ltu)';

                                  RestConnectorMasterTable:
                                  SQL SELECT
                                  "col_1"
                                  FROM CSV (header off, delimiter ",", quote """") "CSV_source"
                                  WITH CONNECTION (
                                  URL "$(URL_path)",
                                  HTTPHEADER "Content-Type" "$(vContentType),
                                  HTTPHEADER "Authorization" "$(vAuthorization)",
                                  HTTPHEADER "X-Amz-Date" "$(vDate)",
                                  HTTPHEADER "X-Amz-Content-Sha256" "$(vSha256)",
                                  )
                                  ;

                                  [CSV_source]:
                                  LOAD "col_1" as "col_1"
                                  RESIDENT RestConnectorMasterTable;
                                  DROP TABLE RestConnectorMasterTable;

                                   

                                  LIB CONNECT.PNG

                                    • Re: QS S3 Connection for Non Public files
                                      Petter Skjolden

                                      You seems to be mixing to different ways of connecting to Amazon S3:

                                       

                                      1) Using Qlik Web Connectors - S3 connector

                                          The QWC acts as a proxy to Amazon S3. The Qlik Sense script will have to

                                          connect to this proxy which would be the same computer on localhost or another computer

                                          in your internal network that is running the QWC software. Like Levi I had no problem using

                                          authentication on S3 through QWC.

                                       

                                          You highlighter in blue in one of the previous posts the URL that you could use with a simple

                                          LOAD .... FROM <URL>; // URL would be normally localhost where the QWC is hosted.

                                       

                                      2) Using Qlik REST Connector directly to Amazon

                                          The authentication mechanism would be "hard" to implement in a load script - so public access is

                                          the most common case for using this.

                                       

                                          Copy and pasting an AWS signature with Postman or similar tools with give you an option to test but only a   

                                          limited time period to connect - a few minutes at the most.

                                        • Re: QS S3 Connection for Non Public files
                                          Craig Murray

                                          Thank you Petter and Levi,

                                           

                                          I was able to connect via Postman, and can now connect using a REST connection, but the expiry of the signature as you have mentioned, means that I cannot use this solution as a longer term incremental pull from S3, and after some time receive an access error once again whilst files remain private.

                                           

                                          Back to the drawing board for me then I guess, as I cannot see how QS would fulfil this use case.

                                           

                                          Thanks

                                            • Re: QS S3 Connection for Non Public files
                                              Levi Turner

                                              Either by using the S3 connector in the Qlik Web Connectors OR a complicated method referenced above.

                                               

                                              The schema of the hard method would be:

                                              • Setup a script to compute the exact values used in the variables.
                                                • e.g. node s3header-authorization.js outputs LET vAuthorization='xxxx-xxx-xxxxxx Credential=xxxxxxxxxxxxxxxx/20180130/us-east-1/s3/aws4_request, SignedHeaders=content-type;host;x-amz-content-sha256;x-amz-date, Signature=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; which is then stored in a .TXT or .QVS file. Repeat for all headers.
                                              • In the load script use include files to pull in these variables
                                                • e.g. $(Must_Include=lib://Scripts\s3header-authorization.qvs);
                                              • Setting up a process to reload those tokens every X minutes.

                                               

                                              It certainly could work but the S3 connector seems much easier to implement, in my view.

                                                • Re: QS S3 Connection for Non Public files
                                                  Craig Murray

                                                  Hi Levi,

                                                  QS does not provide the ability to code SHA-256, which the real-time generation of the AWS signature requires - only Hash256 which isn't the same.

                                                   

                                                  The s3 connector provides the error shown.

                                                   

                                                  Example code looks like it would work..(if SHA-256 encryption was indeed possible.)

                                                  dynamic AWS headers.PNG

                                                  ---------------------------------------------------------------

                                                  let date = TEXT(date(today(),'YYYYMMDD'));

                                                  let dateIS08601 = TEXT(REPLACE((DATE(now( ) ,'YYYYMMDD')&'T'&DATE(now() ,'hh:mm:ss')&'Z'),':',''));

                                                  let canonical_request =
                                                  'GET
                                                  /

                                                  host:axis360.s3.amazonaws.com'&chr(10)&
                                                  'x-amz-content-sha256:e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855'&chr(10)&
                                                  'x-amz-date:'&'$(dateIS08601)'&chr(10)&chr(10)&
                                                  'host;x-amz-content-sha256;x-amz-date'&chr(10)&
                                                  'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855';

                                                  let hash_canonical_request = Hash256(text('$(canonical_request)'));

                                                  let signing_key = Hash256(Hash256(Hash256(Hash256("AWS4" + "xxxx-xxxx-SHA256","$(date)"),"us-east-1"),"s3"),"aws4_request");

                                                  let String_to_Sign =
                                                  'AWS4-HMAC-SHA256'&chr(10)&
                                                  '$(dateIS08601)'&chr(10)&
                                                  '$(date)'&'/us-east-1/s3/aws4_request'&chr(10)&
                                                  '$(hash_canonical_request)';

                                                  let signature = Hash256('$(signing_key)','$(String_to_Sign)');


                                                  let vAuthorization='xxxx-xxxx-SHA256 Credential=xxxxxxxxxxxxxxxx/'&'$(date)'&'/us-east-1/s3/aws4_request,
                                                  SignedHeaders=content-type;host;x-amz-content-sha256;x-amz-date, Signature='&'$(signature)';
                                                  let vSha256='e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855';
                                                  let vDate='$(dateIS08601)';

                                                  let URL_path = 'https://s3.amazonaws.com/publytics-ftp-backup/receipts/PUBLYTIC_RECEIPTS_20170914.TXT';
                                                  let vURL_path = replace(URL_path,'"', chr(34)&chr(34));

                                                   

                                                  Thanks for your help so far.

                                                   

                                                  I'm not sure how the s3 connector error can be resolved given that the same details can be passed though using a REST connection or Postman.

                                                   

                                                  Thanks