SQL MI Restore with valid SAS key failing with Operating System Error 86
Published Apr 22 2024 01:39 AM 1,294 Views
Microsoft

On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.

SamGarth_0-1713440021047.png

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

 

Additional information:
Cannot open backup device
'https://storage.blob.core.windows.net/container/dbbackup_2024_03_21_121901.bak'
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

 

To ensure that the SQL Managed Instance could successfully connect to the storage account, we checked that it was not behind a firewall in Azure.

SamGarth_1-1713440055648.png

We also used a SQL Agent job to check connectivity was successful using this blog post.

SamGarth_2-1713440074980.png

After confirming connectivity was successful, the final check was to test the SAS token.

To do this, we used the SSMS restore GUI to connect to the storage account using the same token, which was successful and allowed us to browse and select a backup file.

SamGarth_3-1713440102084.png

Despite everything looking well, the restore was still failing. 

In the end, we dropped and recreated the credential, which solved the issue.

 

 

DROP CREDENTIAL [https://storageaccount.blob.core.windows.net/container]
GO

CREATE CREDENTIAL [https://storageaccount.blob.core.windows.net/container]
WITH IDENTITY = N'SHARED ACCESS SIGNATURE',
SECRET = N'SAS_TOKEN'
GO

 

 

Disclaimer
Please note that products and options presented in this article are subject to change. This article reflects the behaviour from April 2024.

 

I hope this article was helpful for you, please feel free to share your feedback in the comments section. 

Co-Authors
Version history
Last update:
‎Apr 22 2024 01:39 AM
Updated by: