Introduction to SQL 2005 Native XML Web Services (SOAP)

  • Article

The Native XML Web Services (SOAP) being a new feature in SQL 2005, I like to provide a brief overview of what it is and possible scenarios where it may be more useful then using normal SQL connections. SOAP as many of you know stands for Simple Object Access Protocol. It is an open messaging standard that uses XML extensively and in SQL 2005, the information is transported over HTTP. The W3C definition of SOAP is available at https://www.w3.org/TR/soap12-part0/ if you like to know more about it.

From a data communication’s point of view, SOAP defines a specific structure in which each message must conform to. As mentioned the SQL 2005 implementation of SOAP communicates over HTTP, this allows the client application be to written in any language on any platform as long as the client application is able to open a TCP/IP socket. No additional driver is needed.

From a programming model’s point of view, SOAP combined with WSDL (Web Services Description Language) provides a mechanism for distributed remote procedure execution without the need to worry about the actual messaging structure. WSDL is also an open standard defined by W3C and detailed information is available at https://www.w3.org/TR/wsdl. A WSDL document basically translates to a .h file in typical C/C++ programming model. It contains the method definitions and signatures for each method exposed at that given location, typically an URL.

SOAP can be extremely useful in a heterogeneous environment, where there are different application systems running on different OS. Because of the usage of XML, each application system will be able to interpret the data in the same way.

As you are getting started with SOAP, please remember that for security reasons when a SOAP endpoint is first created, unless you specify the “STATE” to be “STARTED” as show below, the endpoint will not listen for any incoming SOAP messages.

CREATE ENDPOINT sql_endpoint
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (NTLM),
PORTS = ( CLEAR ),
SITE = 'ServerName'
)
FOR SOAP (
WEBMETHOD 'DayAsNumber' (name='master.sys.fn_MSdayasnumber'),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'master',
NAMESPACE = 'https://tempUri.org/'
);

Also for security reasons, please also remember that you must explicitly grant user access to each endpoint, else by default only SQL administrators and endpoint owner has access to connect to that endpoint. Permissions can be granted using:
   GRANT CONNECT ON ENDPOINT::sql_endpoint TO [user]

These information and additional information regarding SQL 2005 Native XML Web Services functionality can be found in Books Online under “Native XML Web Services. Another great overview article on this new functionality is up on MSDN here.

Jimmy Wu
Software Design Engineer/Test
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights