Restore dabase at Azure SQL MI from Storage account that have public endpoint disabled

Question

Restore dabase at Azure SQL MI from Storage account that have public endpoint disabled

Girija Mohapatra 0

Hi,

I have one Azure SQL MI & One Storage account. Due to security reasons public endpoint is disabled on both. Please guide how to restore database at Azure SQL MI from Storage account that have public endpoint disabled. I have tried multiple ways but it failed .

Regards

Girija

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2025-05-21T09:40:01.8+00:00
Hi @ Girija Mohapatra

Thank you for your query.

Restoring a database to Azure SQL Managed Instance (MI) from a storage account with public access disabled is certainly possible by using private endpoints and correct network configuration.

Below is a step-by-step guide to help you accomplish this:

Ensure both the Azure SQL Managed Instance and Storage Account are in the same Virtual Network or connected through VNet peering.

The backup file (.bak) must be stored in Azure Blob Storage using Page Blob format.

Step1:

Configure Private Endpoint for the Storage Account:

Create a Private Endpoint for the Blob service of your Storage Account.

Ensure it connects to the same Virtual Network (or a peered one) as the SQL MI.

Confirm the private DNS zone is linked properly for name resolution.

Step 2:

Grant Access:

Assign Managed Identity:

Assign the SQL MI’s managed identity the Storage Blob Data Reader role at the container level.

Step 3:

Update Networking Rules:

In your Storage Account’s Networking settings, ensure access is allowed from the subnet where SQL MI resides.

Step 4:

Restore Using T-SQL:

CREATE CREDENTIAL [https://<storage>.blob.core.windows.net/<container>] WITH IDENTITY = 'Managed Identity'; RESTORE DATABASE [YourDB] FROM URL = 'https://<storage>.blob.core.windows.net/<container>/<backup>.bak' WITH MOVE 'YourDB_Data' TO 'path_on_sqlmi.mdf', MOVE 'YourDB_Log' TO 'path_on_sqlmi.ldf';

Hope this helps. Do let us know if you any further queries.
Girija Mohapatra 0 Reputation points

2025-05-21T12:50:21.55+00:00

Hi Sai Raghunadh M,

Thanks for your response but following 2 suggestions do not work at Azure

i. We can not create Vnet peering here as storage account do not have default Vnet.

ii.When public endpoint is disabled not question of adding the MI Vnet at storage permissions

My Configrations :

(1) My Storage account and Azure SQL MI in same region, also I have created private endpoint at storage account for Azure SQL MI Vnet (different subnet as we can not use same subnet where MI is deployed )

(2)Private endpoint DNS Configuration Image attached

(3)backup file is uploaded as pageblob

(4)Azure SQL MI has read access on storage account >container >file (able to browse at SSMS)

but restored failing with below error:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Cannot open backup device 'https://tnadfstorage2.blob.core.windows.net/backup/KRYSTAL_2425.bak'. Operating system error 5(Access is denied.).

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-3201-database-engine-error

Regards

Girija
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2025-05-21T15:21:16.4066667+00:00
Hi @ Girija Mohapatra

It looks like you've set up the private endpoint correctly and ensured that the backup file is in Page Blob format, but you're still encountering an Access Denied (Error 3201) issue when trying to restore.

Possible Solutions:

Verify Managed Identity Permissions:

Ensure that Azure SQL Managed Instance has the correct RBAC roles assigned:

Storage Blob Data Reader (Read access)

Storage Blob Data Contributor (Read/Write access)

You can check this in Azure Portal > Storage Account > Access Control (IAM).

Check Private Endpoint DNS Resolution:

Since you're using a private endpoint, confirm that the DNS resolution is working correctly.

Run the following command in Azure SQL MI to check connectivity:

SELECT * FROM sys.dm_external_network_connections;

If the private endpoint is misconfigured, you may need to manually link the Private DNS Zone.

Validate Storage Account Firewall Settings:

Even though public access is disabled, ensure that Azure SQL MI's subnet is explicitly allowed in the Storage Account's networking settings.

Go to Storage Account > Networking > Firewalls and Virtual Networks and confirm that the SQL MI subnet is listed.

Use RESTORE HEADERONLY for Debugging:

Run the following command to check if SQL MI can access the backup file:

RESTORE HEADERONLY FROM URL = 'https://tnadfstorage2.blob.core.windows.net/backup/KRYSTAL_2425.bak' WITH CREDENTIAL = [https://tnadfstorage2.blob.core.windows.net/backup/];

If this fails, it indicates an issue with permissions or network access.

Please go through these below links that might help you:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart?view=azuresql

https://techcommunity.microsoft.com/blog/dataarchitectureblog/failed-to-restore-sql-managed-instance-database-backup-from-azure-blob-storage/4273518

Hope this helps. Do let us know if you any further queries.
Girija Mohapatra 0 Reputation points

2025-05-21T15:39:57.19+00:00

No Luck.SELECT * FROM sys.dm_external_network_connections; --Not in Azure SQL Managed Instance (if you find please share the screenshot)

Go to Storage Account > Networking > Firewalls and Virtual Networks and confirm that the SQL MI subnet is listed (Can never be done if we choose 'disabled public endpoint' if you find please share screenshot)

If we are using sas signature no need of giving the credential name in restore command.

1 answer

Your answer

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2025-05-21T09:40:01.8+00:00

Hi @ Girija Mohapatra

Thank you for your query.

Restoring a database to Azure SQL Managed Instance (MI) from a storage account with public access disabled is certainly possible by using private endpoints and correct network configuration.

Below is a step-by-step guide to help you accomplish this:

Ensure both the Azure SQL Managed Instance and Storage Account are in the same Virtual Network or connected through VNet peering.

The backup file (.bak) must be stored in Azure Blob Storage using Page Blob format.

Step1:

Configure Private Endpoint for the Storage Account:

Create a Private Endpoint for the Blob service of your Storage Account.

Ensure it connects to the same Virtual Network (or a peered one) as the SQL MI.

Confirm the private DNS zone is linked properly for name resolution.

Step 2:

Grant Access:

Assign Managed Identity:

Assign the SQL MI’s managed identity the Storage Blob Data Reader role at the container level.

Step 3:

Update Networking Rules:

In your Storage Account’s Networking settings, ensure access is allowed from the subnet where SQL MI resides.

Step 4:

Restore Using T-SQL:

CREATE CREDENTIAL [https://<storage>.blob.core.windows.net/<container>] WITH IDENTITY = 'Managed Identity'; RESTORE DATABASE [YourDB] FROM URL = 'https://<storage>.blob.core.windows.net/<container>/<backup>.bak' WITH MOVE 'YourDB_Data' TO 'path_on_sqlmi.mdf', MOVE 'YourDB_Log' TO 'path_on_sqlmi.ldf';

Hope this helps. Do let us know if you any further queries.
Girija Mohapatra 0 Reputation points

2025-05-21T12:50:21.55+00:00

Hi Sai Raghunadh M,

Thanks for your response but following 2 suggestions do not work at Azure

i. We can not create Vnet peering here as storage account do not have default Vnet.

ii.When public endpoint is disabled not question of adding the MI Vnet at storage permissions

My Configrations :

(1) My Storage account and Azure SQL MI in same region, also I have created private endpoint at storage account for Azure SQL MI Vnet (different subnet as we can not use same subnet where MI is deployed )

(2)Private endpoint DNS Configuration Image attached

(3)backup file is uploaded as pageblob

(4)Azure SQL MI has read access on storage account >container >file (able to browse at SSMS)

but restored failing with below error:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Cannot open backup device 'https://tnadfstorage2.blob.core.windows.net/backup/KRYSTAL_2425.bak'. Operating system error 5(Access is denied.).

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-3201-database-engine-error

Regards

Girija
Girija Mohapatra 0 Reputation points

2025-05-21T15:39:57.19+00:00

No Luck.SELECT * FROM sys.dm_external_network_connections; --Not in Azure SQL Managed Instance (if you find please share the screenshot)

Go to Storage Account > Networking > Firewalls and Virtual Networks and confirm that the SQL MI subnet is listed (Can never be done if we choose 'disabled public endpoint' if you find please share screenshot)

If we are using sas signature no need of giving the credential name in restore command.

Answer 1

PratikLad 1,830 Microsoft External Staff Moderator

Hi Girija Mohapatra

As per your discussion with CSS team the issue is causing because the security configuration where the public endpoint is disabled on both the Azure SQL Managed Instance and the Storage account.

To restore a database to Azure SQL Managed Instance (MI) from a Storage Account with the public endpoint disabled, you must ensure private connectivity between the Managed Instance and the Storage Account. The error you encountered (Operating system error 5 (Access is denied.)) is typically due to network access or identity issues.

You can follow below document to create a VNet link between zone blobstorage and VNET sql mi and a A record.

https://learn.microsoft.com/en-us/azure/dns/private-dns-getstarted-portal#link-the-virtual-network

PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-05-26T12:28:13.8766667+00:00

Hi Girija Mohapatra, We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-05-27T11:01:00.9766667+00:00

Hi Girija Mohapatra, We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.

Share via

Restore dabase at Azure SQL MI from Storage account that have public endpoint disabled

1 answer

Your answer