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:
- Create some stored procedures.
- Write some WebMethods to return the data from said stored procedures.
- 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.