SQL Server 2005 Web Services (DAT329)

The session covered clearly the new features of SQL Server 2005 in the fields of Web Services support.

Key features showed were:

  • Requires Windows Server 2003 or Windows XP due to HTTP.SYS kernel mode driver
  • No requirements for IIS
  • Configuration: Endpoint + supported operations
  • It works on different kind of ports that are configurable: clear_port, ssl_port
  • Security is implemented and enforced by design:
    • Anonymous and Basic Authentication, requires ssl_port. Basic Authenticaton over clear_port simply doesn't work, the SQL statement fails if you try to do it
    • Integrated and Digest authentication, supports both of the ports
    • WS-Security support for UsernameToken (only in Beta 2)
    • We can restrict access to the endpoint based on IP address filters (RESTRICT_IP and ALLOW_IP options)
  • WebMethods can map to SP or UDF
  • We can expose not only SP that SELECT data FOR XML, but also standard SELECT statements
  • Result depens on FORMAT: ALL_RESULTS or ROWSET_ONLY
  • BATCHES=ENABLED allows any SQL statement via SOAP
    • Very dangerous!!
  • The URL can be invoked with querystring parameters: ?WSDL and ?WSDLSIMPLE
    • WSDL: contains the description of all SQL types
    • WSDLSIMPLE: provides only description of the service
  • Customizable WSDL: very good! We can define custom SP and make SQL invoke them, overriding the default way of working of SQL engine
  • For SP and BatchCommands we can get as result an array of objects (FORMAT=ALL_RESULTS) or a DataSets (FORMAT=ROWSET_ONLY)
  • Only members of the sysadmin role can create endpoints. There's also a CREATE ENDPOINT permission configurable
  • Only members of sysadmin, owners of endpoint and users with CONNECT permission assigned can access endpoints
  • Compatibility test done against many different types of SOAP clients (AXIS, JBuilder9 that is the same as AXIS :-), GLUE, SoapToolkit 3.0, and others) and also compliancy with WS-I BP1 (sounds good)

 

Personally I really don't like the idea to call SQL Server using a Web Service directly from the client, because we loose any opportunity to decouple, scale, architect our apps, etc.

I think that it's better to use SQLXML functionalities, only when are necessary, through a BIZ layer, rather than from a client.

The session demos were all about direct access to SQL from clients (SELECT * of rows into DataGrid and MessageBox) ... not so good, sorry about that. By the way the session was about SQL 2005 WS, not about distributed apps architecture ...

 

My last consideration is that I'd like to have full support for SOAP Messaging, using protocols different than HTTP/HTTPS.

No Comments