Operating system error 50 in Azure SQL Managed Instance

560 views 07:09 0 Comments 8 December 2022

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.

SQL
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)
SQL

First check the current credential by performing a new query

SQL
select * from sys.credentials
SQL

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

SQL
DROP CREDENTIAL [yourcredentialName];
SQL

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

SQL
select * from sys.credentials
SQL

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). 

SQL
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>';
SQL

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.

SQL
USE [master] 
RESTORE DATABASE [SQLTestDB] FROM 
URL = N'https://xxxxx.blob.core.windows.net/yyyyyy/zzzz_db_backup.bak'
SQL

Leave a Reply

Your email address will not be published. Required fields are marked *