SQL Server 本機 Web 服務的使用方式情節

server|web

摘要：獲得有關如何設定 SQL Server 以便在異類環境中進行 Web 服務訪問的詳細討論，並且瞭解更多有關 SQL Server 中 Web

服務的主要方案的資訊。

下載相關的 WebServicePerlScript.exe 程式碼範例。

簡介
在 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 服務，使用者需要做的第一件事情是建立一個終結點。請觀察如下所示的用於建立終結點的資料定義語言 (Data Definition Language) (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="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">:
<soap:Header>:
<Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-
200401-wss-wssecurity-secext-1.0.xsd"
xmlns="http://docs.oasis- open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">:
<wsse:UsernameToken>:
<wsse:Username>user</wsse:Username>:
<wsse:Password Type="http://docs.oasis-
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="http://Adventure-Works/Customers/">:
<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="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP- ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/SOAP"
xmlns:sqlsoaptypes="http://schemas.microsoft.com/sqlserver/2004/SOAP/types" xmlns:sqlrowcount="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlRowCount" xmlns:sqlmessage="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlMessage" xmlns:sqlresultstream="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlResultStream" xmlns:sqltransaction="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlTransaction" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes">:
<SOAP-ENV:Body>:
<SOAP-ENV:Fault xmlns:sqlsoapfaultcode="http://schemas.microsoft.com/sqlserver/2004/SOAP/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="http://www.w3.org/2003/05/soap-envelope">:
<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>:
http://schemas.microsoft.com/sqlserver/2004/SOAP </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 軟體包。該指令碼利用下列軟體包：

安裝 http://theoryx5.uwinnipeg.ca/ppms/Crypt-SSLeay.ppd

安裝 http://theoryx5.uwinnipeg.ca/ppms/XML-Parser.ppd

安裝 http://theoryx5.uwinnipeg.ca/ppms/libxml-perl.ppd

安裝 http://theoryx5.uwinnipeg.ca/ppms/XML-RegExp.ppd

安裝 http://theoryx5.uwinnipeg.ca/ppms/XML-DOM.ppd

在該樣本中，我們需要 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="http://docs.oasis- open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">:
# <wsse:UsernameToken>:
# <wsse:Username>sql_user</wsse:Username>:
#
<wsse:Password Type="http://docs.oasis-open.org/wss/2004/01 /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 服務還可以提高效能，因為資料訪問是在

進程內發生的，而不是被發送到中介層進程。

尋找服務

當 SQL Server 宿主要資料以供引用/尋找時，可以使用 Web 服務作為公開該資料的理想機制。在該方案中，資料庫充當大量資料

的儲存庫。Web 服務利用資料庫引擎查詢處理功能來擷取結果。此類查詢中的結果集定義良好，並且大約為幾個 KB。此類方案的樣本包括：

產品目錄

向使用者返回特定於地區的資訊（天氣、交通）的具有位置意識的 Web 服務。

用於 Intranet 的僱員目錄

報告產生服務

在很多方案中，資料庫伺服器宿主作為報告基礎的資料。在 Intranet 內部，將這些報告公開為 Web 服務是很方便的。使用者可

以輕鬆地建立 T-SQL 預存程序，以便使用 SQL Server 中的 Web 服務產生和公開報告。您還可以輕鬆地將 Web 服務的結果嵌

入到 Office 應用程式（如 Excel 和 InfoPath）中。這不僅使用戶端應用程式可以更加容易地檢索資料，而且還免除了數

據庫管理員的支援附加基礎結構以便公開 Web 服務的負擔。使用者還能夠使用本機 Web 服務的批處理訪問功能來運行特殊查詢和產生報告。

跨平台訪問使用者定義的類型

SQL Server 引入了對使用者定義型別的支援。藉助於使用者定義的類型 (UDT)，您可以擴充資料庫的標量類型系統（不僅僅是為系

統類型定義您自己的別名 — 該功能在以前版本的 SQL Server 中已經可用）。例如，您可以定義一個名為 Point 的 UDT 類型，

以捕獲點的 x 和 y 座標。本機 Web 服務利用了公用語言運行庫中提供的序列化架構，並且啟用了諸如 XML 之類類型的傳輸。

然後，用戶端平台可以將該 XML 還原序列化為在其平台上定義的對象。這就使 Java 用戶端能夠發送和接收 UDT 執行個體。

移動方案

現在，任何能夠分析 XML 和提交 HTTP 要求的裝置都可以訪問 SQL Server。有了這一前提，再加上在丟棄串連時重新加入現

有會話的能力，非常適合於為行動裝置和不定時串連的裝置開發應用程式，而這又使得隨時、隨地訪問 SQL Server 成為可能。

非同步服務

可以將本機 Web 服務與 SQL Service Broker（也通過 SQL Server 提供）結合使用，以便構建提供非同步服務的解決方案。請

考慮一個訂單處理工作流程。您可以公開一個 SQL Server Web 服務，該服務接收訂單，並且通過立即確認它已經收到了該訂單進

行響應。然後，可以將該訂單輸入到服務代理程式隊列中，以便進行處理。訂單的履行可能需要調用其他 Web 服務。在履行該訂

單時，我們可以使用用戶端已經預訂的任何通知機制來通知該用戶端。

小結
本機 XML Web 服務利用了您在資料庫伺服器方面的投資，並且使您的資料庫能夠作為服務提供者參與工作。我已經詳細說明了

如何使用該功能提供對異類環境中 SQL Server 中宿主的資料的訪問，並且描述了其他適合本機 Web 服務的方案。本機 XML Web

服務通過使範圍更為廣泛的用戶端能夠串連到 SQL Server，提高了互通性，促進了服務的擴張。