SQL MI Restore with valid SAS key failing with Operating System Error 86
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.
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.
We also used a SQL Agent job to check connectivity was successful using this blog post.
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.
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.
Microsoft Tech Community – Latest Blogs –Read More