How to take secure, on-demand backups on SQL Managed Instance

[ZoranRilak]

In this blog post we are going to describe how to configure your Azure environment to take native backups of your Azure SQL Managed Instance to your blob storage in a secure fashion.

If you are interested in other posts on how-to discover different aspects of SQL MI - please visit the http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.

Introduction

Azure SQL Managed Instance is a feature-rich, platform-as-a-service SQL Server. It stands out from its sibling Azure SQL offerings in that it blends near-100% Transact-SQL compatibility with a powerful managed experience, online scaling, intelligence, Azure integration, and strong service level objectives. This approach of traditional-made-modern is what recommends Azure SQL Managed Instance as a prime candidate to consider when you migrate your legacy workloads to the cloud, modernize the way they operate, and aim to reduce your total cost of operation through cloud efficiencies of scale.

Such migration and modernization projects aren’t simple, though. Far from it – us folks who are directly involved in building Azure SQL Managed Instance are acutely aware of the difficulties our customers face when they consider the alluvial strata of on-premises code, connections, and configurations. This is why we constantly focus on aiding such transitions, aiming to make hybrid configurations and migrations of entire systems not just possible, but successful and ultimately enabling.

Thanks to its built-in backups, Azure SQL Managed Instance already covers most business continuity and disaster recovery scenarios. We take secure backups to a remote blob storage that is not accessible to anything or anyone except that Azure SQL Managed Instance, and those backups can only be accessed for the purpose of recovering information by the authorized users. Our backups ensure that customers can recover to any needed point in time with the help of PITR (Point In Time Restore) backups or to a certain snapshot in time for the LTR backups (Long Term Retention). There are still, however, valid and important scenarios in which a user-initiated backup command comes in handy. Such cases include regulatory purposes, dev/test, analytics and a bunch more.

In this article we'll show you how to set up your environment to take secure native SQL backups from Azure SQL Managed Instance to Azure blob storage. We shall be using the current latest & greatest available features of Azure and SQL Managed Instance, namely: managed identities, private link, and service endpoint policies. If you haven’t used them or aren’t sure how they interplay with Azure SQL Managed Instance, here’s an opportunity to figure it out as we go along!

TL;DR

If you only want to get the gist of the article, the procedure we'll perform consists of the following:

1. Use managed identity to authorize your Azure SQL Managed Instance to write to the blob storage.
2. Set up a private endpoint to establish connectivity from Azure SQL Managed Instance to blob storage.
3. Close off public access on the blob storage account.
4. Apply a service endpoint policy on the Azure SQL Managed Instance's subnet to prevent data exfiltration.

 

The Setup

As a quick refresher and to establish how we name things, this is our setup:

Diagram 1. Virtual network with one subnet containing a SQL Managed Instance, and an Azure Storage account with a backup container.

The resources we’ll be working with are:

• One Azure SQL Managed Instance named [mysqsqlmi01].
• One Azure Storage account named [mystorage01].
• One container in mystorage01 named [mybackup01].

Match these to your resources and fire up Azure portal. All set? Let’s go!

1. Authorize your Azure SQL Managed Instance to write to the storage account

Shared access signature (SAS) tokens and why we won’t use them

Traditionally, backups from Azure SQL Managed Instance to Azure Storage are done via Shared Access Signature (SAS) tokens. A SAS token is a string of characters that grants its bearer a scope- and time-limited right to access Azure Storage. SAS tokens are easy to use, but they suffer from several drawbacks. Notably, whoever issued the SAS token has no control over the identity of the bearer. Anyone with a valid SAS can obtain access to the resource. This makes SAS tokens a potential security flaw, for example, if they leak to GitHub in a source or configuration file. Another downside to SAS tokens is that, once issued, they cannot be revoked until they expire. For these reasons SAS tokens are typically issued when needed with a very limited scope and duration; just enough to get the job done.

You’ll find plenty of examples on how to backup from Azure SQL Managed Instance to Azure Storage using SAS tokens; for example, Native database backup in Azure SQL Managed Instance. We won’t go down that road; instead…

 

Managed Identities all the way

Today, Azure SQL Managed Instance supports managed identities. A managed identity is akin to a “club membership”: any number of resources may be configured to identify with a given managed identity, and all resources are free to define the level of access they grant to its bearers.

Managed identities provide a flexible way of assigning and revoking access rights between Azure resources, and importantly, do not suffer from the drawbacks that plague SAS tokens.

There are two types of managed identities, and we can use either:

1. System-assigned managed identity is associated with the resource itself and is unique to it. This identity is created together with Azure SQL Managed Resource (unless configured not to) and is deleted automatically together with its Azure SQL Managed Instance. System-assigned managed identity stands for “this one resource only”.
2. User-assigned managed identities, by contrast, are created by Azure users and associated with resources as needed. One user-assigned managed identity can be associated with multiple resources; and each Azure service that accepts managed identities can be configured as to what access to grant to its bearers.

For the sake of simplicity, we will proceed using our Azure SQL Managed Instance’s system-assigned managed identity. Check that system-assigned managed identity is enabled on your Azure SQL Managed Instance’s Identity blade before we continue.

To assign contributor rights on the backup storage account to your Azure SQL Managed Instance:

1. In Azure Portal, begin typing your storage account’s name and select it.
2. In Access Control (IAM), Select “+ Add” and select “Role assignment”.
3. In the list of roles, select “Storage Blob Data Contributor”. This will suffice.
4. Under “Members”, set up “Assign access to” to “Managed identity”, then select “+ Select members”.
5. Select your subscription. In the “Managed identity” box, under the System-assigned managed identity section, select Azure SQL Managed Instance.
6. Choose your Azure SQL Managed Instance from the list and select Select.
7. Select Review + assign twice.

Now, on the Azure SQL Managed Instance, do the following:

CREATE CREDENTIAL [https://mystorage01.blob.core.windows.net/mybackup01]
    WITH IDENTITY = 'Managed Identity';

And that’s it! For our purposes we’re all set, as far as authorization goes. However, if you are looking for even more flexibility – say, multiple instances to multiple storage accounts – you can easily modify this tutorial to use user-assigned managed identities. You’ll find a wealth of information here:

• User-assigned managed identity in Azure AD for Azure SQL
• Manage user-assigned managed identities

2. Establish secure connectivity to the backup account

We will use private link to create an endpoint in the same virtual network where Azure SQL Managed Instance is hosted. This endpoint will send all traffic directly to the Azure Storage container.

Create a hub subnet to hold the private endpoint

It is a good architectural practice to segregate resources in a network into subnets according to their purpose. In our case, we already have one subnet in the virtual network where Azure SQL Managed Instance is hosted. This subnet is typically named [ManagedInstance]. Azure SQL Managed Instance embraces this philosophy, so it is actually not possible to create a private endpoint in the subnet where one or more Azure SQL Managed Instances reside. Thus, let’s create a new subnet to hold the private endpoint for our storage account:

1. In Azure Portal, begin typing the name of your Azure SQL Managed Instance and select it.
2. Select the link shown under `Virtual network / subnet’.
3. Select Subnets in the sidebar.
4. Select + Subnet and name your subnet, e.g. hub-storage.

 

Create a private endpoint to the backup storage account

In Azure Portal, begin typing “private endpoints” and create a new endpoint with the following:

• Basics: select the resource group and instance details for the new private endpoint. Pick the region in which your Azure SQL Managed Instance resides.
• Resource: point to your backup storage account and select "blob" as the target sub-resource.
• Virtual Network: tell Azure to create this private endpoint in the "hub-storage" subnet we created earlier.
• DNS: this is a useful option that will automatically configure your virtual network to tunnel the traffic through the private endpoint. It does so by making the domain name of the storage account resolve to the private endpoint’s IP address instead of the account’s public endpoint. Other virtual networks as well as general Internet traffic won’t see this, so they’ll still have to go to the storage account’s public endpoint. Select this option and accept the defaults.

Diagram 2. We’ve added a private endpoint between our managed instance and the storage account. Our subnet’s blob traffic to mystorage01.blob.windows.net now traverses the private endpoint.

3. Tighten up the security

Okay, we got our connectivity and authorization in place, what more is there to do? Well, if you really want to tighten the bolts on your security plating (as well you should), then you should consider closing off all access on both the Azure SQL Managed Instance's and storage account's end.

Notice that the non-encrypted backups taken from Azure SQL Managed Instance can be restored on other Azure SQL Managed Instances. This alone is reason enough to prevent unauthorized access to the blob storage containing backups.

Secure your storage account against unauthorized access

Now that we have a public endpoint going, we can turn off the public endpoint on the storage account (storage account > Networking > Firewalls and virtual networks > Public network access: Disabled). All traffic of interest will be arriving through the private endpoint instead.

Secure your Azure SQL Managed Instance against data exfiltration

With our path to the backup location established 1-1 via private link, we should ensure that no other storage account can be targeted by a malicious user or an intruder. This will prevent any data exfiltration from taking place, accidental or otherwise. (It happens!)

1. Deploy a service endpoint policy in the same region where your Azure SQL Managed Instance is.
2. In policy’s Policy definitions, add your backup storage account as the sole resource, and add an alias to /services/Azure/ManagedInstance below.
   • Note that, strictly speaking, you can even use an empty resource group instead of the backup account. This will close off all traffic to Azure Storage except as established via private endpoint(s).
3. Visit your Azure SQL Managed Instance’s subnet blade, enable service endpoints, and attach the service endpoint policy you’ve just created.

Diagram 3. Only the traffic from ManagedInstance successfully reach our storage account (via private endpoint). All other sources of traffic to the storage account will be ignored.

Note that a restrictive service endpoint policy will never prevent Azure SQL Managed Instances from their regular operation. This includes regular backups and the access to database files in the General Purpose service tier. More information on how service endpoint policies apply to Azure SQL Managed Instance can be found at Configure service endpoint policies for Azure SQL Managed Instance.

Set things up on the Azure SQL Managed Instance

Okay, we’re ready to try backing up our database(s):

BACKUP DATABASE [myDatabase]
    TO URL = 'https://mystorage01.blob.core.windows.net/mybackup01/backup.bak'
    WITH COPY_ONLY, COMPRESSION, BLOCKSIZE=65536, MAXTRANSFERSIZE=4194304

Keep in mind that you cannot backup a database encrypted with service-managed Transparent Data Encryption (TDE). If your database is encrypted using customer-managed TDE, also known as Bring-Your-Own-Key (BYOK), then you may want to refer to the following article for more information: Transparent data encryption for SQL Managed Instance.

If you run into other issues or want to learn more about backups, take a look at: SQL Server Backup to URL for Microsoft Azure Blob Storage.

That’s it! We hope you’ve found this tutorial useful and informative. Did it work for you?  Drop us a line below, we read everything  Tell us what you think and how we can make our articles better!