Accessing OData for SQL Azure with AppFabric Access Control and PHP
If you are having trouble making sense of the title of this post, I don’t blame you. To clear things up, here’s what this post is about (which I couldn’t fit any more concisely into a title): The SQL Azure Labs team has made it possible to consume data in SQL Azure as an OData feed. And, you can set up an OData feed so that only authenticated users (authenticated with the AppFabric access control service, or ACS) can access it. In this post, I’ll show you how to consume these protected feeds using PHP.
I did write a post a few weeks ago that described how to enable anonymous access to SQL Azure OData feeds (Consuming SQL Azure Data with the OData SDK for PHP), but I had a few things to learn about AppFabric access control before I felt comfortable writing about authenticated access to these feeds. I wrote about what I learned in these posts: Understanding Windows Azure AppFabric Access Control via PHP and Access Control with the Azure AppFabric SDK for PHP. If you aren’t familiar with AppFabric access control, I would suggest reading those posts before reading this one (although it’s not 100% necessary to). I would also suggest reading some other posts I've written if you are not familiar with OData: Retrieving Data with the OData SDK for PHP and CRUD Operations with the OData SDK for PHP. Again, not required reading, but they might prove to be helpful background reading if you decide to dig into this post.
Setting up a Protected SQL Azure OData Feed
OK, I just said the posts I listed above were not required reading. That’s not quite true. To set up a SQL Azure server and enable access to data as an OData feed, you’ll need to follow the instructions in the Creating a SQL Azure Server and Creating a SQL Azure OData Service sections of this post: Consuming SQL Azure Data with the OData SDK for PHP. Then we’ll go one step further and restrict anonymous access to the feeds.
After you have enabled a SQL Azure Odata service and while you are in the SQL Azure Labs portal, select (or leave) No Anonymous Access from the Anonymous Access User dropdown:
Now click on +Add User to add a user whose identity will be impersonated when we access feeds after authentication. In the resulting dialog box I’m selecting the “dbo” user (which is there by default), but you can select another user if you have added users for your database. Leave the Issuer Name blank and click Add User:
You should now have a User Name, Secret Key, Issuer name, and OData service endpoint (I’ve blacked out my server ID in the Issuer Name and service endpoint). You will need this information later.
Now we’re ready to use the OData SDK for PHP to generate classes that will make it easy to access feeds from the endpoint above.
Generating Classes with the OData SDK for PHP
To install the OData SDK for PHP (which we will use to generate classes that allow easy access to OData feeds), follow these steps:
1. Download the OData SDK for PHP here: https://odataphp.codeplex.com/.
2. Follow the directions in the Installation and Configuration section of the User_Guide.htm file (in the doc directory of the SDK download).
3. Don’t forget to re-start your Web server after making changes to your php.ini file.
4. (Optional) Add your PHP installation directory to your PATH environment variable. This will allow you to run PHP scripts from any directory.
Important Note: I found that I had to make one change to a file in the SDK in order to proceed. I did not find a flag in the command line utility for generating classes that allowed me to submit claims as part of ACS authentication. The SQL Azure Labs endpoint requires one claim: authorized=true. I hardwired this into the SDK by changing line 59 in the ACSUtil.php file to this: $this->_claims = array("authorized"=>"true");
Now to generate the classes, execute this statement from a command line prompt (without the line breaks):
php PHPDataSvcUtil.php /uri=[OData service endpoint from above.]
/out=[File to which classes will be written.]
/auth=acs
/u=[Issuer Name from above.]
/p=[Secret Key from above.]
/sn=sqlazurelabs
/at=[Odata service endpoint from above.]
So, for example, the statement I’m executing looks something like this:
php PHPDataSvcUtil.php /uri=https://odata.sqlazurelabs.com/OData.svc/v0.1/MySvrId/Northwind
/out=C:\PHPLibraries\odataphp\NorthwindACSProxies.php
/auth=acs
/u=https://odata.sqlazurelabs.com/OData.svc/v0.1/MySvrId/Northwind/dbo
/p=AAAAABBBBBCCCCC111112222233333DDDDDEEEEEFFF=
/sn=sqlazurelabs
/at=https://odata.sqlazurelabs.com/OData.svc/v0.1/MySvrId/Northwind
Note that sqlazurelabs is the AppFabric service namespace used here to issue tokens.
Using the Generated Classes
Now we are ready to use the generated classes. We’ll start off by including the generated classes, defining variables that we’ll use throughout the script, and creating an instance of the class that we’ll use for executing queries. Note that we again use information from above: the $wrap_name variable is set to the Issuer Name, the $wrap_password variable is set to the Secret Key, and the $wrap_scope variable is set to the OData service endpoint.
require_once "NorthwindACSProxies.php";
$service_namespace = "sqlazurelabs";
$wrap_name = "https://odata.sqlazurelabs.com/OData.svc/v0.1/MySvrId/Northwind/dbo";
$wrap_password = "AAAAABBBBBCCCCC111112222233333DDDDDEEEEEFFF=";
$wrap_scope = "https://odata.sqlazurelabs.com/OData.svc/v0.1/MySvrId/Northwind";
$claims=array('authorized'=>'true');$svc = new Northwind();
$svc->Credential = new ACSCredential($service_namespace,
$wrap_name,
$wrap_password,
$wrap_scope,
$claims,
null //proxy
);
Now we can easily get all customers…
$query = $svc->Customers();
$customers = $query->Execute();
foreach($customers->Result as $customer)
echo $customer->CompanyName.": ".$customer->ContactName."</br>";
…or use a filter to get a particular customer…
$query = $svc->Customers()->filter("CustomerID eq 'ALFKI'");
$customers = $query->Execute();
foreach($customers->Result as $customer)
echo $customer->CompanyName.": ".$customer->ContactName."</br>";
…and we can get the orders for a customer…
$query = $svc->Customers()->filter("CustomerID eq 'ALFKI'");
$customers = $query->Execute();
foreach($customers->Result as $customer)
{
echo $customer->CompanyName.": ".$customer->ContactName."</br>";
$orders = $svc->LoadProperty($customer, 'Orders');
foreach($customer->Orders as $order)
echo $order->OrderID . "<br/>";
}
To add a customer, do the following…
try
{
$newCustomer = Customer::CreateCustomer("BRIAN");
$newCustomer->CompanyName = "Microsoft";
$newCustomer->ContactName = "Brian";
$svc->AddToCustomers($newCustomer);
$svc->SaveChanges();
}
catch(ODataServiceException $exception)
{
echo $exception->getError();
}
…to update a customer…
try
{
$query = $svc->Customers()->filter("CustomerID eq 'BRIAN'");
$customer = $query->Execute();
$customer->Result[0]->ContactName = "Brian Swan";
$svc->UpdateObject($customer->Result[0]);
$svc->SaveChanges();
}
catch(ODataServiceException $exception)
{
echo $exception->getError();
}
…and, finally,to delete a customer…
try
{
$query = $svc->Customers()->filter("CustomerID eq 'BRIAN'");
$customer = $query->Execute();
$svc->DeleteObject($customer->Result[0]);
$svc->SaveChanges();
}
catch(ODataServiceException $exception)
{
echo $exception->getError();
}
Accessing OData Feeds without the OData SDK for PHP
While I, personally, like the style of code above (a style similar to .NET code for Microsoft’s Entity Framework, for which I spent quite a bit of time writing documentation), it does seem to obfuscate the simplicity that OData promises. So, if you aren’t familiar with the code style above (or if you simply don’t like it) and you want to access relational data with a URL, you are in luck: you don’t have to use the OData SDK for PHP to access SQL Azure OData feeds. Really, all you have to do is access a URL, like https://odata.sqlazurelabs.com/OData.svc/v0.1/MySvrId/Northwind/Customers to get all customers. The only tricky part is when this feed requires ACS authentication. But solving that problem isn’t hard: once you have requested and received a security token from ACS, you simply need to add it to an Authorization header for a GET request and you’ll get the data you want. In other words, once you have requested and received a token (as shown in this post using cURL), then this code will get all customers:
$token = 'WRAP access_token="'.$token.'"';
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://odata.sqlazurelabs.com/OData.svc/v0.1/MySvrId/Northwind/Customers");
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Authorization: ".urldecode($token)));
curl_setopt($ch, CURLOPT_HEADER, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);$customers = curl_exec($ch);
curl_close($ch);print("<pre>");
print_r($customers);
print("</pre>");
That’s it for today. As usual, I hope this post at least interesting, if not actually useful.
Thanks.
-Brian