topic Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector in Connectivity & Data Prep

Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Tue, 21 Dec 2021 20:08:50 GMT

Hello Folks,

Greetings!

I am trying to fetch data from Google spreadsheets using Qlik Google Drive & Spreadsheets Connector.

As a part of the advanced installation process, following steps are concluded by the administration team -

1. Installation

2. SSL certificate creation (self-signed) - Exported & shared with us for client-side import

3. User creation for authentication

4. deploy.config update to enable SSL i.e. HTTPS access over default port 5555

From the development-end, we have ensured that we have completed the following steps

1. Certificate import

2. Sign-in using the credentials provided by admin

3. Authorization for access to Google Drive using OAUTH2.0

4. New data connection pointing towards the Web Connectors URL

5. These methods run seamlessly fine - canAuthenticate, ListSpreadsheets, ListWorksheets

6. The getWorksheet method fails with the said error:

Any inputs would be really helpful.

Thanks!

Best,

Sumeet

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Tue, 20 Oct 2020 07:34:04 GMT

Hi @teemus - Can you confirm what version of QWC you are using?

This is to do with the API call from the connector exe to the Google API (not so much to do with the SSL cert the browser is using to connector to the web interface).

I think we actually fixed this issue in the latest March 2020 patch release as this table was using a deprecated Google Sheets API endpoint.

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Tue, 20 Oct 2020 08:08:43 GMT

Hi,

This is the version we're using -

March-2020 (2.77.5.31203)

Below is the exception stack trace:

--- End of inner exception stack trace ---
   at System.Net.HttpWebRequest.GetResponse()
   at APIConnectorEngine.Qwc.HttpHelper.makeRequest(eHTTPMethod method, String url, Object postData, Object state, Int32 attempt)
   at APIConnectorEngine.Qwc.HttpHelper.makeRequestWithRetries(eHTTPMethod method, String url, Object postData, Object state, HttpCallConfig callConfig)</WebExcp>
        <Message>Error in writeSpreadsheet for 1s5azZc-HonnaxOviBG9DrO-KmmT4xhy4G_WCly6T9O8:_0 - Failed on attempt 1 to GET. (The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.)</Message>
        <Exception>APIConnectorInterfaces.Exceptions.APIConnectorException: Failed on attempt 1 to GET. (The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.) ---&gt; System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---&gt; System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.
   at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Net.TlsStream.ProcessAuthentication(LazyAsyncResult result)
   at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.PooledStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.ConnectStream.WriteHeaders(Boolean async)
   --- End of inner exception stack trace ---

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Tue, 20 Oct 2020 08:10:00 GMT

(cont'd)

at System.Net.HttpWebRequest.GetResponse()
   at APIConnectorEngine.Qwc.HttpHelper.makeRequest(eHTTPMethod method, String url, Object postData, Object state, Int32 attempt)
   at APIConnectorEngine.Qwc.HttpHelper.makeRequestWithRetries(eHTTPMethod method, String url, Object postData, Object state, HttpCallConfig callConfig)
   --- End of inner exception stack trace ---
   at APIConnectorEngine.Qwc.HttpHelper.makeRequestWithRetries(eHTTPMethod method, String url, Object postData, Object state, HttpCallConfig callConfig)
   at APIConnectorEngine.OAuth.OAuth2.OAuth2APIHelper.RequestWithAccessToken(eHTTPMethod method, String url, String accessToken, Object enclosedEntity, Object state, HttpCallConfig callConfig, TrackingInfo trackingInfo)
   at APIConnectorEngine.OAuth.OAuth2.OAuth2APIHelperWithRefreshToken.GetWithRefreshToken(String url, String refresh_token, TrackingInfo trackingInfo)
   at GoogleDriveConnector.GoogleDriveConnector.&lt;&gt;c__DisplayClass47_0.&lt;GetWorksheet&gt;b__0()
   at APIConnectorInterfaces.HTTP_and_Authorisation.Retry.WebRequestRetrier`1.Run(Func`1 makeRequestFunction)
   at GoogleDriveConnector.GoogleDriveConnector.GetWorksheet(RequestPackage r, Inputs inputs)</Exception>
        <ExceptionMessage>Failed on attempt 1 to GET. (The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.)</ExceptionMessage>

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Tue, 20 Oct 2020 08:30:29 GMT

OK Thanks - That's strange. Are you running any virus checkers on the machine QWC is running out of interest?

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Tue, 20 Oct 2020 11:07:46 GMT

Hi @chrisbrain,

There are no virus checkers on the server. Also, port 5555 is configured for QWC.

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Wed, 21 Oct 2020 08:35:16 GMT

This will probably be difficult to diagnose over the community forum but we can try a couple of things.

Can you confirm you can login to https://docs.google.com/ - this needs to be from the machine where the QWC process is running from and with the same account which is running the QWC exe.

Ideally also using IE.

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Thu, 22 Oct 2020 08:26:08 GMT

Hi @chrisbrain ,

Thank you for the suggestion. I asked the admin to try this step and oddly, it yielded a surprising result.

Usually, the user will start off on a GMail login and will be redirected to an ADFS which interfaces domain-user based login.

On the server, the admin personnel is not able to go past the G Mail login page itself. Their response quoted "it could be that ADFS is not active on server". Could this be something causing the error?

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Thu, 22 Oct 2020 09:51:07 GMT

Hi @teemus ,

Well - the short answer is I don't know - but it sounds like something is not configured correctly and likely something I cannot help with. But maybe this is the issue yes!

As the underlying error is:

Could not establish trust relationship for the SSL/TLS secure channel.

This sounds like a certificate issue but maybe this is just how the issue is manifesting itself.

My next suggestion, based on this error, was going to be to ask you to enable CAPI2 event logging on the server, repo the issue and then see if that tells you anything about potential certificate/trust issues - I think this is probably still worth a try.

I also should have asked - are you connecting to the internet through a proxy on your network and is QWC configured to use a proxy? This could also possibly be an issue (I guess).

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Thu, 22 Oct 2020 15:21:49 GMT

Hi @chrisbrain ,

Thank you for the suggestion once again.

The admin team helped in pulling CAPI 2.0 logs for QWC & a few errors did pop up when we attempted to run GetWorksheet.

Your thoughts?

I'm looking up what the "the certificate's cn does not match the passed value" means.

Meanwhile, the server team has decided to generate a new certificate.

Thanks & Regards.

Best,

Sumeet

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Thu, 22 Oct 2020 15:43:00 GMT

Hi - Not sure but that error doesn't look like it's related to QWC (it mentions QlikSenseEmailAdmin.exe).

Are you sure you located the correct matching log entry? Maybe you have several SSL issues from this machine?

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Thu, 22 Oct 2020 15:45:22 GMT

BTW - My current guess is this is not about the certificates you are generating but about the train of trust to the certificate for docs.google.com (or some other google certificate) unless you are using client certs to authenticate to ADFS/google?

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Fri, 23 Oct 2020 06:43:27 GMT

Hi @chrisbrain ,

Yes, there are 14 error events (on running the method) with task categories 'Verify Revocation' X 3, then 'Build Chain' X 1, followed by 'Verify Chain Policy' X 10. Could you please suggest a way I can share the event log file with you?

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Fri, 23 Oct 2020 07:40:28 GMT

Hi @teemus ,

Sorry but I don't think I can help you debug windows certificate issues. This doesn't seem like a QWC issue at this point but hopefully I have given you something to go on.

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

teemus — Fri, 23 Oct 2020 11:07:18 GMT

Hi @chrisbrain,

Most definitely, your support has been extremely valuable.

I will continue troubleshooting with the server team and keep you posted on the result.

Thanks once again! 🙂

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

chrisbrain — Fri, 23 Oct 2020 11:13:13 GMT

Great - and yes if you think you find anything which will help others or have anything I can try to help with let me know!

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

Senor_Dai — Tue, 04 May 2021 08:01:36 GMT

Hi @teemus - did you resolve this issue as we are experiencing the same .. Many thanks

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

basilusp — Tue, 04 May 2021 09:11:08 GMT

Hi everyone,

It seems that the problem might be general because we are experiencing it also since today (everything was fine yesterday).

So everything seems to be OK until the GetWorksheet part (we can authenticate, list spreadsheet and list worksheet). For the GetWorksheet, we get an 400 - Bad request error.

We are using the last patch of the December 2020 version of QWC.

Any ideas on how to resolve this ? Thanks a lot for the help !

Here attached is the exception details we get:

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

sid-ri-001 — Tue, 04 May 2021 11:02:59 GMT

Having the same issue in our environment also.

Qlik Google Drive Web Connector GetWorksheet method failing with Bad Request Error in all the environment

Since yesterday, all the call to Qlik Google Drive Web Connector GetWorksheet method is failing. Below is the error log which is shown in the log.

Failed on attempt 1 to GET. (The remote server returned an error: (400) Bad Request.)

APIConnectorInterfaces.Exceptions.APIConnectorException: Failed on attempt 1 to GET. (The remote server returned an error: (400) Bad Request.) ---> System.Net.WebException: The remote server returned an error: (400) Bad Request. at System.Net.HttpWebRequest.GetResponse() at APIConnectorEngine.Qwc.HttpHelper.MakeRequest(eHTTPMethod method, String url, Object postData, Object state, Int32 attempt) at APIConnectorEngine.Qwc.HttpHelper.MakeRequestWithRetries(eHTTPMethod method, String url, Object postData, Object state, HttpCallConfig callConfig) --- End of inner exception stack trace --- at APIConnectorEngine.Qwc.HttpHelper.MakeRequestWithRetries(eHTTPMethod method, String url, Object postData, Object state, HttpCallConfig callConfig) at APIConnectorEngine.OAuth.OAuth2.OAuth2APIHelper.RequestWithAccessToken(eHTTPMethod method, String url, String accessToken, Object enclosedEntity, Object state, HttpCallConfig callConfig, TrackingInfo trackingInfo) at APIConnectorEngine.OAuth.OAuth2.OAuth2APIHelperWithRefreshToken.GetWithRefreshToken(String url, String refresh_token, TrackingInfo trackingInfo) at GoogleDriveConnector.GoogleDriveConnector.<>c__DisplayClass53_0.<GetWorksheet>b__0() at APIConnectorInterfaces.HTTP_and_Authorisation.Retry.WebRequestRetrier`1.Run(Func`1 makeRequestFunction) at GoogleDriveConnector.GoogleDriveConnector.GetWorksheet(RequestPackage r, Inputs inputs) at GoogleDriveConnector.GoogleDriveConnector.WriteTable(RequestPackage r, String tableName, Inputs i) at GoogleDriveConnector.GoogleDriveConnector.onRequestTable(RequestPackage r) at APIConnectorInterfaces.APIConnectorBase.OnRequestTable(RequestPackage requestPackage) at APIConnectorEngine.Qwc.WebServer.QwcWebServer.ProcessHttpRequest(IInstalledConnectors installedConnectors, IWebRequestAndResponse reqAndResp, IAPIConnectorWrapper wrapper, AdditionalProcessHttpRequestCfg additionalProcessHttpRequestCfg)

Re: Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

sid-ri-001 — Tue, 04 May 2021 11:23:50 GMT

This might help

https://community.qlik.com/t5/Knowledge-Base/Qlik-Web-connectors-google-spreadsheets-connector-Error-quot-The/tac-p/1804868#M2525