Non-interactive login using VBA question

[boacon]

Hi,
Has anyone worked out how to login using the non-interactive/bot login method in VBA?
The bf documentation:
https://api.developer.betfair.com/se...28bot%29+login

shows examples in Java and Python but not in VBA. The sample Excel workbook does not shed any light on this as well.

Following the above documentation, I've got it working with certificate generation via openssl, linking the certificate to my account and testing using curl....but I'm struggling with the vba implementation....doing my head in.
I know I could simply build a wrapper around the supplied curl command to grab the session id but would prefer to do a more elegant implementation through httprequest etc.
Is there anyone that can assist?
Thanks

[PeeOoo]

I am new to this and have just managed an interactive login in excel with a http request retrieving the ssoid. If that's any good to you let me know and I'll post the code.

[robne2000]

I'd find that useful - currently trying to get non interactive login working, but struggling a little.

[boacon]

non interactive login with vba

thanks PeeOoo
I would find that useful too.....

[robne2000]

I found a C# version and converted in to VB.NET if anyone wants that?

[lmlac]

I have a 3 line proof of Fermat's Last Theorem. Don't suppose anybody's interested but if they are I'll post it at a later date.

[boacon]

Funny...surprising no-one has figured out the vba implementation

[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

[PeeOoo]

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

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]

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.

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

[boacon]

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

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

[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