topic Re: QS S3 Connection for Non Public files in Connectivity & Data Prep

QS S3 Connection for Non Public files

Anonymous — Fri, 19 Jan 2018 09:26:58 GMT

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 — Fri, 19 Jan 2018 15:17:45 GMT

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

Anonymous — Mon, 22 Jan 2018 11:45:27 GMT

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 — Fri, 26 Jan 2018 15:58:46 GMT

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

S3 Validation:

Connector setup:

Re: QS S3 Connection for Non Public files

Anonymous — Fri, 26 Jan 2018 17:01:52 GMT

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

Re: QS S3 Connection for Non Public files

Levi_Turner — Fri, 26 Jan 2018 19:47:03 GMT

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

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

Anonymous — Tue, 30 Jan 2018 10:48:54 GMT

Thanks Levi,

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

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

Thanks again

Re: QS S3 Connection for Non Public files

Anonymous — Tue, 30 Jan 2018 11:13:43 GMT

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;

Re: QS S3 Connection for Non Public files

petter — Tue, 30 Jan 2018 11:30:08 GMT

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

Anonymous — Tue, 30 Jan 2018 12:53:07 GMT

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 — Tue, 30 Jan 2018 13:26:01 GMT

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

Anonymous — Tue, 30 Jan 2018 15:03:35 GMT

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.)

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

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

Re: QS S3 Connection for Non Public files

Ken_T — Tue, 25 Aug 2020 18:51:32 GMT

@Levi_Turner can you give some possible causes for the 403 error? you mentioned it could be a permissions issue. permissions on the amazon side or on the qlik side? thanks!

Re: QS S3 Connection for Non Public files

priyankapassi — Thu, 01 Jul 2021 07:57:14 GMT

Any possible way to fetch txt files instead of CSV?Please help.

Thanks