[Created - April 16, 2010]
[Updated - October 5, 2010 - Revised text and URIs to refer to ACS production, since ACS Labs is now running ACS v2.]
Looking for a way to expose data in SQL Azure to web clients using simple REST based APIs? If so, life just got easier.
Over the last few weeks, I've been working with Mike Pizzo and David Robinson, building a portal and Silverlight clients for a new OData Service for SQL Azure. Together, the OData Portal and Service let you publish SQL Azure databases as OData services using simple configuration. (For more information about SQL Azure, OData or AppFabric Access Control, please see the glossary at the end of this post.)
Of course, you can still use WCF Data Services (formerly ADO.NET Data Services) to build a custom data service. But, if you just want to expose the data, without any business logic, then the OData Portal and Service provide a no code solution with web friendly security based on AppFabric Access Control (ACS).
Try it out using your web browser at this public data service endpoint.
The OData Service has two access modes: anonymous and authenticated. In both modes, it executes OData queries by impersonating a designated database user. You use the OData Portal to designate database users for both modes.
To use the OData Portal, visit SQL Azure Labs, and navigate to the center tab in the top navigation bar, labeled "OData Service for SQL Azure". If you're not already signed in with a valid Windows Live ID, you'll be taken to a sign in page. When you've signed in, connect to a SQL Azure server by providing the server name (e.g., hqd7p8y6cy.database.windows.net), and the name and password for a login with access to the master catalog, and then clicking Connect.
When a connection is made, OData Service configuration data is displayed for the databases on the selected server. Use the drop down list to switch between the databases on the server.
To enable OData access to the currently selected database, check the box labeled "Enable OData". When OData access is enabled, database user mapping information is displayed at the bottom of the form.
For authenticated access, clients pass SWTs to the OData Service. To get a SWT, just query a Security Token Service (STS) trusted by the OData Service. Currently, the only STS trusted by the OData Service is an ACS based STS located at https://odataacs.accesscontrol.windows.net/WRAPv0.9/. (Note that we are using production ACS, not AppFabric Labs, which is now running ACS v2.)
The query is a token request, as defined by the OAuth WRAP specification: an HTTP POST to the STS endpoint containing a URL encoded form with the following elements:
Here is a typical token request body (note - the real secret key is not shown for security purposes):
If the token request is valid, then the STS will return a SWT in the body of the response as an URL encoded form. Here is a typical SWT for the OData Service (note - this token has expired, and therefore cannot be used):
SWTs issued by the STS for the OData Service expire 3 minutes after they are issued. Your client should therefore either request a new SWT for each query, or reuse SWTs to reduce network usage, checking the expiration timestamp as necessary, and requesting a new SWT whenever the current one expires.
To pass a SWT to the OData Service, place it in either the Authorization or X-Authorization header, encoding it as follows:
Which header should you use? The standard Authorization header is recommended by the OAuth WRAP specification, and is therefore preferred. However, it cannot be set by user code in Silverlight 3 and earlier. Silverlight 4 does let user code set it, but only if the clientaccesspolicy.xml file on the target service contains an allows-from entry with an appropriate http-request-headers attribute. The OData service checks both headers for SWTs. If SWTs are found in both locations, they must match.
For more information about how to request and pass SWTs, see the AppFabric SDK, especially the Programming Guide and Samples. Maciej Skierkowski also has some excellent examples on his blog, including examples for PHP, Java and Python. For more information about identity federation, see my blog post on using ACS with Codename "Dallas", and the FabrikamJets example on Code Gallery.
That's all there is to it. It's easy to get started, so visit the OData Portal, enter your SQL Azure server information, and try it out. If you don't have a SQL Azure server, you can provision one easily on the SQL Azure Portal.
SQL Azure is a highly available, scalable, multi-tenant, cloud-based relational database service hosted in Windows Azure and based on SQL Server. Users can provision SQL Azure virtual servers using the SQL Azure Portal access them over the Internet.
SQL Azure Labs is a site that the SQL Azure team is using to publish incubations and early preview bits for products and enhancements in order to gather feedback from the community. Usage of the services in the Labs environment is not billed. Note, however, that usage of production services accessed through the services in the Labs environment will be billed, if applicable. For example, usage of the OData Service is not billed, but usage of an underlying SQL Azure server accessed through the OData Service will be billed, if applicable. Please note that there is no support or SLA associated with the Labs environment, but in return you can preview the future of SQL Azure while helping to shape it. Though similar to a Community Technology Preview, SQL Azure Labs technologies may occasionally be even farther away from commercial availability.
The Open Data Protocol (OData) is an emerging standard for querying and updating data from a wide variety of sources over the Internet using REST based protocols. It extends several existing standards, including Atom (RFC 4287), an XML format for describing structured data as collections of entries consisting of typed name-value pairs, and AtomPub (RFC 5023), a protocol for getting, adding, updating and removing entries. OData builds on the foundation they provide, adding a familiar entity relationship based data model for describing collections, a query language, formatting facilities, and other features required to enable simple and efficient access to structured data over the Internet.
As Chris Sells points out, OData is analogous to earlier data access standards like JDBC, ODBC and OLEDB. Rather than target a platform, however, it targets Web applications, regardless of implementation technology. OData is supported by many Microsoft products, including Windows Azure Storage, SQL Server 2008 R2, Excel 2010, SharePoint 2010, and WCF Data Services (formerly ADO.NET Data Services), which provides client libraries for .NET, Silverlight, AJAX, PHP and Java. It is also supported by IBM's WebSphere eXtreme Scale REST data service and the Open Government Data Initiative. OData is published under the Open Specification Promise, so that anyone can build servers, clients or tools without royalties or restrictions. For more information see, the OData FAQ on MSDN.
AppFabric Access Control (ACS) is a REST based service that hosts Security Token Services (STSs) provisioned with the Windows Azure AppFabric Portal. ACS based STSs support claims based authentication and authorization using multiple credential types, including SAML and X.509 certificates, and federate with Active Directory Federation Services and other standards based identity providers.