Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

[teemus]

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

[chrisbrain]

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.

[teemus]

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

[teemus]

(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.<>c__DisplayClass47_0.<GetWorksheet>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>

[chrisbrain]

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

[teemus]

Hi @chrisbrain,

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

[chrisbrain]

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.

[teemus]

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?

[chrisbrain]

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

[teemus]

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