Our example endpoint is now complete. When executed, this statement creates a Web Service named EmployeeWebService that has three Web methods: GetEmployeeManagers, GetManagerEmployees, and SQLBatch.

Security
Now that we've defined our endpoint, we need to discuss security. Endpoints are secured separately from the functions and procedures that they invoke. In effect, this means that a user must have rights to invoke both the endpoint and the function or procedure. For example, in order to successfully execute all of the features of our demonstration Web service, a user would need:

• CONNECT permission on the EmployeeWebService endpoint.
• EXECUTE permission on the uspGetEmployeeManagers stored procedure.
• EXECUTE permission on the uspGetManagerEmployees stored procedure.
• Individual permissions on one or more tables in the AdventureWorks database. (This is required because BATCHES are ENABLED, and ad hoc T-SQL statements may be executed. NOTE: this is not a recommended practice and is presented here for illustration purposes only.)

GRANT CONNECT ON ENDPOINT:: EmployeeWebService to Bob

Testing
In order to test the newly created Web service, we need to create a client program. I chose to use a .NET Windows application for this purpose. (You can't test an SQL Server Web service in Internet Explorer as you can with .NET Web services because there are no HTTP interfaces and no forms provided.) The client program needed an appropriate WSDL document in order to create the Web service proxy class. SQL Server provides this document via a special URL parameter, WSDL, as in HTTP://LOCALHOST:8080/Employee?WSDL. I used the Add Web Reference wizard and pointed the browser to that URL. It generated the proxy class for me, and I ran some tests. You can see my test code in Listing 2, and the results in Figure 1, Figure 2, and Figure 3. The test application had no clue that this Web service was provided by SQL Server and not IIS.

Summary
Now that SQL Server 2005 is here, we have an additional option when it comes to the creation of Web services. We can continue to create them with .NET, or we can create them with SQL Server. If we choose SQL Server then we no longer need IIS, and can instead use the HTTP API that is included in the Windows 2003 Servers and in Windows XP Service Pack 2. This gives us additional flexibility when designing a service-oriented system. It also makes SQL Server a more flexible and valuable tool than its predecessors.