OData with SQL Azure - Enabling OData on an Azure Database

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/19/2011 11:26:16 AM
When you first look at OData and its interoperation with SQL Azure, you may think you're in for a lengthy process and a ton of reading. Wrong. Enabling OData is simple and takes no time at all. This section spends a couple of pages walking you through the process of enabling OData on your SQL Azure database.

1. Getting Started at SQL Azure Labs

Browse to SQL Azure Labs. Then, follow these steps:

  1. On the home page, you see a list at left of things Microsoft is working on. Click the SQL Azure OData Services tab, which first asks you to log in using a Windows Live account.

  2. When you've logged in, you're presented with the window shown in Figure 1: a summary of OData and, more important the first step of configuring your SQL Azure OData Service. The Create a New Server link on the right takes you to the SQL Azure home page where you can sign up for an Azure account, and so on. Because you've already done that, you skip that link. In the Connection Information section, enter your complete server name plus your username and password, and then click Connect.

    Figure 1. Configure OData Service Connection Information section
  3. After your information is validated, a Database Information section appears on the page. Select the database on which you want to enable OData, and select the Enable OData check box. At this point you may think you're done, but not quite. When you click the Enable OData check box, a User Mapping section appears on the page, as shown in Figure 2.

    Figure 2. Configure OData Service Database Information section

As the User Mapping section explains, you can map specific users to the Access Control Service (ACS) keys or allow anonymous access to your SQL Azure database via OData through a single SQL Azure account. Now that we have our data exposed via a REST interface it is through the User Mapping that we control access to our SQL Azure data.

The Anonymous Access User drop-down defaults to No Anonymous Access, but you can also choose to map and connect via dbo, as shown in Figure 3. This article talks about anonymous access shortly. Selecting the dbo option allows you to connect using the database dbo account, basically as administrator. In a moment you learn the correct way to connect to the OData service.

Figure 3. Configure OData Service User Mapping section

Notice also that this section provides a URI link that you can use to browse your SQL Azure data in a web browser.

For the sake of this example, select dbo. You have now OData-enabled your SQL Azure database. Before you proceed, let's spend a few minutes discussing in more detail anonymous access and the ACS and how it applies to SQL Azure.

2. Understanding Anonymous Access

Anonymous access means that authentication isn't needed between the HTTP client and SQL Azure OData Service. Keep in mind, however, that there is no such thing as anonymous access to SQL Azure. If you want to allow anonymous access, you must specify a SQL Azure user that the SQL Azure OData Service can use to access SQL Azure. Figure 4 shows how you do that.

Figure 4. Adding an OData user

The SQL Azure OData Service access has the same restriction as the SQL Azure user. Therefore, if the SQL Azure user being used in SQL Azure OData Service anonymous access has read-only permissions to the SQL Azure database, SQL Azure OData Service can only read the data in the database.

Depending on the requirements of the application, you may consider creating a read-only user for your SQL Azure database. The syntax to do that is as follows:

EXEC sp_addrolemember 'db_datareader', username

Let's talk a moment about ACS and how that applies to SQL Azure.

3. Understanding the Access Control Service

ACS is part of the Windows Azure AppFabric. It's a hosted service that provides federated authentication and rules-driven, claims-based authorization for REST based web services, allowing these web services to rely on ACS for simple username/password scenarios.

In the Community Technology Preview (CTP) of SQL Azure OData Service, it's necessary for you to sign up for the AppFabric and create a service namespace to be used with the SQL Azure OData Service. This allows a single user to access SQL Azure OData Service through the Windows Azure AppFabric Access Control. This user must have the same user id as the database user.

4. Implementing Security Best Practices

Now that you know a little about security regarding SQL Azure OData, you need to be familiar with a few best practices surrounding SQL Azure OData Service:

  • Always create a new SQL Azure user instead of allowing anonymous access to SQL Azure OData Service.

  • Never use your SQL Azure Administrator username to access SQL Azure OData Service.

  • Don't allow the SQL Azure user that is used by SQL Azure OData Service to have write access to SQL Azure OData Service through anonymous access.

The problem you run into by not creating a new user is that you then allow anyone to read from and write to your database. You also have no way to control how much data or what type of data they write.

But with all of that said, because SQL Azure OData is currently in CTP, it's easier to test with anonymous access than with a read-only SQL Azure user. But when out of CTP, you should build your client to use anything other than anonymous access. The browser doesn't support simple web token authentication natively, and this is required for SQL Azure OData Service via ACS. Thus, in production, don't use anonymous access.

Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
Video Sports
programming4us programming4us