Great minds discuss ideas !

DATABASE, MSSQL

Operating system error 50 in Azure SQL Managed Instance

We encountered an error while taking a backup of a database from Azure SQL Managed Instance to Blob. This issue is not specific to Azure Managed Instance (MI), but error indicates a problem with accessing the blob storage. Upon investigation, we determined that the Share Access Token (SAS) token had expired, which was causing the connection to the blob storage container to fail.

We discovered that while attempting to restore the backup using SSMS, the available backup file was not visible. Additionally, while utilizing a T-SQL command to restore the backup, an access error was raised.

Backup failed for Server bla bla bla bla
(Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: Cannot open backup device
.... Operating system error 50 (The request is not support.). (Microsoft.SqlServer.Smo)

First check the current credential by performing a new query

select * from sys.credentials

If any old expired Share Access Token (SAS) is present, it should be drop.

DROP CREDENTIAL [yourcredentialName]; 
GO

Verify that the credential has been drop by performing a new query

select * from sys.credentials

Now need to create a new SAS token with valid permission such as list, read, write permission and also created the new credential to access the blob storage. (first we deleted the old one). 

IF NOT EXISTS 

(SELECT * FROM sys.credentials   

WHERE name = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>') 

CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] 

   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  

   SECRET = '<SAS_TOKEN>';

After creating fresh credential we tried to restore database from blob using T_SQL and it was successfully restored. We validated the data and schema both were present there.

USE [master] 

RESTORE DATABASE [SQLTestDB] FROM 

URL = N'https://xxxxx.blob.core.windows.net/yyyyyy/zzzz_db_backup.bak'

Leave a Reply