Non-interactive login using VBA question

[TheOlis]

Non-interactive api login after windows 10 update

Hi, I was using this method to login to betfair api. Everything works fine, but after Windows 10 update (KB3140741) not working anymore. ResponseText = {"loginStatus":"CERT_AUTH_REQUIRED"} Anyone solved this?

Windows 10 Build 10586.218, version 1511
Microsoft Office 2016

-----------------------------
Dim oHTTP As Object: Set oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim uri As String: uri = "https://identitysso.betfair.com/api/certlogin"
oHTTP.Open "POST", uri, False
oHTTP.SetClientCertificate "Common Name"
oHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
oHTTP.setRequestHeader "X-Application", App_key
oHTTP.setRequestHeader "Accept", "application/json"
oHTTP.send "username=" & UserName & "&password=" & Password & ""

[betdynamics]

There is no NEED to use a certificate if you are happy to use the interactive login.

[schmoopie]

Do I need CertLogin or just Login for personal use??

Hi,
Has anyone managed to get CertLogin via Excel VBA going? I have been able to cobble together a function that works using:

https://identitysso.betfair.com/api/login

but not the CertLogin.

My betting bot is for my own personal use and not shared with anyone. Isn't the https url "secure"? I'm confused as to whether I NEED to use the certificate or if my login and keepalive code is fine as it is.

Any thoughts?

Michael

[Arbu]

Sorry for the delay, I thought I would get an email if anyone replied to my message. Will be more attentive in the future.

Anyway here is a test routine I wrote in excel vba, just to get me started. I had to mess around to get it working but it ended up pretty simple. Sorry for the rubbish code, it's just a test, not what I would use in a proper application.


Sub test1()

Dim cookies As String
Dim loggedIn As Boolean
Dim oHTTP As Object: Set oHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Dim ssoid As String
Dim uri As String: uri = "https://identitysso.betfair.com/api/login?username=" + USERNAME + "&password=" + PASSWORD + "&login=true&redirectMethod=POST&product=home.betf air.int&url=https://www.betfair.com/"
Dim x

oHTTP.Open "POST", uri, False
oHTTP.Send

If oHTTP.Status >= 400 And oHTTP.Status <= 599 Then
MsgBox ("Error Occurred : " & oHTTP.Status & " - " & oHTTP.statusText)
GoTo finish
End If

MsgBox (oHTTP.ResponseText)
MsgBox (oHTTP.getAllResponseHeaders())
'MsgBox (oHTTP.getResponseHeader("Set-Cookie"))

cookies = oHTTP.getResponseHeader("Set-Cookie")

x = InStr(1, cookies, "ssoid=") + 6
If x = 6 Then
MsgBox ("Error logging in")
GoTo finish
End If
ssoid = Mid(cookies, x, InStr(x, cookies + ";", ";") - x)

MsgBox (ssoid)

finish:

End Sub

Thanks for posting that. Not working for me though. My cookies string goes "wsid=...." rather than "ssoid=...". I've tried extracting the value and using that but then get errors telling me I'm using invalid session info. Any thoughts?

[odomoro Trader]

Helloo guys,

I did everything you guys said, but I keep having problems with the method "send".
Anyone have any other tips of what could be done?

My "curl line" works. I've had issue with certificate, but putting the "Common Name" correct this ok (apparently).

Now i am with Method 'Send' of object 'IWinHttpRequest' failed message =(

[wellwellwell]

p12 was the key.

OK. I managed to make it work.

p12 was the missing bit.

To create the p12 file, one way is:

openssl pkcs12 -export -in client-2048.crt -inkey client-2048.key -out client-2048.p12

Great work guys!

[wellwellwell]

Hi guys,

I am trying to implement this code. It would save my day if I could make this work! Thanks for sharing it.

It seems I managed to have my certificate registered by Windows. I don't get the complaint "A certificate is required to complete authentication". I suppose I am good with it (although I did do anything related to p12...).

However, now I get the dreadful message on send:
Method 'Send' of object 'IWinHttpRequest' failed

Any clue as to what is going wrong?

[PeeOoo]

Just saw your code. That's great.

Looks like the request headers and send are slightly different. I will try it out sometime.

Brilliant that you got it working.

Thanks

Paul

[boacon]

PeeOoo

You were nearly there. I managed to get it going.
Here is my example code with notes following:

Dim oHTTP As Object: Set oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim uri As String: uri = "https://identitysso.betfair.com/api/certlogin"

oHTTP.Open "POST", uri, False

oHTTP.SetClientCertificate "Common Name"

oHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
oHTTP.setRequestHeader "X-Application", myAppKey
oHTTP.setRequestHeader "Accept", "application/json"

oHTTP.send "username=myuser&password=mypassword"


If oHTTP.status >= 400 And oHTTP.status <= 599 Then
MsgBox ("Error Occurred : " & oHTTP.status & " - " & oHTTP.statusText)
Exit Sub
End If

result = oHTTP.responseText

Dim status: status = Mid(result, InStr(1, result, "loginStatus") + 14)
If InStr(1, status, "SUCCESS") > 0 Then

Dim start, length
start = 18
length = Len(result) - (start - 1) - 26
ssoid = Mid(result, start, length)

End If

Notes:
1. To get this going I needed to load the .p12 version of the certificate (and key) to my local certificate store (i'm on Windows 7 Home Premium)
2. On the betfair host however, the .p12 format is not permitted. I found that either the .crt file or the .pem file works when associated with my account. I'm using the .crt file
3. I found this article useful
http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx
At the bottom of the article it describes the use of winhttprequest object with 3 parameters: location, store and subject. Location and Store are optional. Through trial and error I found the using the Common Name only, as you described, worked for me. This name would need to be presumably unique as it will return the first certificate it finds using the string Common Name.

Trust this is of help.

[boacon]

thanks for sharing this
i'll try and finish it and if I succeed i'll post back

[PeeOoo]

Hi

I decided to have a go at the cert login yesterday and I could not find any decent examples. I managed to cobble together different bits and pieces from different places and languages, but after hours of trying everything I could think of I failed. However I think I have broken the back of it, but I don't have the time to go any further. So maybe someone else can finish it off.

Here is the code I created, afterwards I will give some more information.

---------------------------

Sub test2a()

Dim oHTTP As Object: Set oHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim uri As String: uri = "https://identitysso.betfair.com/api/certlogin"

oHTTP.Open "POST", uri, False
oHTTP.SetClientCertificate "LOCAL_MACHINE\My\betfairLogin"
oHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
oHTTP.setRequestHeader "X-Application", APP_KEY
oHTTP.setAutoLogonPolicy (2)
oHTTP.send ("{""username"":" + USERNAME + ",""password"":" + PASSWORD + "}")

MsgBox (oHTTP.responsetext)

If oHTTP.Status >= 400 And oHTTP.Status <= 599 Then
MsgBox ("Error Occurred : " & oHTTP.Status & " - " & oHTTP.statusText)
GoTo finish
End If

MsgBox (oHTTP.responsetext)
MsgBox (oHTTP.getAllResponseHeaders())

finish:

End Sub

---------------------------

1. In order to specify a certificate you need to use WinHttp.WinHttpRequest.5.1
2. You cannot read the .pem file contents and pass it as a string to SetClientCertificate
3. You need to create a .p12 file as per betfair instructions
4. You open explorer, right click the .p12 file and install to Local Machine in the Personal store
5. To reference the certificate you use LOCAL_MACHINE\My\<CN> where <CN> is the common name you used when creating the .csr file (This took ages to figure out, and I was just about to give up when I found a reference to it)

Up until I figured out point 5 I always got a runtime error "A certificate is required to complete authentication", afterwards I no longer got an error.

However, now the bad news: I now get an oHTTP.Status of 404, "not found".

I spent another couple of hours trying to figure out why this is but to no avail.

That's as much as I can do for now. If anyone else figures out the rest let me know please.

P.

[PeeOoo]

No problem, but I am not sure if I am going to bother with the cert login because it is only for my own use.

P.

[boacon]

Thanks PeeOoo
Tried the code and it works for me...many thanks
If you get the certlogin going would you mind posting it too

[PeeOoo]

Just found out you have to subscribe to thread to get email notifications, how thick am I?

P.

[PeeOoo]

Sorry for the delay, I thought I would get an email if anyone replied to my message. Will be more attentive in the future.

Anyway here is a test routine I wrote in excel vba, just to get me started. I had to mess around to get it working but it ended up pretty simple. Sorry for the rubbish code, it's just a test, not what I would use in a proper application.


Sub test1()

Dim cookies As String
Dim loggedIn As Boolean
Dim oHTTP As Object: Set oHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Dim ssoid As String
Dim uri As String: uri = "https://identitysso.betfair.com/api/login?username=" + USERNAME + "&password=" + PASSWORD + "&login=true&redirectMethod=POST&product=home.betf air.int&url=https://www.betfair.com/"
Dim x

oHTTP.Open "POST", uri, False
oHTTP.Send

If oHTTP.Status >= 400 And oHTTP.Status <= 599 Then
MsgBox ("Error Occurred : " & oHTTP.Status & " - " & oHTTP.statusText)
GoTo finish
End If

MsgBox (oHTTP.ResponseText)
MsgBox (oHTTP.getAllResponseHeaders())
'MsgBox (oHTTP.getResponseHeader("Set-Cookie"))

cookies = oHTTP.getResponseHeader("Set-Cookie")

x = InStr(1, cookies, "ssoid=") + 6
If x = 6 Then
MsgBox ("Error logging in")
GoTo finish
End If
ssoid = Mid(cookies, x, InStr(x, cookies + ";", ";") - x)

MsgBox (ssoid)

finish:

End Sub