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.
a la/s

20 comments:

  1. UnknownFebruary 22, 2010 at 1:24 PM

    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

    ReplyDelete
  2. Javier DelgadoFebruary 22, 2010 at 4:44 PM

    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.

    ReplyDelete
  3. DuncanDaviesApril 1, 2010 at 1:23 AM

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

    ReplyDelete
  4. rethymno19May 6, 2010 at 11:55 PM

    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?

    ReplyDelete
  5. Javier DelgadoMay 25, 2010 at 10:08 AM

    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!

    ReplyDelete
  6. Neeraj KholiyaJuly 19, 2010 at 12:16 AM

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

    ReplyDelete
  7. SSL CertificatesOctober 20, 2010 at 7:04 PM

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

    ReplyDelete
  8. EV SSL CertificatesNovember 2, 2010 at 10:12 AM

    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.

    ReplyDelete
  9. Cheap SSL CertificatesMarch 6, 2011 at 1:55 AM

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

    ReplyDelete
  10. RapidSSLMarch 17, 2011 at 2:55 PM

    A good number of those things make sense to me.

    ReplyDelete
  11. Offshore SEOMay 27, 2011 at 2:38 PM

    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.

    ReplyDelete
  12. AnonymousJuly 1, 2011 at 7:56 AM

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

    ReplyDelete
  13. Wildcard SSL CertificateAugust 26, 2011 at 5:13 PM

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

    ReplyDelete
  14. Cheap GeoTrust SSLMarch 12, 2012 at 1:39 PM

    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.

    ReplyDelete
  15. Peoplesoft developersDecember 26, 2013 at 9:48 AM

    Well found this very informative keep updates.

    ReplyDelete
  16. Rachel BurrJune 15, 2014 at 1:21 PM

    This comment has been removed by a blog administrator.

    ReplyDelete
  17. SSL for Exchange ServersJune 25, 2014 at 10:52 AM

    Thanks for pointing this out ! Helped me a lot.

    ReplyDelete
  18. UnknownDecember 29, 2014 at 7:19 AM

    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.

    ReplyDelete
  19. UnknownDecember 29, 2014 at 7:19 AM

    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.

    ReplyDelete
  20. AruNovember 8, 2017 at 5:07 PM

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

    ReplyDelete