November 27, 2014
Hot Topics:

New HTTP Endpoints Create SQL Server 2005 Web Services

  • August 5, 2004
  • By Peter DeBetta
  • Send Email »
  • More Articles »
by Peter DeBetta of Wintellect

SQL Server 2000 offers some capabilities for returning XML output via HTTP using SQLXML—and, of course, SQLXML supports Web services creation. Although not rocket science, setting up, configuring, and using Web services in SQL Server 2000 does require a little effort (see the SQLXML documentation about Web services in SQL Server 2000).

When .NET came about, people began writing Web services in C# or VB.NET. They simply connected into SQL Server (or any data store, for that matter) to retrieve the data. .NET made creating Web services as simple as 1-2-3:

  1. Create some stored procedures.
  2. Write some WebMethods to return the data from said stored procedures.
  3. Presto! You have a Web service.

But now, the next generation of Web services is hitting the streets. SQL Server 2005 supports native HTTP SOAP via a feature known as HTTP Endpoints. For those who don't know, Simple Object Access Protocol (affectionately known as SOAP) is a lightweight messaging protocol that Webopedia defines as follows:

"Short for Simple Object Access Protocol, a lightweight XML-based messaging protocol used to encode the information in Web service request and response messages before sending them over a network. SOAP messages are independent of any operating system or protocol and may be transported using a variety of Internet protocols, including SMTP, MIME, and HTTP."

This new HTTP Endpoints feature in SQL Server 2005 is the subject of this article.

HTTP Endpoints

So what is an HTTP Endpoint? You may have heard that it is SQL Server 2005's means for creating Web services, but it actually is much more. An HTTP Endpoint also is a means of creating interfaces via HTTP or TCP for SOAP, T?SQL, Service Broker, and even database mirroring. Although these other functions are very intriguing, this discussion concerns only the ability to create Web services in SQL Server—Web services that can return rowset data, scalar values, messages, and even errors, all of which are serialized into XML automatically. And, an HTTP Endpoint does all of this without requiring you to install IIS (it uses the Windows 2003 kernel module http.sys).

Because I like to learn from examples, I am going to teach by example. I demonstrate how to use HTTP Endpoints by walking through an example from start to finish. Before starting, here are a few notes to consider:

  • This demonstration uses the new demo database called AdventureWorks.
  • Native HTTP SOAP in SQL Server 2005 is not supported on Windows XP. If you want to try the examples, you need Windows Server 2003.
  • SQL Server 2005 Express Edition (the new MSDE) does not support HTTP Endpoints, so be sure to install the Developer Edition.
  • I do not explain how to use SQL Server Management Studio to execute SQL scripts. I trust you know how to do this.

Security

Because HTTP Endpoints are a server-level feature, security of endpoints is also on the server level. Of course, the serveradmin system role can create, alter, and drop endpoints from the SQL Server instance, but how do you allow developers to manage these endpoints without giving them the excessive permissions of this system role?

The answer lies in SQL Server 2005's new ability to assign server-level permissions to logins, as shown here:

GRANT ALTER ANY HTTP ENDPOINT TO peter

With this command, you can allow the peter login to manage HTTP Endpoints without giving other unnecessary permissions. The following is a list of permissions that you can modify via GRANT, DENY, or REVOKE:

  • {GRANT|DENY|REVOKE} ALTER ANY ENDPOINT TO Login
    Controls the ability to alter any HTTP Endpoint; also allows permission to transfer ownership and connect to any endpoint

  • {GRANT|DENY|REVOKE} ALTER ON ENDPOINT:: EndPointName TO Login
    Controls whether a login can alter a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint

  • {GRANT|DENY|REVOKE} CONTROL ON ENDPOINT:: EndPointName TO Login
    Controls whether a login can alter or drop a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint

  • {GRANT|DENY|REVOKE} CONNECT ON ENDPOINT:: EndPointName TO Login
    Controls whether or not a login can connect to (execute requests against) an HTTP Endpoint

  • {GRANT|DENY|REVOKE} TAKE OWNERSHIP ON ENDPOINT:: EndPointName
    TO Login
    
    Controls whether a login can take ownership of the HTTP Endpoint

  • {GRANT|DENY|REVOKE} VIEW DEFINITION ON ENDPOINT:: EndPointName
    TO Login
    
    Controls the ability for a login to see the metadata (definition) of the HTTP Endpoint

Consider one last example. If I want to deny the peter login the ability to use an HTTP Endpoint named SQLEP_Financial, I would execute the following statement:

DENY CONNECT ON ENDPOINT::SQLEP_Financial TO peter




Page 1 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel