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.
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!
If you only want to get the gist of the article, the procedure we'll perform consists of the following:
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:
Match these to your resources and fire up Azure portal. All set? Let’s go!
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…
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:
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:
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:
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.
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:
In Azure Portal, begin typing “private endpoints” and create a new endpoint with the following:
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.
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.
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.
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!)
Diagram 3. Only traffic from Managed Instance can 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.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.