Skip to content

Instantly share code, notes, and snippets.

@rohit-lakhanpal

rohit-lakhanpal/AdventureWorks.md

Created March 17, 2026 08:39
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rohit-lakhanpal/e2a7a9a39f736be7563194332ef773cf to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rohit-lakhanpal/e2a7a9a39f736be7563194332ef773cf to your computer and use it in GitHub Desktop.
Download ZIP
This document walks through the complete, end-to-end process of restoring the AdventureWorks sample database into Azure SQL Managed Instance (SQL MI) using Azure Blob Storage and a system-assigned managed identity. This approach is useful when Shared Key / SAS authentication is disabled on the storage account and you want to use Microsoft Entra …
Raw
AdventureWorks.md

Restoring AdventureWorks to Azure SQL Managed Instance (Using Managed Identity)

This document walks through the complete, end-to-end process of restoring the AdventureWorks sample database into Azure SQL Managed Instance (SQL MI) using Azure Blob Storage and a system-assigned managed identity.

This approach is useful when Shared Key / SAS authentication is disabled on the storage account and you want to use Microsoft Entra ID-based access instead of storage account keys or SAS tokens.

1. Create an Azure SQL Managed Instance

  1. In the Azure portal, create a new Azure SQL Managed Instance:
    • Choose subscription and resource group
    • Select region
    • Choose service tier (General Purpose or Business Critical)
    • Configure networking (a dedicated VNet/subnet is required for MI)
  2. Wait for provisioning to complete. This can take approximately 1-2 hours.

Once deployed, note the following:

  • Managed Instance name
  • Fully qualified domain name (FQDN)

Note: To connect from SQL Server Management Studio (SSMS) later, you also need network connectivity to the managed instance, such as:

  • A public endpoint on the managed instance, or
  • A VM inside the MI virtual network, or
  • A point-to-site or site-to-site VPN connection

2. Enable System-Assigned Managed Identity on SQL MI

Azure SQL Managed Instance supports a system-assigned managed identity that can authenticate directly to Azure services such as Blob Storage.

  1. Go to:
Azure Portal -> SQL Managed Instance -> Identity
  1. Under System assigned, set:
Status = On
  1. Save the configuration.

This automatically creates a managed identity for the SQL MI in Microsoft Entra ID.

3. Create an Azure Storage Account for Backups

  1. Create a Storage Account with settings similar to:
    • Performance: Standard
    • Redundancy: Locally redundant storage (LRS) is sufficient for this scenario
  2. Under security settings:
    • Disable Shared Key access
    • Use Microsoft Entra ID for access instead
  3. Create a Blob container, for example:
adventureworks-backups

4. Download the AdventureWorks Backup

Download the AdventureWorks backup from Microsoft’s official sample repository:

5. Upload the Backup to Azure Blob Storage

Upload the .bak file into the blob container you created.

Example blob URL:

https://YOUR-STORAGE-ACCT.blob.core.windows.net/adventureworks-backups/AdventureWorks2022.bak

You can upload the file using:

  • Azure Portal
  • Azure Storage Explorer
  • Azure CLI with az storage blob upload

Note: Your own user account also needs Storage Blob Data Contributor on the storage account or on the target container in order to upload the backup using Microsoft Entra ID authentication.

Note: If you are using the Azure portal with Microsoft Entra ID to browse and upload blobs, you may also need the Reader Azure RBAC role on the storage account so the portal can navigate the storage resources.

No SAS token or storage account key is required for the upload if you are using Microsoft Entra ID-based access.

6. Grant Blob Access to the SQL MI Managed Identity

The SQL Managed Instance must have data-plane access to the storage account so it can read the backup file.

  1. Go to:
Storage Account -> Access Control (IAM)
  1. Add a role assignment:
    • Role: Storage Blob Data Reader
    • Assignee: SQL Managed Instance (system-assigned identity)
  2. Save the role assignment and allow time for propagation.

You can assign this role at either:

  • The storage account scope, or
  • The specific blob container scope

If you also plan to back up databases from SQL MI to Blob Storage later, use Storage Blob Data Contributor instead of Storage Blob Data Reader.

Role assignments can take several minutes to become effective.

7. Connect to SQL Managed Instance Using SSMS

  1. Install and open the latest version of SQL Server Management Studio (SSMS).
  2. Connect using:
    • Authentication: SQL Authentication or Microsoft Entra authentication, depending on your setup
    • Server name: SQL MI FQDN
  3. Connect to the master database.

8. Create a Credential Using Managed Identity

In SQL Managed Instance, create a SQL credential that maps the Blob container URL to the managed identity.

Important rules:

  • The credential name must exactly match the container URL
  • Do not include a trailing slash
  • No SECRET is used when using managed identity
CREATE CREDENTIAL [https://YOUR-STORAGE-ACCT.blob.core.windows.net/adventureworks-backups]
WITH IDENTITY = 'MANAGED IDENTITY';

This tells SQL MI to authenticate to Blob Storage using its Microsoft Entra managed identity.

9. Validate Access to the Backup File (Optional but Recommended)

Before restoring, validate that SQL MI can access the backup file:

RESTORE FILELISTONLY
FROM URL = 'https://YOUR-STORAGE-ACCT.blob.core.windows.net/adventureworks-backups/AdventureWorks2022.bak';

If this succeeds, the credential and blob access are working correctly.

10. Restore the AdventureWorks Database

Run the restore command:

RESTORE DATABASE AdventureWorks2022
FROM URL = 'https://YOUR-STORAGE-ACCT.blob.core.windows.net/adventureworks-backups/AdventureWorks2022.bak';

Notes:

  • The database must not already exist
  • SQL Managed Instance manages file placement automatically, so no MOVE clause is required
  • Avoid unsupported restore options for this scenario

SQL Managed Instance restore operations are asynchronous. SSMS may time out or disconnect while the restore continues in the background.

11. Validate and Monitor the Restore

Check whether the database is online:

SELECT name, state_desc
FROM sys.databases
WHERE name = 'AdventureWorks2022';

You should eventually see:

state_desc = ONLINE

To monitor restore progress while it is running:

SELECT
    session_id AS SPID,
    command,
    start_time,
    percent_complete,
    DATEADD(second, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests
WHERE command IN ('RESTORE DATABASE');

You can also review restore status in sys.dm_operation_status if needed.

Key Learnings / Gotchas

  • SQL Managed Instance cannot restore a database backup from a newer SQL engine version
  • When Shared Key access is disabled, managed identity and Azure RBAC are required
  • The SQL credential name must exactly match the blob container URL
  • The SQL MI managed identity needs Storage Blob Data Reader at minimum to restore from Blob Storage
  • Your own user account needs Storage Blob Data Contributor to upload the .bak file with Microsoft Entra ID
  • If you upload through the Azure portal, you may also need the Reader role on the storage account
  • Restore on SQL Managed Instance is asynchronous, so a client timeout does not always mean the restore failed

Summary

This process enables a secure restore of a SQL Server backup into Azure SQL Managed Instance using:

  • No SAS tokens
  • No storage account keys
  • Microsoft Entra ID-based authentication
  • Azure RBAC for both upload and restore access

It is a strong pattern for environments with stricter security controls and disabled Shared Key authentication.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment