Introduction
Microsoft® SQL Server™ 2005 provides a standard mechanism for accessing the database engine using SOAP via HTTP. Using this mechanism, you can send SOAP/HTTP requests to SQL Server to execute:
- Transact-SQL batch statements, with or without parameters.
- Stored procedures, extended stored procedures, and scalar-valued user-defined functions.
Prior to SQL Server 2005, the only mechanism available to connect to SQL Server was through a custom binary protocol named Tabular Data Stream (TDS). With SOAP/HTTP access, we have provided an open and documented protocol that may be used as an alternative to connect to SQL Server. Providing SOAP/HTTP access enables a broader range of clients to access SQL Server, including "zero foot print" clients, because there is no longer a need to have a Microsoft Data Access Components (MDAC) stack installed on the client device trying to connect to SQL Server. It facilitates interoperability with .NET, SOAP Toolkit, Perl, and more on a variety of platforms. Since the SOAP/HTTP access mechanism is based on well-known technologies such as XML and HTTP, it inherently promotes interoperability and access to SQL Server in a heterogeneous environment. Any device that can parse XML and submit HTTP requests can now access SQL Server.
Many enterprises have heterogeneous environments in which applications that run on UNIX and Linux platforms might require connectivity to SQL Server. Traditionally, the only solution available to such users was to use either JDBC or ODBC drivers. The SOAP/HTTP access now provides another, low-cost alternative. It is extremely useful for scenarios where DBA's have scripts written in Perl that run on UNIX and manage a SQL Server resource. It is also useful in developing client applications that connect to SQL Server using smart integrated development environments (IDEs) that have built-in SOAP/HTTP support, such as Microsoft Visual Studio® .NET or Jbuilder. These IDEs generate proxy code that abstracts the communication with SQL Server and provides objects that the client applications can use. Using SOAP/HTTP also enables anytime, anywhere access to SQL Server, which makes it easier to develop applications for mobile or sporadically connected devices. Once a connection has been
established and the server has started processing requests, it can be monitored using existing mechanisms that TDS-based clients such as sqlclient, ODBC, and OLEDB use.
Requirements
SQL Server 2005–native Web services require Microsoft Windows Server™ 2003 as the operating system, because they rely on the kernel mode http driver http.sys that this version provides. Since SQL Server leverages the kernel mode http.sys driver, you do not necessarily need to have IIS installed to expose Web services out of SQL Server; this simplifies administration. Instead, you should base your decision to install IIS on application requirements. For example, certain applications benefit from having an explicit middle tier. In such cases, IIS would be useful.
HTTP Endpoints
Setting up SQL Server as a Web Service that can listen natively for HTTP SOAP requests requires creating an HTTP endpoint and defining the methods that the endpoint exposes. When an HTTP endpoint is created, it must be created with a unique URL that it uses to listen for incoming HTTP requests. For example, if you create an endpoint with the URL "http://servername/sql," SOAP requests that are sent to http://servername/sql will be picked up by http.sys. Http.sys will then route the SOAP requests to the SQL Server instance that hosts the endpoint associated with the URL. From there, the requests will be handed off to the SOAP processing layer within SQL Server.
A SQL Server instance can have multiple endpoints, each of which can expose any number of stored procedures (implemented using either Transact-SQL or CLR) as WebMethods on the endpoint and can be invoked via SOAP remote procedure calls (RPCs). A WebMethod can have a different name than the actual stored procedure that is being exposed. The WebMethod name is what is shown to the user in WSDL as the operation name.
Note It is important to call out that the WebMethod clause in the endpoint is specific to SQL Server 2005 and is unrelated to the ASMX WebMethod attribute.
Users have the ability to execute ad-hoc Transact-SQL statements against the endpoints. This is done by enabling batches on the endpoint using an optional clause in the data definition language (DDL). Enabling batches implicitly results in a WebMethod named "sqlbatch" being exposed to the user. These concepts are illustrated further in the next few sections.
CREATE HTTP ENDPOINT
HTTP Endpoints are created and administered using Transact-SQL DDL. Creating an HTTP Endpoint is the first step in enabling HTTP/SOAP access to SQL Server 2005. Each endpoint has a name and a collection of options that when combined define the behavior of the endpoint.
To illustrate how the CREATE HTTP ENDPOINT is used, let's take a look at a Hello World example for invoking a stored procedure via SQL Server Web Services.
First, create a stored procedure called hello world in the master database, using the following T-SQL. This stored procedure simply displays the string provided in the input parameter. CREATE PROCEDURE hello_world
(@msg nvarchar(256))
AS BEGIN
select @msg as 'message'
END
Next, use the following T-SQL to create the HTTP endpoint which will allow access to this stored procedure as a WebMethod: CREATE HTTP ENDPOINT hello_world_endpoint
AS
AUTHENTICATON = ( INTEGRATED ),
PATH = '/sql/demo',
PORTS = ( CLEAR ),
STATE = STARTED
FOR SOAP (
METHOD
'http://tempuri.org/'.'hello_world'
(NAME = master.dbo.hello_world)
WITH
BATCHES = ENABLED,
WSDL = DEFAULT
)
All endpoints are stored in master, in the metadata view master.sys.http_endpoints. An endpoint doesn't have any SOAP Methods unless you define them. In the above example, we exposed the stored procedure master.dbo.hello_world as WebMethod 'hello_world'; the WebMethod can have any name and, for example, could have been called as 'testproc1' under the 'http://tempuri.org' namespace. Specifying DEFAULT as the value for WSDL clause enables the endpoint to respond to requests for WSDL generating WSDL using the default format. You can suppress WSDL generation by setting WSDL=NONE in the above statement. We discuss the details of WSDL generation in a subsequent section.
Authentication and Security
HTTP Endpoints support the following standard authentication mechanisms: Basic, Digest, Integrated (NTLM, Kerberos), Anonymous - SQL Auth. You first authenticate at the HTTP transport level. Once that is successful, the users SID is used to authenticate with SQL. This is true for all options except ANONYMOUS. Specifying ANONYMOUS at the endpoint only implies that it is anonymous at the HTTP transport level, the client will still need to provide SQL Auth credentials in order to be able to authenticate with the database. When ANONYMOUS is specified an additional HTTP header is required that encodes user name and password. The header must be called "MS-SQLAuth" and the value must be the base64 encoding of a valid username:password. Administrators can also set IP based Restrictions on an endpoint basis, restricting access to endpoints by only allowing specified IP's or ranges of IP's access to HTTP endpoints. Conceptually, an "endpoint" is an "application"—all the methods that
implement a single application are mapped to an endpoint, and so security is applied to the endpoint to control access to the application. Endpoints are secure by design; listed below are a few items that help make endpoints secure.
- Off by default. No default endpoints or Web methods mapped; must be explicitly created and specified.
- Security checks also apply to objects, so a mapped stored procedure is only executable if the user has connect permissions on the endpoint, plus execute permissions on the stored procedure.
- No anonymous support for connecting to endpoints. All requests, including requests for WSDL are authenticated. Clients must authenticate, against SQL Server principles, in order to submit any request.
When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement: GRANT CONNECT ON HTTP ENDPOINT::hello_world_endpoint TO [DOMAIN\USER]
Clients on non-Microsoft platforms can connect to SQL Server by using either BASIC or SQL Auth. However, using BASIC or SQL Auth requires the channel to be secure, so users can connect only on ports that also have SSL enabled.
WSDL
WSDL is a document written in XML that describes a Web service. It specifies the location of the service and the operations (or methods) the service exposes. WSDL provides the information necessary for a client to interact with a Web service. Tools such as Visual Studio .NET and Jbuilder use the WSDL to generate proxy code that client applications can use to communicate with a Web service. If the endpoint has WSDL enabled, that endpoint will produce WSDL when it receives a request for it. The endpoint created earlier in this article will produce WSDL when an authenticated request is sent to it. A WSDL request is a simple HTTP get request of the form. http://servername/sql/demo?wsdl
The server queries the metadata associated with the endpoint and generates the WSDL dynamically. The WSDL generated provides rich type description of the parameters of the stored procedure. The server has the ability to generate different flavors of WSDL—we call it simple and complex WSDL, depending on whether we use primitive xsd types or complex types to describe the parameters in a request/response message. The default is to use complex types.
SOAP RPC: Method Invocation
In the endpoint created above we have exposed this stored procedure master.dbo.hello_world as a Web method that we can execute via SOAP RPC. The following is an example of the soap message that is sent to the server to invoke this SP via SOAP over HTTP. <SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<hello_world xmlns="http://tempuri.org/">
<msg>Hello World!</msg>
</hello_world>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
The result will be a SOAP envelope containing: <SqlRowSet1 xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
<row>
<message>Hello World!</message>
</row>
</SqlRowSet1>
Batches: AdHoc Queries
When BATCHES are ENABLED on an endpoint by using the T-SQL command, another SOAP method, called "sqlbatch," is implicitly exposed on the endpoint. The sqlbatch method allows you to execute T-SQL statements via SOAP. This method takes two parameters. The first parameter is named "<BatchCommands>" and is the batch of T-SQL statements. The second parameter is named "<Parameters>" and is optional; it contains an array of parameter information if the T-SQL statement used any parameters. For example, here is the body of the SOAP request that calls the sqlbatch method and executes a parameterized query. <sqlbatch xmlns="http://schemas.microsoft.com/SQLServer/2001/12/SOAP">
<BatchCommands>
SELECT EmployeeID, LoginID, Gender
FROM Employee
WHERE EmployeeID=@x
FOR XML AUTO;
</BatchCommands>
<Parameters>
<SqlParameter name="x" sqlDbType="Int" maxLength="20"
xmlns="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlParameter">
<Value xsi:type="xsd:string">1</Value>
</SqlParameter>
</Parameters>
</sqlbatch>
The response from this SOAP request will contain the following: <sqlresultstream:SqlXml xsi:type="sqlsoaptypes:SqlXml">
<SqlXml>
<employees EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
</SqlXml>
</sqlresultstream:SqlXml>
Administration and Management
We have seen how simple it is to create an endpoint and submit SOAP requests against the endpoint. Administration is simplified because we now have to administer only one component, namely SQL Server. We don't need to administer an IIS component. The endpoint abstraction provides greater flexibility to administrators, who can enable IP filtering. It also eliminates the need to open up another port because we reuse the port used for http/https Web traffic. You can also provision endpoints for access by only certain individuals by explicitly granting CONNECT privileges only to those users.
Conclusion
With native SOAP access we have provided a protocol based on well-known and documented standards such as SOAP/HTTP to access SQL Server. This promotes interoperability and facilitates outreach by enabling a broader range of clients to connect to SQL Server.
Collected By
Rajjan | 