By: Mohammed Moinudheen | Updated: 2019-11-04 | Comments | Related: > 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.
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:
Just click on the option to "Change access level". Change the "Public Access Level" to private (no anonymous access) and click on "OK".
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.
Copy the URL to the clipboard.
On copying the URL and checking it in a browser, you will get this error stating that the resource can’t be found.
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.
Once you click on the "Generate SAS" token option, you will see this screen.
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.
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.
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.
When you scroll a bit further you will see the rest of the screen and the option to "Generate SAS and connection string".
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:
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".
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.
Click on "Next", you will see this screen where you can connect.
Once you click on connect, you will be able to view your blob container as shown.
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.
You will see this message on the activity log of the Azure storage explorer.
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
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-11-04
