Browse DevX
Sign up for e-mail newsletters from DevX


Using SQL Server 2005's New Web Services Capability : Page 3

Microsoft SQL Server 2005 lets you expose specific stored procedures, user-defined functions, and SQL queries for use via HTTP or secure HTTP, making SQL Server databases more accessible to non-Windows clients, and improving security by eliminating the need to open TCP ports for SQL Server.

Creating an Example Endpoint
So, for example, you could create a SOAP endpoint with the following SQL (adapted from a sample given in the SQL Server documentation):

   DROP ENDPOINT sql_endpoint;
   CREATE ENDPOINT sql_endpoint 
      PATH = '/sql', 
      PORTS = ( CLEAR ), 
      WEBMETHOD 'GetSqlInfo' 
                SCHEMA=STANDARD ),
      WEBMETHOD 'DayAsNumber' 
      DATABASE = 'master',
      NAMESPACE = 'http://tempUri.org/'
The preceding code sample creates the endpoint called sql_endpoint. There is nothing magical about the name. It begins by dropping any existing endpoint with the name sql_endpoint, which is no more than good hygiene for a SQL script that you might want to run more than once.

The code creates the endpoint in the started state. If you don't specify the state, it defaults to stopped. You use the disabled state only when you want the endpoint to stop listening completely.

Because the name of the server (given by the SITE argument) is //myserver, the HTTP option PATH = '/sql' means that the endpoint proper will listen on http://myserver/sql. The INTEGRATED authentication means that the server will use a challenge/response for either Kerberos or NTLM, depending on which one the client requests.

As mentioned before, for the warehouse application you can get away with BASIC SQL Server authentication if you use SSL encryption. To accomplish that, set AUTHENTICATION = (BASIC), and PORTS = (SSL) instead of PORTS = (CLEAR). You would also need to have an SSL certificate installed for the server.

The SOAP options given in this sample expose two Web methods, one for a stored procedure and one for a user-defined function. The default WSDL option means that SQL Server will automatically generate two descriptions for the endpoint: a standard description (see Listing 1) that provides strong typing for Web service clients built with Visual Studio 2005, and a simplified description (see Listing 2) that works well with a broad range of Web service clients, but doesn't provide strong typing.

In this case, the path for obtaining the default WSDL would be http://myserver/sql?wsdl, and the path for obtaining the simplified WSDL would be http://myserver/sql?wsdlsimple. The simple WSDL file is much shorter than the default WSDL file, and includes only native XSD types.

If you wanted to allow this endpoint to process arbitrary SQL, you could add BATCHES = ENABLED to the FOR SOAP section of the definition. For security, batches are disabled by default.

After creating the endpoint, you need to provide users with access to it. For example, to provide access to the Warehouse group, issue GRANT CONNECT ON ENDPOINT:: sql_endpoint TO warehouse.

For the warehouse example, you would probably have two Web methods, but they wouldn't be the ones shown above. Instead, they would expose stored procedures to add an item to inventory and subtract an item from inventory. Obviously, the procedures would each be single transactions, and take a parameter representing the UPC scanned from the barcode on the box.

The user interface on the handheld could be no more complicated than two buttons: ADD and REMOVE. The button-click handlers would call the Web services proxies generated by adding the Web reference of the WSDL for the endpoint to the project. Conceptually, you could create a client prototype in less than half an hour after setting up the Web service.

For further information
You can find extensive documentation for SQL Server's HTTP endpoints feature in the Using Native XML Web services in SQL Server 2005 section of SQL Server 2005 Books Online. That section can be found under the SQL Server Database Engine header.

You can also find an older (and slightly obsolete) general introduction to the subject online at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2005websvc.asp.

Finally, the book A First Look at SQL Server 2005 for Developers, by Bob Beauchemin, Niels Berglund, and Dan Sullivan, contains an excellent and detailed chapter explaining the ins and outs of Native HTTP endpoints. Only a few small points have changed since this was published in June 2004: most notably that Http.sys is now supported on Windows XP SP2.

Martin Heller is a Web and Windows programming consultant. He writes from Andover, Massachusetts.
Thanks for your registration, follow us on our social networks to keep up-to-date