Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
teemus
Partner - Contributor III
Partner - Contributor III

Error while using GetWorksheets in Qlik Google Drive and Spreadsheets Connector

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:

2020-10-19 - Copy_LI (3).jpg

Any inputs would be really helpful.

Thanks!

Best,

Sumeet

Labels (2)
1 Solution

Accepted Solutions
teemus
Partner - Contributor III
Partner - Contributor III
Author

I am able to retrieve data from a test worksheet (privately owned) using the GetSheetValues call, if that helps. 🙂

It has one tab viz. test tab and has data range A1 to B3 - specified as test tab!A1:B3 in the range param.

Regarding the 400 error for the GetWorksheet call, it still persists (we are now using the Dec-2020 version of QWC, and the account that is being used for this exercise has no added privileges from the API console apart from the ones QWC requests when it creates the API token).

You may try the resolution suggested here and see if it works.

Best,

Sumeet

View solution in original post

22 Replies
chrisbrain
Partner - Specialist II
Partner - Specialist II

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.

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
teemus
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

(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>
chrisbrain
Partner - Specialist II
Partner - Specialist II

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

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
teemus
Partner - Contributor III
Partner - Contributor III
Author

Hi @chrisbrain,

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

chrisbrain
Partner - Specialist II
Partner - Specialist II

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.

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
teemus
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Specialist II
Partner - Specialist II

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

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
teemus
Partner - Contributor III
Partner - Contributor III
Author

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. 

QWC_CAPI2_Error_XML.png

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