none
Read-only access to Azure blob container for SQL DB restores RRS feed

  • Question

  • I am running into an issue when trying to have a non-prod SQL instance (running in an Azure VM) restore a database backup in an Azure storage account blob container created by a prod SQL instance (also running in an Azure VM). I want to prevent any non-prod SQL instances from modifying the backup files, so I created a SAS token with only Read/List permissions and then created the necessary SQL credential. When attempting to restore the DB, I get the error:

    "Cannot open backup device 'https://<storageaccountname>.blob.core.windows.net/<blobcontainer>/<backupfile>.bak'. Operating system error 13(The data is invalid.)."

    Changing the SAS token to include Write permission fixes the issue. Has anyone else seen this behavior? Is there another option to allow read-only access for SQL backup files?

    Tuesday, June 18, 2019 5:44 PM

Answers

  • I just heard back from Azure support. Apparently the reason why SQL Server needs write access to the blob is because by default it acquires a lease on the blob, which is a write operation. For read operations, you can enable SQL trace flag 1820 which does not acquire the lease on the blob, and therefore only grant Read/List permissions to the SAS token.
    • Marked as answer by paulystyle Thursday, June 20, 2019 6:34 PM
    Thursday, June 20, 2019 6:34 PM

All replies

  • Hi Paulystyle,

    Read Access on a blob doesn't necessarily allow for copy. Like in Unix/Linux, a user will need execute permissions and not just read permissions. The basic permissions for Azure Storage accounts does not allow for the ganularity you need. With Azure Active Directory you can use Managed Identities and RBAC controls to provide a more secure and greater level of granularity of permissions within the container.  

    Authorizing access to Azure Storage (link)

    Authorization for the Azure Storage Services (link)

    Authenticate with Azure Active Directory (link)

    Please let us know if you have additional questions.

    Regards,

    Mike

    Wednesday, June 19, 2019 10:09 PM
    Moderator
  • Hi Mike,

    Thanks for the reply. So are you saying that if I enable AAD for Azure Files, I can somehow mount that file path on SQL Server for the service to access, therefore allowing it to read files just as it would via SMB? I'm not sure how the managed identity comes into play. IIRC, that's just giving the computer account object access to Azure. SQL is running as a domain-joined user (service account).

    Thanks,

    Paul

    Wednesday, June 19, 2019 10:56 PM
  • Azure AD supports Azure Storage Blobs and Queues (link)

     
    Thursday, June 20, 2019 5:10 PM
    Moderator
  • I just heard back from Azure support. Apparently the reason why SQL Server needs write access to the blob is because by default it acquires a lease on the blob, which is a write operation. For read operations, you can enable SQL trace flag 1820 which does not acquire the lease on the blob, and therefore only grant Read/List permissions to the SAS token.
    • Marked as answer by paulystyle Thursday, June 20, 2019 6:34 PM
    Thursday, June 20, 2019 6:34 PM