Lesson 9 Using Native HTTP Support
Introduction
In a service-oriented architecture, you can use native Hypertext Transfer Protocol (HTTP) support to publish Web services that are based on Simple Object Access Protocol (SOAP) directly from Microsoft SQL Server. 2005. This module teaches you how to create HTTP endpoints that make database services available to Web services clients.
Objectives
After completing this lesson, you will be able to:
- Define Web services and SOAP.
- Describe the features of HTTP support in SQL Server 2005.
- Define scenarios in which native HTTP support is a suitable solution.
- Describe the architecture of native HTTP support in SQL Server 2005.
- Describe the native HTTP support features in SQL Server 2005.
Contents
What Are Web Services and SOAP?
Native HTTP Support
Why Use Native HTTP Support?
Native HTTP Architecture
How to Create an HTTP Endpoint
How to Secure an HTTP Endpoint
How to Reserve a URL Namespace
How to Build an HTTP Endpoint Client Using .NET
Check Your Understanding
What Are Web Services and SOAP?
Introduction
Problems with existing development models for distributed applications have forced developers to look for alternatives. With the rapid adoption of Web standards, it is natural that solutions based on Web standards would be considered. This has led to the evolution of Web services and SOAP.
Web services
A Web service provides programmable logic, accessible over the Internet or an intranet that serves as a building block for creating distributed applications. Like components, Web services are black boxes: they encapsulate the implementation and provide an interface for communicating with the Web service.
The advantages of using Web services include:
- Interoperability. Web services communicate using HTTP and XML, so any network node that supports these technologies can both host and access Web services.
- Multilanguage support. Developers can write Web services in any language.
- Reusing existing applications. It is very easy to expose existing components and libraries as Web services. Most companies have a large number of existing components, libraries, and applications. It may be more cost effective to reuse the functionality in these software resources than to implement them again.
- Use of industry-supported standards. All of the major vendors support Web Service-related technologies, specifically, HTTP, XML, and SOAP. This kind of support makes it very easy for heterogeneous systems to communicate.
SOAP
Simple Object Access Protocol (SOAP) is a protocol for the exchange of information in decentralized, distributed environments. Exchanging SOAP messages provides a useful way for communicating with Web services.
A SOAP message consists of an XML document with a header and a body. SOAP messages are fundamentally one-way transmissions from a sender to a receiver. SOAP does not define any application semantics such as a programming model or implementation-specific details. However, Web services require a request/response model. A solution is to send SOAP messages within the body of an HTTP request and response. This solution provides the required model for Web services.
Example of Web services using SOAP
A Web site can offer features based on Web services. In a travel agency Website scenario, a user enters a destination city name on a Web page. The page uses the city name as a parameter in calls to several Web services using SOAP. The Web services return information to the page, including weather conditions, currency exchange rates, and so on, as a SOAP response message. The Web page formats the results and displays them coherently to the user.
From the user's perspective, this is a sophisticated Web site that offers a great deal of functionality. From a code perspective, the Web site is more of a graphical interface combining Web services from unrelated companies with very little effort.
Native HTTP Support
Introduction
SQL Server 2005 provides native HTTP support so that you can programmatically interact with a database across the Internet or through an internal firewall by using a Web service. This feature reduces the developer's effort in working with data across the Internet.
Exposes database objects Security
You can choose which individual database objects to expose through a Web service. Only the objects that you specify will be accessible by client applications. You can choose from the following objects:
- Stored procedures. You can expose any stored procedure (including those written using the .NET common language runtime, or CLR) or extended stored procedure. The stored procedure can return a result set, accept parameters, or simply perform some functionality without returning any information.
- User-defined functions. You can expose scalar-valued functions using a Web service. The functions can accept parameters.
- Transact-SQL batch statements. You can choose to allow execution of ad-hoc Transact-SQL statements by using a special method on the Web service. SQL Server ensures that clients have permission to execute each ad hoc statement.
Additionally, HTTP endpoints are used to provide remote access to Service Broker services.
Security
SQL Server performs security permission checks on the authentication credentials of client applications before allowing execution of stored procedures, user-defined functions, or Transact-SQL statements. This enables you to manage your Web service security from within SQL Server.
Why Use Native HTTP Support?
Introduction
There are many ways to retrieve data from a database within a local area network (LAN). For example, you can use Microsoft ADO.NET to access a database. However, sometimes it can be beneficial to implement your database as a service that is accessed using Web service protocols.
Heterogeneous data access
A business often accesses the same data from many different client applications. These applications can be Microsoft Windows.based, Webbased, or even console-based for batch processing. If each application is created using a different programming language, you might need to allow various data access mechanisms, including OLE DB, ODBC, or other custom mechanisms.
An alternative solution is to use a Web service to access the data. Because Web services are independent of both programming language and operating system platform, you can rely on them to work with any client application that can send and receive XML over an HTTP network connection. This includes applications running on handheld devices, mainframes, or desktops written in Java, Microsoft Visual Basic .NET, or any other programming language.
Limit open firewall ports
When you access SQL Server from a Web application, an administrator usually must open extra ports in the internal firewall to allow access from the Web server. Using Web services to access the database limits the open ports in the internal firewall to those that allow HTTP traffic.
Native HTTP Architecture
Introduction
To use native HTTP within SQL Server 2005, you must understand its basic architecture. This will help you to identify the requirements for installation as well as the role that various objects play in communication with clients.
Native HTTP requirements
You can access SQL Server 2005 using native HTTP when it is installed on Windows Server. 2003. You do not need to install Internet Information Services (IIS) because SQL Server handles listening for requests as well as security management.
HTTP.sys receives requests
The Windows HTTP application programming interface, HTTP.sys, receives the HTTP request from the client and forwards the request to SQL Server. HTTP.sys redirects the request based on a registered HTTP endpoint that signifies that SQL Server should handle the requested Uniform Resource Locator (URL). You register an HTTP endpoint with HTTP.sys using data
definition language (DDL) statements.
SQL Server produces WSDL documentation
Web Services Description Language (WSDL) is an industry standard that uses XML to describe a Web service and the methods that the Web service exposes. This documentation helps the client application developer to understand the methods that are available and the parameters that those methods expect. It is this WSDL documentation that allows client application developers to create a proxy class that greatly simplifies calls to a Web service.
Web service methods
The Web service exposes one or more Web methods that client applications can access using SOAP. You can bind a single method to a single SQL Server object such as a stored procedure. The same SQL Server object can be accessed by multiple Web methods, but a single Web method can access only one SQL Server object.
How to Create an HTTP Endpoint
Introduction
To access SQL Server 2005 using a Web service, you must create an HTTP endpoint that specifies the Web service information.
To create a HTTP endpoint, you must perform the following steps within the
CREATE ENDPOINT statement:
- Specify the transport protocol information.
- Specify the endpoint payload information, including the list of accessible Web methods.
Specifying the transport protocol information
The first part of the
CREATE ENDPOINT statement specifies the transport protocol information. This information includes items such as the endpoint listening port and the authentication mechanism.
You specify the transport protocol information using the following syntax.
CREATE ENDPOINT endPointName [AUTHORIZATION login]
STATE = { STARTED | STOPPED | DISABLED }
AS { TCP | HTTP | VIA | ...}
(
PATH = 'url'
, PORTS = ({CLEAR | SSL} [,... n])
[ SITE = {'*' | '+' | 'webSite' },]
[, CLEAR_PORT = clearPort ]
[, SSL_PORT = SSLPort ]
, AUTHENTICATION =({BASIC | DIGEST | INTEGRATED} [,...n])
[, AUTH_REALM = { 'realm' | NONE } ]
[, DEFAULT_LOGON_DOMAIN = {'domain' | NONE } ]
[, RESTRICT_IP = { NONE | ALL } ]
[, COMPRESSION = { ENABLED | DISABLED } ]
[,EXCEPT_IP = ({ <4-part-ip> | <4-part-ip>:<mask> } [,...n])
)
Example
The following example shows how to create an endpoint that provides two Web methods using integrated authentication.
CREATE ENDPOINT sql_AdventureWorks
STATE = STARTED
AS HTTP(
PATH = '/sql/AdventureWorks', AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR) )
FOR SOAP (
WEBMETHOD 'GetProducts'
(name='AdventureWorks.Production.GetProducts',
schema=STANDARD),
WEBMETHOD 'GetProductsNoSchema'
(name= 'AdventureWorks.Production.GetProducts',
schema=NONE),
WSDL = DEFAULT,
BATCHES = ENABLED,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://AdventureWorks/' )
The only difference between the two Web methods is that the second method does not return an XSD schema as part of the result sent to the client.
To view the WSDL documentation, you need to navigate a browser to
http://machinename/sql/AdventureWorks?WSDL.
How to Secure an HTTP Endpoint
Introduction
A Web service can open your database to trusted suppliers, customers, or other roles within your business. However, you will always want to ensure that only the appropriate people gain access to such a critical part of your business and that you can keep unwanted guests out.
To secure an HTTP endpoint, you must perform the following steps:
- Create appropriate user accounts or roles with database access.
- Grant permissions for any stored procedures or user-defined functions that the users or roles need to access using the Web service.
- Grant permissions to allow users or roles to connect to HTTP endpoints.
Create user accounts with database access
When you have decided which users you want to allow into your database, you must add them to the SQL Server 2005 security system by using the CREATE LOGIN statement.
The following example shows how to add a Microsoft Windows user account to SQL Server and assign a default database.
USE master
CREATE LOGIN [Adventure-Works\Peter]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks
You must also add a user account for the logon in the database, as shown in the following code sample.
USE AdventureWorks
CREATE USER Peter
FOR LOGIN [Adventure-Works\Peter]
Grant permissions for stored procedures
After the user exists and has rights to access the database, you must create the appropriate stored procedure or user-defined function permissions using the GRANT EXECUTE statement. If the user attempts to execute either type of object using a Web service, he or she must have EXECUTE permissions on the objects that the Web method calls.
The following example shows how to allow a user permission to execute the GetContact stored procedure.
USE AdventureWorks
GRANT EXECUTE ON Production.GetContact TO [Peter]
Grant permission to allow endpoint connection
After the user has permission to execute the stored procedure or user-defined function, you must grant him or her permission to connect to the HTTP endpoint. Use the
GRANT CONNECT ON ENDPOINT statement within the
master database to allow a user to connect using a Web service.
The following example shows how to allow a user permission to connect to an HTTP endpoint:
USE master
GRANT CONNECT ON ENDPOINT:: sql_AdventureWorks
TO [Adventure-Works\Peter]
