Usage scenarios for SQL Server native Web services

Server|web

Summary: Get detailed discussions about how to set up SQL Server for Web service access in a heterogeneous environment, and learn more about the Web in SQL Server

Information about the main scenarios of the service.

Download the relevant WebServicePerlScript.exe code example.

Brief introduction
In SQL Server, we added support for native XML Web services to the database engine. This feature is surrounded by well-known standards such as SOAP 1.2

, WSDL 1.1 and HTTP) are designed. The solution is built on these standards to support interoperability and service expansion in heterogeneous environments owned by most enterprises.

The new infrastructure added to SQL Server greatly facilitates exposing WEB services directly outside the server because the native SOAP stack is built into the database

The engine eliminates the need to use a middle-tier process, such as IIS, to achieve this goal. It also enables SQL Server to participate as a component in a service-oriented architecture,

Because the service provides binders in these new architectures. Native XML Web Services enable you to expose stored procedures as WEB services and to

The database server performs special T-SQL statements. In fact, we've created a new mechanism for accessing SQL Server based on SOAP; SOAP provides a

Almost the same functionality as the current tabular Data Stream (TDS) private binary protocol.

We'll start with a detailed review of how to set up SQL Server for Web service access in a heterogeneous environment. We'll look at how to use Perl scripts for databases

Management and a brief review of other scenarios that can use native WEB services.

Heterogeneous access
Consider an environment where applications running on non-Microsoft operating systems need to connect to SQL Server. For this type of application,

Our recommendation is to use SQL Server Licensing (Sql-auth) to connect to the SQL Server Web service. Let's examine how the mechanism works.

The first thing a user needs to do to expose a WEB service is to create an endpoint. Observe the data definition language (DDL) that is used to create endpoints, as shown below

Statement. It exposes a stored procedure named "GetCustomerInfo" to a Web service.

Note Although the term WebMethod is conceptually the same as [WebMethod] in asp.net, it has nothing to do with asp.net in other ways.

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/')
In order to maintain the "design security" theme in SQL Server, we do not allow ANONYMOUS access to SQL Server under any circumstances. This means

All connections need to authenticate at the HTTP transport level using one of the supported authentication schemes. BASIC is the most common and most widely used identity test

Card model because it is supported by most clients. However, it is also the least secure option because it requires a password to be sent in clear text. To avoid this problem,

We require that the endpoint be enabled for SSL whenever BASIC is selected as the authentication type. To enable SSL, you must perform the following command:

Httpcfg set ssl/i ip:port/h hash/g GUID where the hash is a certificate hash, the GUID is a globally unique identifier (GUID) string that identifies the entity that registered the certificate. Users can pass the certificate

Gets the hash value of the certificate by looking for the thumbprint value in the As a best practice policy, create a single GUID for each instance of SQL Server, and for the solid

The same GUID is used for all certificate registrations that are performed by an instance. You can use any tool to discover the GUID value. The Windows support tool is included with Httpcfg.exe.

Therefore, in this example, it will be:

Httpcfg Set ssl/i 1.1.1.1:443/h 4463b7899c499a38812a7bbe7d73f4d31d026b2f/g
' {2bb50d9c-7f6a-4d6f-873d-5aee7fb43290} '
Where 1.1.1.1 is replaced by the IP address of the computer hosting SQL Server.

So, how do you enable Sql-auth at the endpoint? This is done by specifying "login_type=mixed" in the payload section of the endpoint syntax. Flux

Specified "MIXED", you can authenticate an instance of SQL Server using integrated or SQL authentication. Now we have the SQL credentials to make

Flows as part of a payload (message). When this work is done, we have carefully ensured that the SOAP headers that transmit the credentials and the ws-security Username

Tag matches. Adhering to the ws-security standard naturally improves interoperability; For example, with a few lines of code, you can use Web Services

Enhancements 2.0 for Microsoft. NET (WSE) generates a user name token SOAP header.

As you can see in the discussion above, there are two levels of authentication:

Transport level

Message level

Now, let's delve into how these two levels of authentication work.

All requests are always authenticated at the transport level. Therefore, if the user submits an invalid BASIC authentication credential, the connection fails, and the

HTTP 401 Access Denied error. If the user successfully authenticates at the transport level, then we have two choices. We can use the transmission

Log in to SQL Server or credentials that come as part of a SOAP message. The selected credential is Sql-auth by the presence of a SOAP message

It is determined. If there are credentials in the SOAP message, we will attempt to log on to the SQL Server database using the Sql-auth credentials. If the method

Failed, we returned to the user failed, and we do not go back to using BASIC authentication credentials. If no credentials exist in the SOAP message, the

We will attempt to log on to SQL Server using transport credentials.

The SOAP message that contains the SQL credentials is as follows:

<?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>:

Specifying invalid credentials in a SOAP message produces the following SOAP error (which is returned to the user):

<?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 a 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 a 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>:

The solution leverages only HTTP, SOAP, BASIC authentication, and SSL, which makes it ideal for heterogeneous environments. In the next section, we will see how to benefit

Use this solution to create a Perl script to connect directly to SQL Server.

Using Perl scripts for management and monitoring
Through the Web services in SQL Server, you can connect to SQL Server from any platform that has Web service support. To illustrate this interoperability

, we will create a Perl script to connect to SQL Server. Perl is commonly used to create scripts to help manage and monitor database servers.

The following example shows how to create a Perl script to monitor the state of a database. SQL Server has introduced support for dynamic management views, which

The view provides dynamic state information about the server that is running. In this example, we created a Perl script so that the query named

Dm_exec_connections Dynamic view to monitor the number of active connections to and from the database.

We assume that the computer running this code has installed and configured Perl correctly.

The example here uses the ActiveState 5.8.x Perl package. The script leverages the following packages:

Install HTTP://THEORYX5.UWINNIPEG.CA/PPMS/CRYPT-SSLEAY.PPD

Install HTTP://THEORYX5.UWINNIPEG.CA/PPMS/XML-PARSER.PPD

Install HTTP://THEORYX5.UWINNIPEG.CA/PPMS/LIBXML-PERL.PPD

Install HTTP://THEORYX5.UWINNIPEG.CA/PPMS/XML-REGEXP.PPD

Install HTTP://THEORYX5.UWINNIPEG.CA/PPMS/XML-DOM.PPD

In this example, we need SSL because the Perl application will use BASIC authentication and Sql-auth, and the XML package needs to analyze

Respond and display the results.

Note For information about the complete script, see related downloads.

The following code block instantiates the SOAP Lite package. We need to explicitly require that the output be formatted as XML so that the response can be parsed.

My $soap = SOAP::Lite.:
Uri (' http://Adventure-Works/Customers/'):
Proxy (' Https://srikr-800/sql/sql_auth '):
Outputxml (1):

Next, we need to set the credentials for the connection. Because we intend to use sql-auth, we need to initialize the UsernameToken header as follows.

# Sample Yukon Security SoapHeader
# <wsse:security xmlns:wsse= 'http://docs.oasis-Open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd "":
# &LT;WSSE:USERNAMETOKEN&GT;:
# &LT;WSSE:USERNAME&GT;SQL_USER&LT;/WSSE:USERNAME&GT;:
#
<wsse:password type= "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#passwordtext "":
Foo-bar1 </wsse:password>:
# &LT;/WSSE:USERNAMETOKEN&GT;:
# &LT;/WSSE:SECURITY&GT;:
My $Username = SOAP::D ata->name (' Username ' => ' adminuser ');
My $Password = SOAP::D ata->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::D ata->name (' UsernameToken ')->value (\soap::D ata->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 authentication credentials are passed in by implementing the following stub:

Sub Soap::transport::http::client::get_basic_credentials {return ' User ' => ' Password ':
}
Note It is recommended that you do not store/reference passwords in script files. Users should follow the standard security guidelines when handling passwords.

Next, we call the Web method. Because we're going to execute a T-SQL batch statement, the code looks like this:

# # Invoking a sqlbatch to retrieve the number of connections $soap:
On_action (Sub {return ' "';}):
$method = SOAP::D ata->name (' Sqlbatch ')->attr ({xmlns =>:
' Http://schemas.microsoft.com/sqlserver/2004/SOAP '}):
@param = (SOAP::D ata->name (Batchcommands =>:
' Select session_id, Net_transport, protocol_type from Sys.dm_exec_connections '):

Finally, we analyze the XML response to retrieve the data:

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":
}
Run the Perl script to generate the following output:

Testing SOAP::Lite client against AdventureWorks Contacts sample Web service.
Calling Sqlbatch Server response ...
Server response ...
SESSION_ID:: Net_transport:: HTTP
Protocol_type:: SOAP
Connection_id::5ec2b4e2-39a6-4fa7-bbdb-144daed59a41
SESSION_ID:: Net_transport:: Shared Memory
Protocol_type:: TSQL
CONNECTION_ID:: 5ae50b7d-d919-4fbc-ba42-6069a12f4d30
SESSION_ID:: Net_transport:: Session Protocol_type:: TSQL
Connection_id::05830be9-f12f-429d-bbac-e4eeb2c528ef
PARENT_CONNECTION_ID:: 5ae50b7d-d919-4fbc-ba42-6069a12f4d30
The above output indicates that there are two connections to SQL Server: A connection uses the binary protocol TDS and is displayed as Protocol_type:: TSQL;

The other connection corresponds to the Soap/http connection that was generated when the Perl script was run.

I would like to draw your attention to the session_id column. The session identifier matches the SPID (session process identifier) in the database engine associated with the request.

The SPID with two entries is equal to 53 because one corresponds to a physical connection (Net_transport is shared memory) and the other corresponds to the same physical connection

The logical session. (For more information about multiple active result sets, see Multiple active results Sets (MARS) in SQL Server 2.) ) The Logos

This is evidenced by the fact that the parent_connection_id of a session matches the physical connection. For TDS, the connection and session are tightly linked;

In other words, users cannot join an existing session from a different physical connection. SOAP Access enables the user to join an existing session by specifying the appropriate session headers in the request.

The topic of using multiple sessions in SOAP needs to be specifically written. Interested readers can read SQL Server books Online to get information about such

How to enable and use session details.

Other programmes
Now, let's look at some other options. Most database applications have a large number of data-centric logic built into the stored procedure. Native XML Web Services

It becomes very easy to take advantage of this investment by making the stored procedure public as a WEB service. In addition, native WEB services can improve performance because data access is

Within the process, rather than being sent to the middle-tier process.

Find a service

When SQL Server hosts data for reference/lookup, you can use a Web service as an ideal mechanism for exposing that data. In this scenario, the database acts as a large amount of data

Repository. WEB services use the Database Engine query processing functionality to obtain results. The result set in such a query is well defined and approximately several KB. Examples of such scenarios include:

Product Catalog

A position-aware WEB service that returns locale-specific information (weather, traffic) to the user.

The employee directory for the Intranet

Report Generation Services

In many scenarios, the database server hosts the data that is the basis for the report. Within an Intranet, it is convenient to expose these reports as WEB services. Users can

To easily create T-SQL stored procedures to generate and expose reports using WEB services in SQL Server. You can also easily embed the results of a WEB service

into Office applications such as Excel and InfoPath. This not only makes it easier for client applications to retrieve data, but also eliminates the number

The Library administrator supports additional infrastructure to expose the burden of WEB services. Users can also run ad hoc queries and generate reports using the batch access feature of the native Web service.

Cross-platform access to user-defined types

SQL Server introduces support for user-defined types. With user-defined types (UDT), you can extend the scalar type system of the database (not just for the department

The unified type defines your own alias-the feature is already available in previous versions of SQL Server. For example, you can define a UDT type named point.

To capture the X and Y coordinates of the point. Native Web Services take advantage of the serialization framework provided in the common language runtime and enable transport of types such as XML.

The client platform can then deserialize the XML into an object defined on its platform. This enables the Java client to send and receive instances of the UDT.

Mobile Solutions

Now, any device that can parse XML and submit HTTP requests can access SQL Server. With this premise, plus rejoin the present when the connection is dropped

The ability to have sessions is ideal for developing applications for mobile devices and devices that are not connected in time, which makes it possible to access SQL Server anytime, anywhere.

Asynchronous service

The native Web service can be used in conjunction with SQL Service Broker, also provided through SQL Server, to build a solution that provides asynchronous services. Please

Consider an order Processing workflow. You can expose a SQL Server WEB service that receives orders, and by immediately confirming that it has received the order in

Row response. You can then enter the order into the service agent queue for processing. The fulfillment of an order may require other WEB services to be invoked. In fulfilling the order

form, we can notify the client by using any notification mechanism that the client has subscribed to.

Summary
Native XML Web Services take advantage of your investment in database servers and enable your database to work as a service provider. I've already explained the details.

How to use this feature to provide access to data hosted in SQL Server in a heterogeneous environment, and to describe other scenarios that are appropriate for native WEB services. Native XML Web

Services improve interoperability by enabling a wider range of clients to connect to SQL Server, facilitating the expansion of services.