New HTTP Endpoints Create SQL Server 2005 Web Services

by Peter DeBetta of Winellect

SQL Server 2000 offers some capabilities for returning XML output via HTTP using SQLXML—and, of course, SQLXML supports Web services creation. Although not rocket science, setting up, configuring, and using Web services in SQL Server 2000 does require a little effort (see the http://www.microsoft.com/sql/techinfo/xml/default.asp SQLXML documentation about Web services in SQL Server 2000).

When .NET came about, people began writing Web services in C# or VB.NET. They simply connected into SQL Server (or any data store, for that matter) to retrieve the data. .NET made creating Web services as simple as 1-2-3:

  1. Create some stored procedures.
  2. Write some WebMethods to return the data from said stored procedures.
  3. Presto! You have a Web service.

But now, the next generation of Web services is hitting the streets. SQL Server 2005 supports native HTTP SOAP via a feature known as HTTP Endpoints. For those who don’t know, Simple Object Access Protocol (affectionately known as SOAP) is a lightweight messaging protocol that Webopedia defines as follows:

“Short for Simple Object Access Protocol, a lightweight XML-based messaging protocol used to encode the information in Web service request and response messages before sending them over a network. SOAP messages are independent of any operating system or protocol and may be transported using a variety of Internet protocols, including SMTP, MIME, and HTTP.”

This new HTTP Endpoints feature in SQL Server 2005 is the subject of this article.

HTTP Endpoints

So what is an HTTP Endpoint? You may have heard that it is SQL Server 2005’s means for creating Web services, but it actually is much more. An HTTP Endpoint also is a means of creating interfaces via HTTP or TCP for SOAP, T?SQL, Service Broker, and even database mirroring. Although these other functions are very intriguing, this discussion concerns only the ability to create Web services in SQL Server—Web services that can return rowset data, scalar values, messages, and even errors, all of which are serialized into XML automatically. And, an HTTP Endpoint does all of this without requiring you to install IIS (it uses the Windows 2003 kernel module http.sys).

Because I like to learn from examples, I am going to teach by example. I demonstrate how to use HTTP Endpoints by walking through an example from start to finish. Before starting, here are a few notes to consider:

  • This demonstration uses the new demo database called AdventureWorks.
  • Native HTTP SOAP in SQL Server 2005 is not supported on Windows XP. If you want to try the examples, you need Windows Server 2003.
  • SQL Server 2005 Express Edition (the new MSDE) does not support HTTP Endpoints, so be sure to install the Developer Edition.
  • I do not explain how to use SQL Server Management Studio to execute SQL scripts. I trust you know how to do this.

Security

Because HTTP Endpoints are a server-level feature, security of endpoints is also on the server level. Of course, the serveradmin system role can create, alter, and drop endpoints from the SQL Server instance, but how do you allow developers to manage these endpoints without giving them the excessive permissions of this system role?

The answer lies in SQL Server 2005’s new ability to assign server-level permissions to logins, as shown here:

GRANT ALTER ANY HTTP ENDPOINT TO peter

With this command, you can allow the peter login to manage HTTP Endpoints without giving other unnecessary permissions. The following is a list of permissions that you can modify via GRANT, DENY, or REVOKE:

  • {GRANT|DENY|REVOKE} ALTER ANY ENDPOINT TO Login

    Controls the ability to alter any HTTP Endpoint; also allows permission to transfer ownership and connect to any endpoint

  • {GRANT|DENY|REVOKE} ALTER ON ENDPOINT:: EndPointName TO Login

    Controls whether a login can alter a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint

  • {GRANT|DENY|REVOKE} CONTROL ON ENDPOINT:: EndPointName TO Login

    Controls whether a login can alter or drop a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint

  • {GRANT|DENY|REVOKE} CONNECT ON ENDPOINT:: EndPointName TO Login

    Controls whether or not a login can connect to (execute requests against) an HTTP Endpoint

  • {GRANT|DENY|REVOKE} TAKE OWNERSHIP ON ENDPOINT:: EndPointName
    TO Login
    

    Controls whether a login can take ownership of the HTTP Endpoint

  • {GRANT|DENY|REVOKE} VIEW DEFINITION ON ENDPOINT:: EndPointName
    TO Login
    

    Controls the ability for a login to see the metadata (definition) of the HTTP Endpoint

Consider one last example. If I want to deny the peter login the ability to use an HTTP Endpoint named SQLEP_Financial, I would execute the following statement:

DENY CONNECT ON ENDPOINT::SQLEP_Financial TO peter

Providing a Source for Data

The first order of business is to create the code components that will be the source of information for the endpoint. I create two stored procedures and one user-defined function (UDF), as shown in Listing 1:

Listing 1

USE AdventureWorks
GO
CREATE PROCEDURE prProductList
AS
SELECT   Product.ProductID, Product.ProductNumber, Product.Name
FROM     Production.Product AS Product
         INNER JOIN Production.ProductInventory AS Inventory
             ON Product.ProductID = Inventory.ProductID
         INNER JOIN Production.Location AS Location
             ON Inventory.LocationID = Location.LocationID
WHERE    Product.ListPrice > 0
 AND     Location.LocationID = 6 --Products from Misc Storage only
ORDER BY Product.Name
GO
CREATE PROCEDURE prProductStockInfo
    @ProductID int
AS
IF (SELECT ListPrice FROM Production.Product
    WHERE ProductID = @ProductID) = 0
 BEGIN
    RAISERROR ('Product not available for retail sale', 11, 1)
 END
ELSE
 BEGIN
    SELECT  Product.ProductID, Product.ProductNumber,
            Product.Name, Product.Weight,
            Product.WeightUnitMeasureCode,
            Product.ListPrice, Inventory.Quantity
    FROM    Production.Product AS Product
        INNER JOIN Production.ProductInventory AS Inventory
            ON Product.ProductID = Inventory.ProductID
        INNER JOIN Production.Location AS Location
            ON Inventory.LocationID = Location.LocationID
    WHERE   Product.ProductID = @ProductID
     AND    Location.LocationID = 6 --Misc Storage
 END
GO
CREATE FUNCTION dbo.fnProductPhoto (@ProductID int)
RETURNS varbinary(MAX)
AS
 BEGIN
    DECLARE @largePhoto varbinary(max)

    SELECT  @largePhoto = ProdPhoto.LargePhoto
    FROM    Production.ProductPhoto AS ProdPhoto
    INNER JOIN Production.ProductProductPhoto ProdProdPhoto
        ON ProdPhoto.ProductPhotoID = ProdProdPhoto.ProductPhotoID
    WHERE   ProdProdPhoto.ProductID = @ProductID

    RETURN (@largePhoto)
 END

The first procedure returns a list of products (product ID, name, and number) from the AdventureWorks database. The second procedure returns more details about a product, including inventory levels. The UDF returns a single varbinary value containing the product photo.

More by Author

Must Read