RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Using SQL Server 2005's New Web Services Capability

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.


magine that you're a developer at a company that wants to automate its warehouse inventory process. After the usual series of meetings, the managers agree with your original proposal to track the barcodes of every box in the warehouse. They equip the warehouse staff with hand-held Pocket PC devices that have barcode scanners and WiFi, and install a wireless router over the middle of the warehouse, connected to the corporate network.

Back in the central office, on a separate subnet with its own firewall, but inside the main corporate firewall perimeter, you have a Windows 2003 Server running Microsoft SQL Server 2005, which contains the inventory database. Your mission is to give the warehouse staff the capability to add and subtract scanned items to and from the inventory database, using their handheld devices.

One option that occurs to you is to install SQL Server 2005 Mobile Edition on each Pocket PC and set them up to synchronize with the central SQL Server 2005 instance. Your managers, however, in their infinite wisdom, have already bought the devices without enough storage to install SQL Server 2005 Mobile Edition and a local database.

On further reflection, you realize that you don't need or want the inventory database—which runs to tens of thousands of items—installed on the handheld devices. The devices never leave the warehouse, so they are always in wireless range, which means that they can always update the central database. Updating the central database in real time avoids a whole class of problems with update collisions.

Because of a horrible experience with a SQL Slammer infection in the past, the network administrators don't want to open the SQL Server TCP ports through the central office firewall if they can possibly avoid it. Basically, they want you to use Web services as the interface between the warehouse devices and the central database.

In the past you ran SQL Server 2000. To make that accept updates via Web services, you would probably have set up an internal IIS Web server instance and SQLXML to host the Web services.

For some of your other database applications, you use IIS-based Web services that extensively call middle-tier business objects that mediate for the database. For the high-volume extranet that your business partners use to exchange information with your company, you use multiple Web servers behind an IP switch, which call business objects, that in turn use the database. Both those architectures are overkill for the warehouse inventory application, however.

But SQL Server 2005 has come to your rescue, because it lets you set up native SQL Server Web services. In the balance of this article, I'll explain how that works.

Native HTTP Endpoints
To use native XML Web services in Microsoft SQL Server 2005, you need a full version of SQL Server 2005: the feature is not supported in SQL Server 2005 Express. You also need to install SQL Server 2005 on an operating system that includes the kernel-mode HTTP listener Http.sys, which currently means Windows Server 2003 or Windows XP SP2. Future Windows versions such as "Longhorn" will also include Http.sys. In older systems the HTTP listening capability was part of IIS.

The native SQL Server Web service provides a fully compliant SOAP server implementation that supports SOAP 1.1 and SOAP 1.2 clients. It has full support for parameterized batch execution, and can generate WSDL dynamically at the server.

To set up a Web service, you create an HTTP endpoint on the server. That endpoint can expose either a stored procedure (SP) or a user-defined function (UDF) as a Web method. It can also accept batches of SQL commands if you explicitly allow them. The endpoint can use a variety of authentication mechanisms, and it can use WS-Security for a limited subset of those authentication mechanisms; however, native HTTP endpoints cannot use any other WS-* standards. Therefore, if you're planning a large, heterogeneous service-oriented architecture, native HTTP endpoints may be too limited for you, and may not conform to all the standards your architecture requires.

For the purposes of this warehouse example, which operates on a private network inside a corporate firewall, you can rely on SSL to provide encryption of SQL Server authentication credentials, and WiFi Protected Access (WPA) to additionally encrypt the wireless communications. For a different scenario involving the public Internet, you would probably want to use a stronger authentication mechanism, like Kerberos.

SQL Server allows fine control of the permissions on HTTP endpoints. As is true for any SQL application, you should grant the minimum permissions to the minimum number of users. For this scenario, only the Warehouse group should have permission to use the HTTP endpoint for the inventory update.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date