Share via

How to Use OData for SQL Azure with AppFabric Access Control

  • Article

[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.

Access Modes

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.

 

  • Anonymous mode requires no credentials, as the name implies.
  • Authenticated mode, by contrast, requires clients to pass credentials with their queries. The OData Service uses Simple Web Token (SWT), a lightweight, web friendly security token format. You can get a SWT to pass to it by querying an ACS based Security Token Service (STS) using OAuth WRAP, a lightweight, web friendly authorization protocol. Before going into more detail about how to get a SWT and pass it to the OData service, let's look at the OData Portal.

 

Using the Portal

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.

 

  • Use the drop down list labeled "Anonymous Access User" to select an anonymous access user. If an anonymous access user is selected, then all queries against the database presented without credentials will execute by impersonating that user. You can access the database as the anonymous user by clicking on the link provided at the bottom of the page. If no anonymous access user is selected, then the OData Service will not allow anonymous access to the database.
  • Click the link labeled "Add User" to add a user for authenticated access. In the pop up panel, select the user from the drop down list. Leave the issuer name empty for simple authentication, or provide the name of a trusted Security Token Service (STS) for federated authentication. For example, to federate with another ACS based STS, provide the base URI for the STS endpoint displayed by the Windows Azure AppFabric Portal for the STS, as shown in the first line below.

 

Issuer name for ACS based STS

 

  • Click the "OK" button to complete the configuration process and dismiss the pop up panel. When one or more authenticated access users are added, the OData Service will impersonate them when appropriate credentials are presented. You can designate as many authenticated access users as you like. The OData Service will decide which one to impersonate for each query by inspecting the credentials presented with the query.

 

Building Clients

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:

 

  • The name of the protected resource to be accessed (the wrap_scope parameter). In the case of the OData Service, this is the base URI for the data source to be queried.
  • The name of the issuer to be used for authentication (the wrap_name parameter). In the case of the OData Service, this is the issuer name displayed on the portal. The default issuer name is the base URI for the data source to be queried with the database user name appended as a path segment (e.g., https://odata.sqlazurelabs.com/OData.svc/v0.1/tatbuixa8p/Pubs/pubsuser). To enable federation, the name of an STS can be provided, instead of the default issuer name, as described above. Note, however that if you plan to use federation, the alternate issuer name must be specified on the OData Portal before you can use it to request a SWT.
  • The secret key for the specified issuer name (the wrap_password parameter). In the case of the OData Service, this is the secret key for the database user displayed on the OData Portal.
  • One or more claims, expressed as form parameters. The OData Service requires a single claim (authorized=true).

 

Here is a typical token request body (note - the real secret key is not shown for security purposes):

 

wrap_scope=https%3a%2f%2fodata.sqlazurelabs.com%2fOData.svc%2fv0.1%2fhqd7p8y6cy%2fAdventureWorks&wrap_name=https%3a%2f%2fodata.sqlazurelabs.com%2fOData.svc%2fv0.1%2fhqd7p8y6cy%2fAdventureWorks%2fodata_guest&wrap_password=9vR4uV7GvBXUcYhcBtbuPWJDwUPKu0UCd%2b%2bRJRC7Egw%3d&authorized=true

 

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):

 

user=odata_guest&Issuer=https%3a%2f%2fodataacs.accesscontrol.windows.net%2f&Audience=https%3a%2f%2fodata.sqlazurelabs.com%2fOData.svc%2fv0.1%2fhqd7p8y6cy%2fAdventureWorks&ExpiresOn=1271458956&HMACSHA256=St1j434tXjyXbSJMPoAY6gsFMTzc3xsLum1UgvXokpw%3d

 

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:

 

WRAP access_token="user=odata_guest&Issuer=https%3a%2f%2fodataacs.accesscontrol.windows.net%2f&Audience=https%3a%2f%2fodata.sqlazurelabs.com%2fOData.svc%2fv0.1%2fhqd7p8y6cy%2fAdventureWorks&ExpiresOn=1271458956&HMACSHA256=St1j434tXjyXbSJMPoAY6gsFMTzc3xsLum1UgvXokpw%3d"

 

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.

 

What Now?

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.

 

Glossary

SQL Azure

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

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.

OData

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

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.

ACSBasedSTSIssuerName.PNG

Comments