SQL Server 本机 Web 服务的使用方案

2022-07-15 80酷酷网 80kuku.com

　　server|web

摘要：获得有关如何设置 SQL Server 以便在异类环境中进行 Web 服务访问的详细讨论，并且了解更多有关 SQL Server 中 Web

服务的主要方案的信息。

下载相关的代码示例。

简介
在 SQL Server 中，我们向数据库引擎中添加了对本机 XML Web 服务的支持。这一功能是围绕众所周知的标准（如 SOAP 1.2

、WSDL 1.1 和 HTTP）设计的。将解决方案建立在这些标准之上，可以在大多数企业都拥有的异类环境中支持互操作性和服务扩张。

添加到 SQL Server 中的新的基础结构大大有利于直接向服务器外部公开 Web 服务，这是因为将本机 SOAP 堆栈内置到数据库

引擎中消除了使用中间层进程（如 IIS）达到这一目标的需要。它还使 SQL Server 能够作为组件参与面向服务的体系结构，

因为服务在这些新的体系结构中提供了黏合剂。本机 XML Web 服务使您既可以将存储过程作为 Web 服务公开，而且可以针对

数据库服务器执行特殊的 T-SQL 语句。实际上，我们已经基于 SOAP 创建了一种新的访问 SQL Server 的机制；SOAP 提供了

与当前的 Tabular Data Stream (TDS) 专用二进制协议几乎相同的功能。

我们首先详细考察如何设置 SQL Server 以便在异类环境中进行 Web 服务访问。我们将查看如何使用 Perl 脚本进行数据库

管理，并且简要考察一下其他可以使用本机 Web 服务的方案。

异类访问
请考虑这样一个环境，在这里，运行在非 Microsoft 操作系统上的应用程序需要连接到 SQL Server。对于此类应用程序，

我们的建议是使用 SQL Server 授权 (SQL-Auth) 连接到 SQL Server Web 服务。让我们考察一下该机制是如何工作的。

要公开 Web 服务，用户需要做的第一件事情是创建一个终结点。请观察如下所示的用于创建终结点的数据定义语言 (DDL)

语句。它将一个名为“GetCustomerInfo”的存储过程公开为 Web 服务。

注尽管术语 WEBMETHOD 在概念上与 ASP.NET 中的 [WebMethod] 相同，但它在其他方面与 ASP.NET 无关。

CREATE ENDPOINT sql_auth_endpoint
STATE = STARTED AS HTTP( SITE = '*', PATH = '/sql/sql_auth',
AUTHENTICATION = (BASIC), PORTS=(SSL) )
FOR SOAP(
WEBMETHOD'GetCustomerInfo'
(
name='AdventureWorks.dbo.GetCustomerInfo',
schema=STANDARD ) ,
LOGIN_TYPE = MIXED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
BATCHES=ENABLED,
NAMESPACE = 'http://Adventure-Works/Customers/' )
为了保持 SQL Server 中的“设计安全”主题，我们在任何情况下都不允许对 SQL Server 进行 ANONYMOUS 访问。这意味

着所有连接都需要使用受支持的身份验证方案之一在 HTTP 传输级别进行身份验证。BASIC 是最常见和使用最广泛的身份验

证模型之一，因为它受到大多数客户端的支持。但是，它也是最不安全的选择，因为它要求以明文发送密码。为了避免该问题，

我们要求每当选择 BASIC 作为身份验证类型时，都要为 SSL 启用终结点。要启用 SSL，必须执行以下命令：

httpcfg set ssl /i IP:Port /h Hash /g Guid 其中，Hash 是证书哈希，Guid 是一个标识注册该证书的实体的全局唯一标识符 (GUID) 字符串。用户可以通过在 Certificate

中查找 Thumbprint 值来获取证书的哈希值。作为最佳实施策略，请为 SQL Server 的每个实例创建单个 GUID，并且对于该实

例执行的所有证书注册，都使用同一个 GUID。您可以使用任何工具来发现该 GUID 值。Httpcfg.exe 随附了 Windows 支持工具。

因此，在该示例中，它将成为：

httpcfg set ssl /i 1.1.1.1:443 /h 4463b7899c499a38812a7bbe7d73f4d31d026b2f /g
"{2bb50d9c-7f6a-4d6f-873d-5aee7fb43290}"
其中，1.1.1.1 会被宿主 SQL Server 的计算机的 IP 地址替换。

那么，如何在终结点上启用 SQL-Auth 呢？这是通过在终结点语法的 payload 节中指定“LOGIN_TYPE=MIXED”完成的。通

过指定“MIXED”，您可以使用集成式或 SQL 身份验证对 SQL Server 实例进行身份验证。现在，我们使 SQL 凭据能够作

为有效负载（消息）的一部分流动。在完成该工作时，我们已经小心地确保传输凭据的 SOAP 标头与 WS-Security Username

标记相匹配。遵循 WS-Security 标准自然可以提高互操作性；例如，只需很少的几行代码，就可以使用 Web Services

Enhancements 2.0 for Microsoft .NET (WSE) 生成用户名标记 SOAP 标头。

正如您可以在上述讨论中看到的那样，存在两种级别的身份验证：

传输级别

消息级别

现在，让我们深入探讨这两个级别的身份验证是如何工作的。

所有请求总是在传输级别进行身份验证。因此，如果用户提交无效的 BASIC 身份验证凭据，则连接失败，并且发生

HTTP 401 访问被拒绝错误。如果用户成功地在传输级别进行身份验证，则我们具有两个选择。我们可以使用传输凭

据或作为 SOAP 消息的一部分到来的凭据登录 SQL Server。所选的凭据是由 SOAP 消息中是否存在 SQL-Auth 凭

据确定的。如果 SOAP 消息中存在凭据，则我们将试图使用 SQL-Auth 凭据登录 SQL Server 数据库。如果该方法

失败，则我们向用户返回失败，并且我们不会后退到使用 BASIC 身份验证凭据。如果 SOAP 消息中不存在凭据，则

我们将试图使用传输凭据登录 SQL Server。

包含 SQL 凭据的 SOAP 消息如下所示：

<?xml version="1.0" encoding="utf-8"?>:
<soap:Envelope xmlns:soap=""
xmlns:xsi=""
xmlns:xsd="">:
<soap:Header>:
<Security xmlns:wsse="-
200401-wss-wssecurity-secext-1.0.xsd"
xmlns="- open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">:
<wsse:UsernameToken>:
<wsse:Username>user</wsse:Username>:
<wsse:Password Type="-
open.org/wss/2004/01/oasis-200401-wss-username-token-profile- 1.0#PasswordText">:
password </wsse:Password>:
</wsse:UsernameToken>:
</Security>:
</soap:Header>:
<soap:Body>:
<GetCustomerInfoxmlns="">:
<CustomerID>1</CustomerID>:
<OutputParam>Hello World</OutputParam>:
</GetCustomerInfo>:
</soap:Body>:
</soap:Envelope>:

在 SOAP 消息中指定无效的凭据会产生以下 SOAP 错误（该错误被返回给用户）：

<?xml version="1.0" encoding="utf-8"?>:
<SOAP-ENV:Envelope xml:space="preserve" xmlns:xsd=""
xmlns:xsi=""
xmlns:SOAP- ENV=""
xmlns:sql=""
xmlns:sqlsoaptypes="" xmlns:sqlrowcount="" xmlns:sqlmessage="" xmlns:sqlresultstream="" xmlns:sqltransaction="" xmlns:sqltypes="">:
<SOAP-ENV:Body>:
<SOAP-ENV:Fault xmlns:sqlsoapfaultcode="">:
<faultcode>SOAP-ENV:Client</faultcode>:
<faultstring>:
There was an error in the incoming SOAPrequest packet: Client, LoginFailure, AccessDenied
</faultstring>:
<faultactor>http://schemas.microsoft.com/sqlserver/2004/SOAP</faultactor>:
<detail xmlns:SOAP-1_2-ENV="">:
<SOAP-1_2-ENV:Code>:
<SOAP-1_2-ENV:Value>SOAP-1_2-ENV:Sender</SOAP-1_2-ENV:Value>:
<SOAP-1_2-ENV:Subcode>:
<SOAP-1_2-ENV:Value>sqlsoapfaultcode:LoginFailure</SOAP-1_2-ENV:Value>:
<SOAP-1_2-ENV:Subcode>:
<SOAP-1_2-ENV:Value>sqlsoapfaultcode:AccessDenied</SOAP-1_2-ENV:Value>:
</SOAP-1_2-ENV:Subcode>:
</SOAP-1_2-ENV:Subcode>:
</SOAP-1_2-ENV:Code>:
<SOAP-1_2-ENV:Reason>:
<SOAP-1_2-ENV:Text xml:lang="en-US">:
There was an error in the incoming SOAPrequest packet:
Sender, LoginFailure, AccessDenied
</SOAP-1_2-ENV:Text>:
</SOAP-1_2-ENV:Reason>:
<SOAP-1_2-ENV:Node>https://srikr-800.redmond.corp.microsoft.com:443/sql/sql_auth</SOAP-1_2-ENV:Node>:
<SOAP-1_2-ENV:Role>:
</SOAP-1_2-ENV:Role>:
<SOAP-1_2-ENV:Detail />:
</detail>:
</SOAP-ENV:Fault>:
</SOAP-ENV:Body>:
</SOAP-ENV:Envelope>:

该解决方案只利用 HTTP、SOAP、BASIC 身份验证和 SSL，这使它对于异类环境很理想。在下一部分中，我们将看到如何利

用该解决方案来创建 Perl 脚本，以便直接连接到 SQL Server。

使用 Perl 脚本进行管理和监视
通过 SQL Server 中的 Web 服务，可以从任何具有 Web 服务支持的平台连接到 SQL Server。为了说明这一互操作性

，我们将创建 Perl 脚本以连接到 SQL Server。Perl 被普遍用于创建脚本，以便帮助管理和监视数据库服务器。

下面的示例说明了如何创建 Perl 脚本以监视数据库的状态。SQL Server 已经引入了对动态管理视图的支持，这些

视图提供了有关正在运行的服务器的动态状态信息。在该示例中，我们创建了一个 Perl 脚本，以便通过查询名为

dm_exec_connections 的动态视图来监视与数据库之间的活动连接的数量。

我们假设运行这段代码的计算机已经正确安装和配置了 Perl。

这里的示例使用 ActiveState 5.8.x Perl 软件包。该脚本利用下列软件包：

安装

在该示例中，我们需要 SSL，因为 Perl 应用程序将使用 BASIC 身份验证和 SQL-Auth，并且 XML 软件包需要分析

响应并显示结果。

注有关完整脚本的信息，请参见相关的下载。

下面的代码块将 SOAP Lite 软件包实例化。我们需要明确要求将输出格式化为 XML，以便可以分析响应。

my $soap = SOAP::Lite ->:
uri('http://Adventure-Works/Customers/') ->:
proxy('https://srikr-800/sql/sql_auth') ->:
outputxml(1):

接下来，我们需要为该连接设置凭据。因为我们打算使用 SQL-Auth，所以我们需要按如下方式初始化 UsernameToken 标头。

# sample Yukon security SOAPheader
# <wsse:Security xmlns:wsse="- open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">:
# <wsse:UsernameToken>:
# <wsse:Username>sql_user</wsse:Username>:
#
<wsse:Password Type=" /oasis-200401-wss-username-token-profile-1.0#PasswordText">:
foo-bar1 </wsse:Password>:
# </wsse:UsernameToken>:
# </wsse:Security>:
my $Username = SOAP::Data->name('Username' => 'AdminUser');
my $Password = SOAP::Data->name('Password' => 'password') ->attr({Type =>:
'http://docs.oasis-open.org/wss/2004/01 /oasis-200401-wss-username-token-profile-1.0#PasswordText'}):
my $UsernameToken= SOAP::Data->name('UsernameToken') ->value(\SOAP::Data->value($Username, $Password)):
my $security = SOAP::Header->name(Security) ->attr({'xmlns' =>:
'http://docs.oasis-open.org/wss /2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'}) ->value(\$UsernameToken):

BASIC 身份验证凭据是通过实现以下存根传入的：

sub SOAP::Transport::HTTP::Client::get_basic_credentials { return 'User' => 'Password':
}
注建议不要在脚本文件中存储/引用密码。用户在处理密码时应该遵循标准的安全准则。

接下来，我们调用 Web 方法。因为我们将执行 T-SQL 批处理语句，所以代码如下所示：

# # Invoking a sqlbatch to retrieve the number of connections $soap ->:
on_action (sub { return '""';}):
$method = SOAP::Data->name('sqlbatch')->attr({xmlns =>:
'http://schemas.microsoft.com/sqlserver/2004/SOAP'}):
param = ( SOAP::Data->name(BatchCommands =>:
'select session_id, net_transport, protocol_type from sys.dm_exec_connections')):

最后，我们分析 XML 响应以检索数据：

for my $node($doc->getElementsByTagName("row")) { print "\n":
for my $kid ($node->getChildNodes) { print $kid->getNodeName():
print ":: ":
for my $gkid ($kid->getChildNodes) { print $gkid->getNodeValue():
#print the actual values for the columns } print "\t":
} print "\n":
}
运行该 Perl 脚本可以生成以下输出：

Testing SOAP::Lite client against AdventureWorks Contacts sample web service.
Calling sqlbatch Server response...
Server response...
session_id:: 54 net_transport:: HTTP
protocol_type:: SOAP
connection_id::5EC2B4E2-39A6-4FA7-BBDB-144DAED59A41
session_id:: 53 net_transport:: Shared memory
protocol_type:: TSQL
connection_id:: 5AE50B7D-D919-4FBC-BA42-6069A12F4D30
session_id:: 53 net_transport:: Session protocol_type:: TSQL
connection_id::05830BE9-F12F-429D-BBAC-E4EEB2C528EF
parent_connection_id:: 5AE50B7D-D919-4FBC-BA42-6069A12F4D30
上述输出表明与 SQL Server 之间存在两个连接：一个连接使用二进制协议 TDS，并且显示为 protocol_type:: TSQL；

另一个连接对应于在运行该 Perl 脚本时生成的 SOAP/HTTP 连接。

我希望将您的注意力引到 session_id 列上面。该会话标识符和与该请求关联的数据库引擎中的 spid（会话进程标识符）匹配。

有两个条目的 spid 等于 53，因为一个对应于物理连接（net_transport 是共享内存），另一个对应于在同一物理连接上进行

的逻辑会话。（有关多个活动结果集的详细信息，请参阅 Multiple Active Result Sets (MARS) in SQLServer 2。）该逻

辑会话的 parent_connection_id 与物理连接匹配这一事实证明了这一点。对于 TDS 而言，连接和会话紧密联系在一起；换

句话说，用户无法从不同的物理连接加入现有的会话。SOAP 访问使用户能够通过在请求中指定适当的会话标头来加入现有会话。

在 SOAP 中使用多个会话这一主题需要专门撰文加以阐述。感兴趣的读者可以阅读 SQL Server Books Online 来获得有关如

何启用和使用会话的详细信息。

其他方案
现在，让我们考察其他一些方案。大多数数据库应用程序都在存储过程中内置了大量以数据为中心的逻辑。本机 XML Web 服务

通过使得将存储过程公开为 Web 服务变得非常容易来利用这一投资。另外，本机 Web 服务还可以提高性能，因为数据访问是在

进程内发生的，而不是被发送到中间层进程。

查找服务