共用方式為

Introduction to Open Data Protocol (OData) and SQL Azure

  • 發行項

[This article was contributed by the SQL Azure team.]

image The Open Data Protocol (OData) is an emerging standard for querying and updating data over the Web. OData is a REST-based protocol whose core focus is to maximize the interoperability between data services and clients that wish to access that data. It is being used to expose data from a variety of sources, from relational databases like SQL Azure and file systems to content management systems and traditional websites. In addition, clients across many platforms, ranging from ASP.NET, PHP, and Java websites to Microsoft Excel, PowerPivot, and applications on mobile devices, are finding it easy to access those vast data stores through OData as well.

The SQL Azure OData Service incubation (currently in SQL Azure Labs) provides an OData interface to SQL Azure databases that is hosted by Microsoft. Currently SQL Azure OData Service is in incubation and is subject to change. We need your feedback on whether to release this feature. You can provide feedback by emailing SqlAzureLabs@microsoft.com or voting for it at www.mygreatsqlazureidea.com. Another way to think about this is that SQL Azure OData Service provides a REST interface to your SQL Azure data.

The main protocol to call SQL Azure is Tabular Data Stream (TDS), the same protocol used by SQL Server. While SQL Server Management Studio, ADO.NET and .NET Framework Data Provider for SqlServer use TDS the total count of clients that communicate via TDS is not as large as those that speak HTTP. SQL Azure OData Service provides a second protocol for accessing your SQL Azure data, HTTP and REST in the form of the OData standard. This allows other clients that participate in OData standard to gain access to your SQL Azure data. The hope is that because OData is published with an Open Specification Promise there will be an abundance of clients, and server implementations using OData. You can think of ADO.Net providing a rich experience over your data and OData providing a reach experience.

SQL Azure OData Service Security

The first thing that jumps to mind when you consider having a REST interface to your SQL Azure data is how do you control access? The SQL Azure OData Service implementation allows you to map both specific users to Access Control Service (ACS) or to allow anonymous access through a single SQL Azure user.

Anonymous Access

Anonymous access means that authentication is not needed between the HTTP client and SQL Azure OData Service. However, there is no such thing as anonymous access to SQL Azure, so when you tell the SQL Azure OData Service that you allow anonymous access you must specify a SQL Azure user that SQL Azure OData Service can use to access SQL Azure. The SQL Azure OData Service access has the same restriction as the SQL Azure user. So if the SQL Azure user being used in SQL Azure OData Service anonymous access has read-only permissions to the SQL Azure database then SQL Azure OData Service can only read the data in the database. Likewise if that SQL Azure user can’t access certain tables, then SQL Azure OData Service via the anonymous user can’t access these tables.

If you are interested in learning more about creating users on SQL Azure, please see this blog post which shows how to create a read-only user for your database.

Access Control Service

The Windows Azure AppFabric Access Control (ACS) service is a hosted service that provides federated authentication and rules-driven, claims-based authorization for REST Web services. REST Web services can rely on ACS for simple username/password scenarios, in addition to enterprise integration scenarios that use Active Directory Federation Services (ADFS) v2.

In order to use this type of authentication with OData you need to sign up for AppFabric here, and create a service namespace that use with SQL Azure OData Service. In the CTP of SQL Azure OData Service, this allows a single user, which has the same user id as the database user to access SQL Azure OData Service via Windows Azure AppFabric Access Control, using the secret key issued by the SQL Azure Labs portal. It doesn’t currently allow you to integrate Active Directory Federation Services (ADFS) integration, nor map multiple users to SQL Azure permissions.

Security Best Practices

Here are a few best practices around using SQL Azure OData Service:

  • You should not allow anonymous access to SQL Azure OData Service using your SQL Azure administrator user name. This allows anyone to read and write from your database. You should always create a new SQL Azure user, please see this blog post.
  • You should not allow the SQL Azure user used by SQL Azure OData Service to have write access SQL Azure OData Service via anonymous access, because there is no way to control how much or what type of data they will write.
  • Because the browser will not support Simple Web token authentication natively, which is required for SQL Azure OData Service using Windows Azure AppFabric Access Control, you will need to build your own client to do anything but anonymous access. For more information see this blog post. That said, it is easiest while OData is under CTP to just use anonymous access with a read-only SQL Azure user.

Summary

I wanted to cover the basic of OData and a lay of the land around security. This information will surely change as OData matures and migrates from SQL Azure Labs to a production release. Do you have questions, concerns, comments? Post them below and we will try to address them.