*UPDATE AT THE END
I need help with using an API to authenticate into https://connect.garmin.com/signin/. I am using VBA and Power Query to automate the collecting of workout data from my Garmin account. As far as I can tell, the website uses cookie based authentication and a CSRF token.
I am testing my API calls in Postman, building the authentication request in VBA, and performing data collection in Power Query with the authenticated cookies. (I would use Power Query for the whole project, but it is not able to return the response headers/authenticated cookies)
I am trying to replicate the browser actions by collecting the cookies from response headers and CSRF token from the HTML body before making the authentication POST request, but I am not having much success. When I try to authenticate using this method the response status is 200 and response body is the login page with a "Something went wrong" message.
I have tried to follow OmegaStripes answer from this question to get a handle on the cookies, How to set and get JSESSIONID cookie in VBA
Is there a special way to handle CSRF tokens in Postman or VBA MSXML2.ServerXMLHTTP? Is there something I am fundamentally not understanding about cookie authentication?
If you need any further info from me, please let me know. Any help is greatly appreciated!
My modified version of OmegaStripes code is below,
Option Explicit
Sub GetCookie()
Dim sUrl, sRespHeaders, sRespText, aSetHeaders, aList, aSetBody, sCSRFToken, sBody
sBody = "username=USERNAME&password=PASSWORD&embed=false&_csrf="
' get cookie 1
sUrl = "https://connect.garmin.com/signin/"
XmlHttpRequest "GET", sUrl, Array(), "", sRespHeaders, sRespText
ParseResponse "^Set-(Cookie): (\S*?=\S*?);[\s\S]*?$", sRespHeaders, aSetHeaders
' get cookie 2 and CSRF Token
sUrl = "https://sso.garmin.com/sso/signin?service=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&webhost=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&source=https%3A%2F%2Fconnect.garmin.com%2Fsignin%2F&redirectAfterAccountLoginUrl=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&redirectAfterAccountCreationUrl=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&gauthHost=https%3A%2F%2Fsso.garmin.com%2Fsso&locale=en_US&id=gauth-widget&cssUrl=https%3A%2F%2Fconnect.garmin.com%2Fgauth-custom-v1.2-min.css&privacyStatementUrl=https%3A%2F%2Fwww.garmin.com%2Fen-US%2Fprivacy%2Fconnect%2F&clientId=GarminConnect&displayNameShown=false&consumeServiceTicket=false&generateExtraServiceTicket=true&generateTwoExtraServiceTickets=false&generateNoServiceTicket=false&globalOptInShown=true&globalOptInChecked=false&connectLegalTerms=true&locationPromptShown=true&showPassword=true"
XmlHttpRequest "GET", sUrl, aSetHeaders, "", sRespHeaders, sRespText
' parse project names
ParseResponse "^Set-(Cookie): (\S*?=\S*?);[\s\S]*?$", sRespHeaders, aSetHeaders
sCSRFToken = GetCSRFToken("name=" & Chr(34) & "_csrf" & Chr(34) & " value=" & Chr(34), sRespText)
' get authenticated cookies
sUrl = "https://sso.garmin.com/sso/signin?service=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&webhost=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&source=https%3A%2F%2Fconnect.garmin.com%2Fsignin%2F&redirectAfterAccountLoginUrl=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&redirectAfterAccountCreationUrl=https%3A%2F%2Fconnect.garmin.com%2Fmodern%2F&gauthHost=https%3A%2F%2Fsso.garmin.com%2Fsso&locale=en_US&id=gauth-widget&cssUrl=https%3A%2F%2Fconnect.garmin.com%2Fgauth-custom-v1.2-min.css&privacyStatementUrl=https%3A%2F%2Fwww.garmin.com%2Fen-US%2Fprivacy%2Fconnect%2F&clientId=GarminConnect&displayNameShown=false&consumeServiceTicket=false&generateExtraServiceTicket=true&generateTwoExtraServiceTickets=false&generateNoServiceTicket=false&globalOptInShown=true&globalOptInChecked=false&connectLegalTerms=true&locationPromptShown=true&showPassword=true"
XmlHttpRequest "POST", sUrl, aSetHeaders, sBody & sCSRFToken, sRespHeaders, sRespText
' parse project names
ParseResponse "^Set-(Cookie): (\S*?=\S*?);[\s\S]*?$", sRespHeaders, aSetHeaders
End Sub
Sub XmlHttpRequest(sMethod, sUrl, aSetHeaders, sPayload, sRespHeaders, sRespText)
Dim aHeader
With CreateObject("MSXML2.ServerXMLHTTP")
'.SetOption 2, 13056 ' SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
.Open sMethod, sUrl, False
For Each aHeader In aSetHeaders
.SetRequestHeader aHeader(0), aHeader(1)
Next
.Send (sPayload)
sRespHeaders = .GetAllResponseHeaders
sRespText = .ResponseText
End With
End Sub
Sub ParseResponse(sPattern, sResponse, aData)
Dim oMatch, aTmp, sSubMatch
If IsEmpty(aData) Then
aData = Array()
End If
With CreateObject("VBScript.RegExp")
.Global = True
.MultiLine = True
.Pattern = sPattern
For Each oMatch In .Execute(sResponse)
If oMatch.SubMatches.Count = 1 Then
PushItem aData, oMatch.SubMatches(0)
Else
aTmp = Array()
For Each sSubMatch In oMatch.SubMatches
PushItem aTmp, sSubMatch
Next
PushItem aData, aTmp
End If
Next
End With
End Sub
Function GetCSRFToken(sPattern, sResponse)
Dim lStart, lLength, sSubMatch
lStart = InStr(1, sResponse, sPattern) + Len(sPattern)
lLength = InStr(lStart, sResponse, Chr(34)) - lStart
GetCSRFToken = Mid(sResponse, lStart, lLength)
End Function
Sub PushItem(aList, vItem)
ReDim Preserve aList(UBound(aList) + 1)
aList(UBound(aList)) = vItem
End Sub
EDIT
I have changed approach to creating an instance of Internet Explorer, and automating the log in process. Not ideal, but I am able to sign in successfully. My issue now is I am not able to retrieve the HttpOnly Cookies required to maintain the logged in session using,
getCookie = objIE.document.Cookie
I have tried another of OmegaStripes answers (coincidentally) Retrieve ALL cookies from Internet Explorer but was unsuccessful.
Any suggestions on how to get this working with MSXML2.ServerXMLHTTP, an IE instance, or any other method would be greatly appreciated!
