About Container Shared Access Signature for SQL Server XI

  • Article

Hi all,

Ever since the MSDN post about SQL Server XI, available from SQL 2014 (see  http://blogs.msdn.com/b/igorpag/archive/2013/10/23/create-a-sql-server-2014-database-directly-on-azure-blob-storage-with-sqlxi.aspx) there is a lot of buzz about Azure Storage in the SQL Server community. One recurring question is: What is the Shared Access Signature (SAS) and why SQL Server XI needs it?

First some background: in the Azure storage platform, each blob is contained in a, er, container. Each container, in turn, is related to a specific Storage Account:

 

So, in order to access a blob you need to specify:

  • The account.
  • The container.
  • The blob name itself.

So, for example, suppose you have a storage account called frcogno. In that account you have created a blob called database.mdf and placed it into the container mindflavor.

 

In the Azure world, this is a REST URI:

 https://frcogno.blob.core.windows.net/mindflavor/database.mdf

 Of course, unless you’ve specified the container or the blob to be publicly accessible, you cannot access it directly. The Get Blob operation (http://msdn.microsoft.com/en-us/library/windowsazure/dd179440.aspx) requires you to specify the Authorization header. Without it any access will fail. But what is required to specify the authorization header? Simply put you need the Azure Account shared key. You can find them in the portal:

 

As you can see the access keys are linked to your Azure Storage account:

Our picture now shows that in order to access https://frcogno.blob.core.windows.net/mindflavor/database.mdf we need the Shared Access Keys of the account frcogno. But what if we have two containers?

As you can see, knowing the Shared Access Keys of frcogno would allow you to access both containers. Aside from that, each REST API call must be independently authorized. Since the authorization involves a SHA256 algorithm it will be hard on your CPU (suppose 50K IOPS: it means up to 50K SHA256/sec!).  

In order to avoid the continuous authorization we should create a reusable signed request. In the Azure platform it’s done by using a “self-signed” URI (or, more correctly, Shared Access Signature URI).  That is, an URI that contains the authorization header once and for all.

Referring here we find exactly how to generate that URI: http://msdn.microsoft.com/en-us/library/windowsazure/dn140255.aspx; don’t worry, though, you can use the easier T-SQL method (just install the SQL Server to Windows Azure helper library available here: https://sqlservertoazure.codeplex.com/).

Using the library we can call the GenerateDirectBlobSharedAccessSignatureURI function to do the job for us:

 SELECT [Azure].GenerateDirectBlobSharedAccessSignatureURI(
    'https://frcogno.blob.core.windows.net/mindflavor/database.mdf',
  'cQhAGiYCTw09dg+i4YkciTiG2g0g5Sn5AMvKD2bnG9csjxuLOf8fNq7dKoY4I48LEFLTUGDZXxNxBz0oxGSUNQ==',
   'rwd',
    'b',
  '2010-01-01', '2015-12-31');

The parameters are:

  1. The URI you want to sign. In our case is 'https://frcogno.blob.core.windows.net/mindflavor/database.mdf'.
  2. The Storage account shared Key.
  3. The permission to grant the SAS URI (in this case we give it Read, Write, Delete. Please refer to http://msdn.microsoft.com/en-us/library/windowsazure/dn140255.aspx for the list of permissions available).
  4. The type of resource. In this case we use b as Blob. If we were to grant a container it would have been c.
  5. The validity interval of the SAS URI. In this case we specify from '2010-01-01' to '2015-12-31'.

The result would be a rather complex URI that will allow you to access the resource without knowing the Azure account shared key.

 

 Notice that in this case we are creating a SAS URI for the single blob. We could have done it at container level. In that case it would be possible to access all the blobs in that container. For example this SAS URI would allow access to any blob in the mindflavor container:

 

Now all you have to do is to create the credential in SQL Server:

 CREATE CREDENTIAL [https://frcogno.blob.core.windows.net/mindflavor]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
SECRET='sv=2012-02-12&st=2009-12-31T23:00:00Z&se=2015-12-30T23:00:00Z&sr=c&sp=rwdl&sig=OR4xtXnPXe%2BYHpZ9J5EiZ0Fm9R664wc7%2BK0RVELffIQ%3D';

And now SQL Server can manage the blobs in the mindflavor container. And, more importantly, not in any other container of the same Azure Storage Account.

There are a couple of things to note:

  • The SAS URI has a validity time frame. Outside that timespan the SAS URI would expire.
  • Knowing the SAS URI will allow uncontrolled access to the container. Make sure to not share it.

The second point leads to another thought: what if the SAS URI is stolen? Is there a way to revoke the permissions?

In our case we’ll have to change the Azure Account Shared key (the one we used to generate the SAS URI). That will, of course, invalidate every single SAS URI generated with it. This is far from ideal so the Azure platform has another solution for you.

You can generate a access policy of a resource and then grant access to that policy. If the SAS URI is stolen all you have to do is to revoke that single policy.

But that will be shown in another post since it requires you to play with the container ACLs. More on that later.

 

Happy Coding,

Francesco Cogno