如何从SQL Server登录WebAPI [英] How to logon webapi from SQL Server

问题描述

我尝试从SQL中的WebAPI获取数据.当我请求WebAPI链接时，它需要我的身份验证.我用下面的脚本.如何向该脚本添加身份验证用户?谢谢.

I try to get data from WebAPI in SQL. When I request WebAPI link, it wants an authentiction from me. I use below script. How can I add authentication user to this script? Thank you.

网络浏览器中的身份验证屏幕

代码:

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Body as varchar(8000) = 
'{
    "Subsystem": 1,
    "Exception": "",
    "Message": "I have done what you asked",
    "Time": "2014-06-09T11:16:35",
    "Attribute": { "Number of attempts": "0" }
}'  

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
EXEC  sp_OAMethod @Object, 'open', NULL, 'post','http://server/ws/v1/GET_DATA', 'false'

Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send', null, @body

Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText

Exec sp_OADestroy @Object

推荐答案

如弹出窗口所示，该站点正在使用基本身份验证.这是作为请求标头实现的:

As the popup indicates, the site is using Basic authentication. This is implemented as a request header:

Authorization: Basic `base64(user:pass)`

如通过msxml2.ServerXMLHTTP 提供身份验证信息中所述，进行设置此标头，您可以像对内容类型一样简单地在对象上调用 setRequestHeader .

As explained in Providing authentication info via msxml2.ServerXMLHTTP, to set this header, you can simply call setRequestHeader on the object like you already do for the content-type.

现在要创建授权字符串，您需要将字符串"Basic"，用户名，冒号和密码连接起来. SQL Server中的Base64编码中说明了如何在T-SQL中进行操作2005 T-SQL .

Now to create the authorization string, you need to concatenate the string "Basic ", the username, a colon and the password. How to do so in T-SQL is explained in Base64 encoding in SQL Server 2005 T-SQL.

请注意，通过始终提供Authorization标头，基本上可以完全绕过身份验证.实际上流程应该是这样的:

Note that by always providing the Authorization header you're basically bypassing authentication entirely. Actually the flow should be like this:

1. 执行未经身份验证的请求
2. 接收成功状态代码(完成)或401未经授权
3. 检查该401响应的响应标头:应该为 WWW-Authenticate:Basic (或其他，您也需要支持)
4. 使用 Authorization:Basic ... 标头重新发出请求.

1. Perform an unauthenticated request
2. Receive either a success status code (done) or a 401 Unauthorized
3. Inspect the response headers of that 401 response: it should be WWW-Authenticate: Basic (or others, which you'll also need to support)
4. Re-issue the request with the Authorization: Basic ... header.

因此，我宁愿使用功能完善的HTTP客户端来正确处理此问题，而不是手工处理来自T-SQL的HTTP请求.

So I'd rather just use a full blown HTTP client that properly handles this, as opposed to hand-crafting HTTP requests from T-SQL.

这篇关于如何从SQL Server登录WebAPI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！