使用VBA使用凭据访问Web服务 [英] Accessing webservice with credentials using vba

问题描述

我正在为Access使用更长的脚本，有一次必须检查Web服务以获取文件的最新版本(文件名).仅可通过具有 https://webservice.example.com:1234/Server/test.jsp?parameter=value 之类的URL的浏览器访问此Web服务，然后必须使用标准浏览器进行身份验证用户名密码弹出.当然，如果我改用 https://user:[e-mail]:1234/Server/test.jsp?parameter = value 之类的方法，则可以跳过此弹出窗口.(请注意，此时的安全性与密码无关，仅是为了拥有密码而存在，将其存储为明文是完全可以接受的.)

I'm working on a longer script for Access and at one point it is necessary to check a webservice for the latest version of a file (the filename). This webservice is only accessible via a browser with an URL like https://webservice.example.com:1234/Server/test.jsp?parameter=value then it is necessary to authenticate with the standard browser username password pop up. Of course I could skip this pop up if I'd use something like https://user:[e-mail]:1234/Server/test.jsp?parameter=value instead. (Note that it is not about security at this point the password only exists for the sake of having a password and it's totally acceptable to store it as clear text)

此刻，我已经使用以下工作代码从另一个代码中获取信息网站:

At the moment I already use the following working code to get information from another website:

Dim appIE As Object
Dim sURL as String, infoStr as String
Set appIE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}") 'class id of InternetExplorerMedium
sURL = "https://webservice.example.com:1234/Server/test.jsp?parameter=value"
With appIE
    .Navigate sURL
    .Visible = False
End With

Do While appIE.Busy Or appIE.ReadyState <> 4
    DoEvents
Loop

infoStr = appIE.Document.getElementsByTagName("body").item.innerText

但是，如果我像在浏览器中那样将凭据添加到URL， sURL ="https://user:[e-mail]:1234/Server/test.jsp?parameter = value" 我将收到以下错误:

However, if I add the credentials to the URL as I would do in the browser sURL = "https://user:[e-mail]:1234/Server/test.jsp?parameter=value" I will get the following error:

运行时错误'-2146697202(800c000e)':对象的方法'navigate'"IWebBrowser2"失败

Runtime error '-2146697202 (800c000e)': method 'navigate' of object 'IWebBrowser2' failed

有人知道我添加凭据的原因为什么会失败，或者有人知道如何以不同的方式执行此操作吗?

Does anybody know why it is failing if I add the credentials or has anybody an idea how to do this differently?

推荐答案

如果您的网站需要基本身份验证，则使用基本身份验证标头进行身份验证相对容易.

If your website requires Basic authentication, it's relatively easy to authenticate using a basic authentication header.

我们需要能够对内容进行Base64编码，因此首先我们需要为此定义一个辅助函数:

We need to be able to Base64 encode content, so first we need to define a helper function for that:

Public Function ToBase64(Bytes() As Byte) As String
    Dim XMLElement As Object
    Set XMLElement = CreateObject("Msxml2.DOMDocument.6.0").createElement("tmp")
    XMLElement.DataType = "bin.base64"
    XMLElement.nodeTypedValue = Bytes
    ToBase64 = Replace(XMLElement.Text, vbLf, "")
End Function

然后是创建基本身份验证标头的第二个助手:

Then, a second helper to create a basic authentication header:

Public Function CreateBasicAuthHeader(Username As String, Password As String) As String
    'Assuming ASCII encoding, UTF-8 is harder
    CreateBasicAuthHeader = "Authorization: Basic " & ToBase64(StrConv(Username & ":" & Password, vbFromUnicode))
End Function

快速验证显示?CreateBasicAuthHeader("Aladdin"，"OpenSesame")返回 Authorization:Basic QWxhZGRpbjpPcGVuU2VzYW1l ，这是根据维基百科

A quick validation shows that ?CreateBasicAuthHeader("Aladdin", "OpenSesame") returns Authorization: Basic QWxhZGRpbjpPcGVuU2VzYW1l, which is the expected header according to Wikipedia

然后，您可以在 Navigate 方法中使用它:

Then, you can use this in the Navigate method:

Dim appIE As Object
Dim sURL as String, infoStr as String
Set appIE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}") 'class id of InternetExplorerMedium
sURL = "https://webservice.example.com:1234/Server/test.jsp?parameter=value"
With appIE
    .Navigate sURL, Headers:=CreateBasicAuthHeader("MyUsername", "MyPassword")
    .Visible = False
End With

Do While appIE.Busy Or appIE.ReadyState <> 4
    DoEvents
Loop

infoStr = appIE.Document.getElementsByTagName("body").item.innerText

这假定服务器要么希望使用ASCII编码，要么您的用户名和密码都只是ASCII字符，而服务器希望使用UTF-8编码.

This assumes that the server either expects ASCII encoding, or your username and password are both only ASCII characters and the server expects UTF-8 encoding.

这篇关于使用VBA使用凭据访问Web服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！