QS S3 Connection for Non Public files

[Anonymous]

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

[petter]

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.

[Anonymous]

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.

[Levi_Turner]

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

S3 Validation:

Connector setup:

[Anonymous]

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

[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):

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.

[Anonymous]

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

[Anonymous · Accepted solution]

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;

[petter]

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.

[Anonymous]

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