Thursday, February 18, 2010

ExcelToCI under HTTPS with SSL certificate errors

One of our customers was trying to use an ExcelToCI template to load data in PeopleSoft environment which could only be accessed through HTTPS. However, as the certificate in the web server was expired, ExcelToCI would error out whenever the user wanted to create a new template or submit the data to the database.

Obviously, this issue would have been solved by simply renewing the web server certificate, but the customer was not in a position to do this now and we had to seek an alternative solution.

Our solution was to modify the ExcelToCI macros to tell them to ignore SSL certificate errors. To do so, we had to do the following changes on the CreateCITemplate.sendSOAPRequest_GetCIShape() and StagingAndSubmission.sendSOAPRequest_SubmitToDB() functions:
  • Replace the following variable declaration:
Dim xHTTP As XMLHTTP

by

Dim xHTTP As New MSXML2.ServerXMLHTTP40
  • Replace the following variable initialization:
Set xHTTP = New XMLHTTP

by

Set xHTTP = New MSXML2.ServerXMLHTTP40

  • Add the following line before calling the Send method:
xHTTP.setOption 2, 13056 'Ignore all SSL errors


The SetOption call is actually telling the Send method to ignore any SSL certificate error.

20 comments:

Unknown said...

I had a similar problem but just renewed the SSL certificate (it was much easier) but i've bookmarked this incase I have different circumstances of clients in the future

Javier Delgado said...

That's true, although this particular customer said it would take some weeks to get the certificate renewed because of internal approval procedures.
It's clear this is just a workaround.

DuncanDavies said...

This saved me a lot of searching today. Thank you.

rethymno19 said...

Javier,

We receive this error but the description is different (its just the number again). Also our certificates are definitely not expired.

This works in IE6 but breaks in IE8. Any idea on what this could be?

Javier Delgado said...

Michael,

I'm not sure what you mean, as IE is not involved in the process.

However, depending on the Excel version, the MsXML objects may have different properties. Still I don't see any relation with IE.

Thanks!

Neeraj Kholiya said...

I prefer winhttp in this case .. I tried using in a javascript through peoplesoft and achieved same results

SSL Certificates said...

This is true even if the client said it would take weeks to get the certificate renewed because of internal approval procedures.

EV SSL Certificates said...

If your SSL certificate is valid for the hostname, not expired and was issued by a trusted root certificate authority you might well be fine. Just change the parameters on the Connection tab and you’re good to go.

Cheap SSL Certificates said...

An SSL Certificate helps Web site visitors protect sensitive information and get a better idea of who they are trusting with it.

RapidSSL said...

A good number of those things make sense to me.

Offshore SEO said...

If the SSL certificate is valid for a hostname, not over, and has released a trusted certification authority, you may very well be in order. Only a change in the Parameters tab and you're done.

Unknown said...

Well if you have got SSL Certificate from trusted authority reseller, then you might not get any problems in renewing the certificate.

Wildcard SSL Certificate said...

Thanks for this i have bookmark the guide just incase i ever have to do this again next time my server crashes :(

Cheap GeoTrust SSL said...

Hi! Let me the part of this error and we want to bring update you that you can change your status of Certificate from connection tab. Thanks for sharing this error, we will surely create buzz for the same error.

Peoplesoft developers said...

Well found this very informative keep updates.

Rachel Burr said...
This comment has been removed by a blog administrator.
SSL for Exchange Servers said...

Thanks for pointing this out ! Helped me a lot.

Unknown said...

Hi Javier... this helped me a lot. In 8.9 the class used is ServerXMLHTTP60. This already takes care of the SSL connection. The only change needed is

xHTTP.setOption 2, 13056

This will set the option so as to ignore all ssl validation before the call of the send method.

Unknown said...

Hi Javier... this helped me a lot. In 8.9 the class used is ServerXMLHTTP60. This already takes care of the SSL connection. The only change needed is

xHTTP.setOption 2, 13056

This will set the option so as to ignore all ssl validation before the call of the send method.

Aru said...

Thanks for posting a solution for this issue & this helped me to fix my issue in my test environment