Improve interoperability and application connectivity with SQL Server 2005 Web services support


The native XML data type in SQL Server 2005, which I wrote about in "Extending XML in SQL Server 2005," (June 2005, InstantDoc ID 45995), lets you combine XML functionality and relational-data storage in the systems you build so that your applications can work with data that might change frequently or vary in type. Another XML-related SQL Server 2005 feature that adds flexibility is native support for Web services, which lets DBAs access SQL Server by using SOAP through HTTP. This connection method enables interoperability with a variety of platforms—a great benefit to any SQL Server DBA working in a heterogeneous environment and supporting users on Linux or UNIX platforms. Web services support also simplifies development of client applications that connect to SQL Server through Visual Studio .NET or other smart IDEs. And using SOAP/HTTP connectivity also simplifies development of applications for mobile devices. Let's take a closer look at how Web services support works in SQL Server 2005.

SQL Server 2000 provided support for Web services through the Web Services Toolkit (SQLXML), which lets you set up a Web-service interface to SQL Server data through standard Web-service frameworks and Microsoft IIS. Although SQLXML gives users the ability to Web-service-enable a database, it requires installing an add-on pack and having IIS running on the database server, a requirement that's a problem for many people and is certainly not a recommended configuration for most database servers.

For SQL Server 2005, the SQL Server development team decided that enabling Web services for the database was important enough to warrant native support. While the SQL Server team was working on a solution, the Windows development team was looking at options for a lightweight Web server for Microsoft Windows that users could work with if they didn't need the full features of IIS. The Windows team's solution, a Web server called HTTPSYS, is integrated in Windows Server 2003 and Windows XP Service Pack 2 (SP2). The inclusion of HTTPSYS in Windows let the SQL Server team design a Web-service solution that doesn't need IIS as long as the solution runs on either of the two supported OSs. The earlier SQLXML Web-service implementation still works on Windows 2000, Windows XP SP1, and earlier releases.

The Web-service implementation in SQL Server 2005 is provided through an HTTP endpoint at the server. SQL Server uses endpoints to expose stored procedures or functions as WebMethods through a standard SOAP interface. SQL Server 2005 also automatically generates all the Web Services Description Language (WSDL) required to describe the Web-service interface so that if users call the Web-service URL and use the ?wsdl parameter (e.g., http://MySite/MyFirstWebService?wsdl), they'll get back standard WSDL.

Creating an endpoint in SQL Server 2005 doesn't take much code, as Listing 1 shows. The code at callout A creates the endpoint on the server and assigns details such as the URL and authentication type. The code at callout B describes the WebMethods that SQL Server is exposing and the relationship they have to the database. In Listing 1's code, the Web service exposes a WebMethod called MyFirstWebMethod, which is associated with the stored procedure MyExposedStoredProcedure in the database MyDatabase.

What About Security?

The SQL Server development team has included several features to ensure Web-service security. First, Web-service functionality is off by default. The administrator has to enable support on the server if you want to use Web-service features. Second, SQL Server endpoints don't allow anonymous authentication. Users can access a Web service from SQL Server only if they have security rights to the server and the database containing the endpoint. Also, security checks apply to objects the endpoint is using, so for the endpoint that Listing 1 creates, users must have security rights to the MySite server and the MyDatabase database, and they must also have execution rights for MyExposedStoredProcedure. Finally, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. To allow other users to connect to and execute methods on the endpoint, you use the following T-SQL call:


HTTP endpoints in SQL Server 2005 support four standard authentication mechanisms: Basic, Digest, Integrated (NTLM, Kerberos), and SQL Authentication. When users first attempt to connect to a SQL Server 2005 endpoint, they are authenticated at the HTTP transport level. After the first authentication, the user SID passes to the SQL Server for authentication. Both these steps happen for all authentication options except SQL Authentication credentials, which use WsSecurity Username token headers and are sent as part of the SOAP packet.

Clients on non-Microsoft platforms can connect to a SQL Server endpoint by using either BASIC or SQL Authentication. Using either of these methods requires a secure channel, so users can connect only on ports that also have SSL enabled.

SQL Server 2005 features the ability to create an endpoint that lets users execute ad hoc T-SQL statements. You achieve this functionality by enabling batches on the endpoint. Enabling batches implicitly exposes to users a WebMethod named sqlbatch. This functionality is particularly useful for administrators, who can create a SQL Server endpoint that lets them access, interrogate, and interact with the database even when they don't have direct access to the network it's on.

So, when would you use the Web service functionality in SQL Server 2005? As I mentioned, using Web services lets administrators perform operations against the server, but also opens up new options for application design. The ability to expose Web services directly from the database makes plugging into Service Oriented Architecture (SOA) systems easy. You can use SQL Server Web services as a lightweight data-access protocol for simple internal applications, removing the need for complex data layers. And Web services open up server-connection possibilities for applications running on non-Microsoft platforms. These uses allow greater flexibility in application design and implementation along with greater interaction possibilities in heterogeneous environments.

For more about endpoints and Web services in SQL Server 2005, see the Microsoft article "Overview of Native XML Web Services for Microsoft SQL Server 2005" at

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.