2

I wrote a WinHttp POST request in VBA. It works good as long as there is only one certificate installed on the computer. However, some users have multiple certs with similar certificate names and therefore it returns an error:

a certificate is required to complete client authentication

Any suggestions on how I can select the correct certificate when multiple certificates share similar names? I've tried using both the "friendly name" and the "CN" name of the cert.

Below is my code: 

Sub dapull()

Dim URL As String: URL = "https://ce.midwest.org/dart/xml/query"
Dim mfile As String

pulldate = Format(Worksheets("Sheet2").Range("date").Value, "yyyy-mm-dd")
mfile = "<?xml version=" & """" & "1.0" & """" & "?><Envelope xmlns=" & """" & "http://schemas.xmlsoap.org/soap/envelope/" & """" & "><Header/><Body><QueryRequest xmlns=" & """" & "http://markets.midwest.org/dart/xml" & """" & "><QueryResults day=" & """" & pulldate & """" & "><Location>BART</Location></QueryResults></QueryRequest></Body></Envelope>"
Set Req = New WinHttp.WinHttpRequest

With Req
    .Open "POST", URL, False
    .SetClientCertificate "CURRENT_USER\MY\name" '*this is the issue line
    .SetRequestHeader "content-type", "text/xml"
    .Send (mfile)
    .ResponseText
End With

End Sub
Teamothy
  • 2,000
  • 3
  • 16
  • 26
guice99
  • 55
  • 9

1 Answers1

0

I have the same issue, did you manage to solve it? VBA just picks the first one :( no way to list or to identify which is which (or at least sort by date or something else before picking up the certificate). If you did it, please let me know how

For now I "solved" it by asking people to copy their own right certificate into Trusted People section and put into my XLSM an option to switch the store so that it is picked up from CURRENT_USER\TrustedPeople\ instead of MY store. It works but it is not elegant as it needs the certificate to be manually re-copied every 6 or 12 months (but better than not working at all :) )

C S
  • 1
  • 1
  • Welcome to SO. Since you are asking also for a solution and you are saying yourself that your solution is not elegant, maybe it should be a comment instead of an answer? – fgamess Sep 20 '18 at 09:30