Securing Azure Storage Account with Shared Access Signature


By:   |   Updated: 2019-11-04   |   Comments   |   Related: More > Azure

Problem

I would like to secure the contents of my Azure storage and limit the accessibility to authorized users based on a certain end date. I would also like to limit the access to the various other services that are available within the Azure storage account. Is there any easy way this can be achieved using Azure?

Solution

This tip assumes that you already have resources in your Azure environment and you are familiar with using the Azure storage account. This tip covers a brief introduction on the Azure storage account. If you are new to Azure, you can subscribe to a trial account here which will give you access to Azure services for an year and a credit of USD 200. In this tip, we will learn about the use of shared access signature (SAS) with few demos.

What is an Azure storage account?

In simple terms, an Azure storage account is used for storing objects. There are different kinds of storage options available within Azure which will meet all of your storage requirements. Refer to this tip, which has information on how you can create storage accounts in Azure.

In this tip, we will use the Azure blob storage option for demo purposes. Blob storage basically refers to unstructured data like images, audio files, video files, etc.

How can we use the Azure storage account in SQL Server?

There are a number of ways by which you can use the Azure storage account in SQL Server. Refer to one of the tips in this link where you can see how on-premises database backups can be performed directly to Azure storage using database maintenance plans. Another use is for the purpose of migration. You can refer to this tip where you can see an example of a migration of a large on-premises database to Azure using the Azure storage container. If you are using standard processes for copying large backup files from on-premises to Azure you may not be able meet the SLAs. But by using Azure storage for this purpose you can save a lot of time on the copy process.

How can we secure the storage account?

You can authorize access to the Azure storage using the access key which gets created when a storage account is created. The access key needs to be secured and not be shared with anyone. Refer to this screenshot from a storage account to view information regarding access keys.

azure storage account access keys

It is not secure to embed these access keys within your application to access the storage account as they may get compromised. In order to overcome these issues, we can use the shared access signatures (SAS). Microsoft recommends that you use Azure Active Directory to authorize to Azure storage as we wouldn’t need to store the access keys within the application code. However, in this demo, we will learn to generate the account SAS and service SAS. Account SAS as the name implies is at the storage account level whereas service SAS is at the individual service level like a blob storage service.

What is a shared access signature (SAS)?

SAS is a URI (Uniform Resource Identifier). Instead of providing your storage access keys to authorized users, you can provide the SAS URIs in order to access the storage resources. As it is not recommended to share the storage access keys, it would be much easier to generate a SAS token and embed within your application for accessing the storage resources. With SAS, you have the ability to set a start time, expiry date, permitted permissions, allowed IP addresses, etc.

What are the real-world use cases you can think of?

Imagine a streaming service like Netflix, and you are looking forward to allow new users a free month of service, you can use a similar concept like SAS for new users. Instead of providing something like storage access keys, appropriate SAS tokens with read permissions could be generated for the application users with an expiry date. This is just one example, but there are several other use case scenarios where this concept is applicable. Even from a SQL Server point of view, we could have the databases backed up to Azure blob storage by creating a credential using the SAS token.

Changing access level and testing access to a resource

In this screenshot, you can view the contents of the storage folder as shown below:

azure storage account access level

Just click on the option to "Change access level". Change the "Public Access Level" to private (no anonymous access) and click on "OK".

azure storage account access level

After changing the access level to "private" and when I click on the blob properties for the "fathersday.jpg" object and copy the URL and test it in a browser.

azure storage account objects

Copy the URL to the clipboard.

azure storage account object properties

On copying the URL and checking it in a browser, you will get this error stating that the resource can’t be found.

azure storage account object access error

Service SAS

In this section, let us generate a SAS for this fathersday.jpg object. Just click on the blob properties like before and select the "Generate SAS" token option instead.

azure storage account generate sas

Once you click on the "Generate SAS" token option, you will see this screen.

azure storage account generate sas

Here, you can select the various options that are available. Under permissions, you could choose any of the following – read, create, write or delete. HTTPS is the recommended protocol for security reasons. You could either choose access key 1 or key 2 and finally select on the option to "Generate blob SAS token and URL". Once you click on the option to generate the SAS token, you will be able to view the SAS token details.

azure storage account generate sas

In this case, in order to test if the SAS URL is working, you can copy the contents of the Blob SAS URL and run it on a browser. In this demo, you will be able to view the image as shown.

azure storage account test sas url

In the first section – "Changing Access Level", we changed the access level to private and tested the URL for the fathersday.JPG object and it returned an error. Now, we can see that the Blob SAS URL is working even though the access level is set to private.

Account SAS

In this section, we will check out the implementation of an account SAS. As the name implies, this is at the storage account level. For this tip, I have created a storage account by the name – "sqltipsdatastore". Once you click on the storage account as shown, you can view the option to generate the shared access signature on the left side.

azure storage account shared access signature

When you scroll a bit further you will see the rest of the screen and the option to "Generate SAS and connection string".

azure storage account shared access signature

Once you click on the option to generate the SAS token and connection string, you will be able to view the details as shown below:

azure storage account sas and connection string

For this SAS token, we had enabled only the read and list permissions. As this is a SAS account, we wouldn’t be able to perform a test similar to how we did for the service SAS using the object-fathersday.JPG. We will perform a test using the storage explorer which was described in a previous tip.

Try account SAS using the Azure Storage Explorer

This tip assumes you are already familiar with the Azure Storage Explorer. In order to connect to Azure storage using the shared access signature, click on the option to "Use a shared access signature (SAS) URI" as shown under the "Add an account" option and click on "Next".

azure storage account testing

Once you click on "Next", you will get this screen to provide the details. You can provide a suitable "Display name" and for "URI", just copy the contents of "Blob service SAS URL" that was generated.

azure storage account testing

Click on "Next", you will see this screen where you can connect.

azure storage account testing

Once you click on connect, you will be able to view your blob container as shown.

azure storage account testing

Here, you can try to perform a delete operation on any of the objects. You had only assigned the permission to read and list when you generated the account SAS. Once you click on delete, you will notice that the delete operation is not authorized.

azure storage account testing

You will see this message on the activity log of the Azure storage explorer.

azure storage account testing

In this section, you saw how you can test the account SAS using the Azure storage explorer.

Next Steps
  • In this tip you learned about the use of Azure storage
  • In this tip you learned about the access keys used in the storage account
  • You learned about the use of shared access signatures (SAS) with examples
  • You learned about the different kinds of SAS tokens
  • You learned how SAS URIs can be used to connect to Azure storage explorer
  • Try out this demo on your own Azure environment
  • Refer to these other articles available on related to Azure


Last Updated: 2019-11-04


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools