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.
Syntax for Creating Endpoints
The full SQL syntax for creating an endpoint is:
CREATE ENDPOINT endPointName [ AUTHORIZATION login ] STATE = { STARTED | STOPPED | DISABLED } AS { HTTP | TCP } ( ) FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } ( ) ::= AS HTTP ( PATH = 'url' , AUTHENTICATION =( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] ) , PORTS = ( { CLEAR | SSL} [ ,... n ] ) [ SITE = {'*' | '+' | 'webSite' },] [, CLEAR_PORT = clearPort ] [, SSL_PORT = SSLPort ] [, AUTH_REALM = { 'realm' | NONE } ] [, DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ] [, RESTRICT_IP = { NONE | ALL } ] [, COMPRESSION = { ENABLED | DISABLED } ] [, EXCEPT_IP = ( { <4-part-ip> | <4-part-ip>: } [ ,...n ] ) ) ::=AS TCP ( LISTENER_PORT = listenerPort [ , LISTENER_IP = ALL | (<4-part-ip> | ) ] [ , RESTRICT_IP = ALL | NONE ] [ , EXCEPT_IP = ( { <4-part-ip> | <4-part-ip>: } [ ,...n ] ) ] ::= FOR SOAP( [ { WEBMETHOD [ 'namespace' .] 'method_alias' ( NAME = 'database.owner.name' [ , SCHEMA = { NONE | STANDARD | DEFAULT } ] [ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]) } [ ,...n ] ] [ BATCHES = { ENABLED | DISABLED } ] [ , WSDL = { NONE | DEFAULT | 'sp_name' } ] [ , SESSIONS = { ENABLED | DISABLED } ] [ , LOGIN_TYPE = { MIXED | WINDOWS } ] [ , SESSION_TIMEOUT = timeoutInterval | NEVER ] [ , DATABASE = { 'database_name' | DEFAULT } [ , NAMESPACE = { 'namespace' | DEFAULT } ] [ , SCHEMA = { NONE | STANDARD } ] [ , CHARACTER_SET = { SQL | XML }] [ , MAX_SOAP_HEADERS_SIZE = { int | DEFAULT }]) ::= FOR SERVICE_BROKER ( [ AUTHENTICATION = { WINDOWS* [ { NTLM | KERBEROS | NEGOTIATE* } ] | CERTIFICATE certificateName | WINDOWS[ {NTLM|KERBEROS|NEGOTIATE* } ] CERTIFICATE certificateName | CERTIFICATE certificateName WINDOWS[ {NTLM|KERBEROS|NEGOTIATE* } ]}] [ , ENCRYPTION = {DISABLED|SUPPORTED|REQUIRED*} [ALGORITHM {RC4*|AES|AES RC4|RC4 AES}]]} [ , MESSAGE_FORWARDING = { ENABLED | DISABLED* } ] [ , MESSAGE_FORWARD_SIZE = forwardSize ]) After creating an endpoint, you can alter (ALTER ENDPOINT) or drop it (DROP ENDPOINT), as well as grant permissions for it. This article concentrates on SOAP and HTTP, and ignores the options to use TCP, to provide service brokering, and to provide database mirroring.
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; GO CREATE ENDPOINT sql_endpoint STATE = STARTED AS HTTP( PATH = '/sql', AUTHENTICATION = ( INTEGRATED ), PORTS = ( CLEAR ), SITE = 'MYSERVER' ) FOR SOAP ( WEBMETHOD 'GetSqlInfo' (name='master.dbo.xp_msver', SCHEMA=STANDARD ), WEBMETHOD 'DayAsNumber' (name='master.sys.fn_MSdayasnumber'), WSDL = DEFAULT, SCHEMA = STANDARD, DATABASE = 'master', NAMESPACE = 'http://tempUri.org/' ); GOThe 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.
