使用VBA调用OAuth2 API获取AccessToken时遇401未授权问题求助

Hey there, let's break down why you're stuck with that frustrating 401 error—small, easy-to-miss mistakes in OAuth2 setup are usually the culprit here, and I spot a couple of key issues in your code.

1. Reversed Credential Order in Basic Auth #

The biggest red flag is how you're building the Basic Auth string. OAuth2's Basic Auth requires the format client_id:client_secret (or username:password for user-based auth), but you've flipped the order entirely:

PasswordnUsername = Password & ":" & Username  ' This is backwards!

You need to swap them to Username & ":" & Password—the server expects the client ID (or username, depending on the API's requirements) first, followed by the secret/password, before Base64 encoding. This alone is almost certainly causing your authentication failure.

2. Missing Content-Type Header #

When sending form data via POST (like your grant_type=password parameters), you have to tell the server how to parse the request body. Add this header right before calling Send:

xmlhttp.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

Without this, many servers won't recognize the form data you're sending, leading to auth errors even if your credentials are correct.

3. Minor: Case Consistency for "Basic" #

While most servers are lenient, the HTTP spec technically calls for Basic (capitalized) instead of basic in the Authorization header. It's a tiny tweak but can avoid edge-case issues:

xmlhttp.SetRequestHeader "Authorization", "Basic " + mdl_API_MB_ACCESS_TOKEN.Base64Encode(Username & ":" & Password)

Corrected Code Example #

Here's your code with all key fixes applied:

Username = "myusername"
Password = "myclientsecret"
' Fixed credential order
CredentialString = Username & ":" & Password
argumentString = "grant_type=password&username=myusername&password=mypassword"

Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")
xmlhttp.Open "POST", "https://api.url/partner/oauth/token", False
xmlhttp.SetRequestHeader "Authorization", "Basic " + mdl_API_MB_ACCESS_TOKEN.Base64Encode(CredentialString)
xmlhttp.SetRequestHeader "x-api-key", "myxapicode"
' Added required Content-Type header
xmlhttp.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.Send (argumentString)

Extra Checks to Rule Out Other Issues #

• Verify your Base64 encoding function works correctly: Test encoding username:password manually and compare it to your function's output—some functions add extra whitespace or newlines that break the auth header.
• Double-check that myusername and myclientsecret are exactly what the API provider gave you—typos or hidden spaces here are extremely common.
• Confirm the API endpoint URL is correct (no typos, uses the right environment like production vs sandbox).

内容的提问来源于stack exchange，提问作者Gregory