azure-storage.ipynb

{
    "cells": [
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "3f79ad9b-7872-479a-9813-26e34e49ba4f"
            },
            "source": [
                "# Work with Azure Blob Storage\n",
                "\n",
                "To start, ensure that you have a Azure Blob Storage account. Reference the documentation on how to create a storage account if needed. [Create a storage account](https://learn.microsoft.com/azure/storage/common/storage-account-create?tabs=azure-portal)\n",
                "\n",
                "Also, have a container created in the Azure Blob Storage account. You can use this quick-start to help guide you in creating a container. [Quickstart: Upload, download, and list blobs with the Azure portal](https://learn.microsoft.com/azure/storage/blobs/storage-quickstart-blobs-portal)\n",
                "\n",
                "Blob Storage REST endpoint are documented here: [Azure Blob Storage REST API](https://learn.microsoft.com/rest/api/storageservices/blob-service-rest-api). \n",
                "The samples use the [Get Blob](https://learn.microsoft.com/rest/api/storageservices/get-blob?tabs=azure-ad), [Put Blob](https://learn.microsoft.com/en-us/rest/api/storageservices/put-blob?tabs=azure-ad), and the (Create Container)[https://learn.microsoft.com/rest/api/storageservices/create-container?tabs=azure-ad] APIs.\n",
                "\n",
                "The following samples assume that there is an Azure Blob Storage Account deployed at `https://blobby.blob.core.windows.net/` and a container named `myblobs`. To have the samples working in your environment, make sure adjust URL and container name so that they will match your account."
            ]
        },
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "ad7d9205-5d90-47aa-916a-56f9378a9fcc"
            },
            "source": [
                "## Work with files in Azure Blob Storage using a SAS Token\n",
                "\n",
                "Only authenticated requests can send REST requests to Azure Blob Storage. One way to authenticate a request is to provide a Shared Access Signature token: \n",
                "- [Delegate access by using a shared access signature](https://learn.microsoft.com/rest/api/storageservices/delegate-access-with-shared-access-signature)\n",
                "- [Create an account SAS](https://learn.microsoft.com/rest/api/storageservices/create-account-sas). \n",
                "\n",
                "At the moment is not possible to generate a SAS token directly from Azure SQL database, but you can put the code for generating such a token in an Azure Function and call it from Azure SQL database using `sp_invoke_external_rest_point` as well.\n",
                "\n",
                "Once you have the token you can add it into a Database Scoped Credential:"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "azdata_cell_guid": "3fdacfa3-b5ac-4c3a-8cd3-e77f287cd992",
                "language": "sql",
                "tags": []
            },
            "outputs": [],
            "source": [
                "-- make sure a database master key exists\n",
                "if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##') begin\n",
                "    create master key encryption by password = 'LONg_Pa$$_w0rd!'\n",
                "end\n",
                "\n",
                "-- create database scoped credential\n",
                "create database scoped credential [filestore]\n",
                "with identity='SHARED ACCESS SIGNATURE', \n",
                "secret='sv=2022-11-02&ss=bfqt&srt=sco&sp=seespotrun&se=2023-08-03T02:21:25Z&st=2023-08-02T18:21:25Z&spr=https&sig=WWwwWWwwWWYaKCheeseNXCCCCCCDDDDDSSSSSU%3D'\n",
                "go"
            ]
        },
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "5e6e4469-209d-4946-9546-a6acd793b82a"
            },
            "source": [
                "You can now create a file and add content to the file:"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "azdata_cell_guid": "a87411a4-84c5-493d-9a84-dea4eeffa98b",
                "language": "sql",
                "tags": []
            },
            "outputs": [],
            "source": [
                "declare @response nvarchar(max);\n",
                "declare @payload nvarchar(max) = (select * from (values('Hello from Azure SQL!', sysdatetime())) payload([message], [timestamp])for json auto, without_array_wrapper)\n",
                "declare @url nvarchar(max) = 'https://blobby.blob.core.windows.net/myblobs/test-me-from-azure-sql.json'\n",
                "exec sp_invoke_external_rest_endpoint\n",
                "    @url = @url,\n",
                "    @method = 'PUT',\n",
                "    @headers = '{\"x-ms-blob-type\": \"BlockBlob\", \"Content-Type\":\"application/json\", \"Accept\":\"application/xml\"}',\n",
                "    @payload = @payload,\n",
                "    @credential = [filestore],\n",
                "    @response = @response output\n",
                "select cast(@response as xml)\n",
                "go\n",
                "\n",
                "declare @response nvarchar(max);\n",
                "declare @url nvarchar(max) = 'https://blobby.blob.core.windows.net/myblobs/test-me-from-azure-sql.json'\n",
                "exec sp_invoke_external_rest_endpoint\n",
                "    @url = @url,\n",
                "    @headers = '{\"Accept\":\"application/xml\"}',\n",
                "    @credential = [filestore],\n",
                "    @method = 'GET',\n",
                "    @response = @response output\n",
                "select cast(@response as xml)\n",
                "go"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Now, using the Get Blob REST endpoint, you can read the contents of the file:"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "declare @response nvarchar(max);\n",
                "declare @url nvarchar(max) = 'https://blobby.blob.core.windows.net/myblobs/test-me-from-azure-sql.json'\n",
                "exec sp_invoke_external_rest_endpoint\n",
                "    @url = @url,\n",
                "    @headers = '{\"Accept\":\"application/xml\"}',\n",
                "    @credential = [filestore],\n",
                "    @method = 'GET',\n",
                "    @response = @response output\n",
                "select cast(@response as xml)\n",
                "go"
            ]
        },
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "0afadd91-d62b-4d30-80d2-b7f0c14753ec"
            },
            "source": [
                "## Work with files in Azure Blob Storage using Managed Identities\n",
                "\n",
                "### Enabling Managed Identity in Azure SQL\n",
                "\n",
                "First, follow the instructions here: [Enable Managed Identity in Azure SQL](./azure-sql-enable-msi.ipynb) to enable Managed Identity for your Azure SQL database. \n",
                "\n",
                "### Adding the database to a storage role\n",
                "\n",
                "Next, we need to add the Azure SQL Database to the Storage Blob Data Owner role. This is done via Access Control (IAM) in the Azure Portal or via Azure CLI.\n",
                "To start, navigate to the Access Control page in the Azure Storage Account you are using\n",
                "\n",
                "![A picture of navigating to the Access Control page for an Azure Storage Account](./assets/files-add-roll0.png)\n",
                "\n",
                " and click **+ Add**. Then in the dropdown, click **Add role assignment**.\n",
                "\n",
                "![A picture of adding a role assignment on the Access Control page for an Azure Storage Account](./assets/files-add-roll1.png)\n",
                "\n",
                "On the following page, use the search box and enter **blob owner**. The roles list should then show the **Storage Blob Data Owner** role. **Click the role** and then click the **Next** button on the bottom of the page.\n",
                "\n",
                "![A picture of selecting the Storage Blob Data Owner role and then clicking Next](./assets/files-add-roll2.png)\n",
                "\n",
                "On the next page, click the **Managed identity** radio button then click the **+ Select members** link.\n",
                "\n",
                "![A picture of clicking the Managed identity radio button then clicking the + Select members link](./assets/files-add-roll3.png)\n",
                "\n",
                "In the slide out blade on the right, use the **Subscription** drop down to select the subscription where your Azure SQL Database is located. Then use the **Managed identity** dropdown to select **SQL server**. Use the **Select** field to filter the databases by name.\n",
                "\n",
                "![A picture of using the Subscription drop down to select the subscription where your Azure SQL Database is located then using the Managed identity dropdown to select SQL server](./assets/files-add-roll4.png)\n",
                "\n",
                "Once you find the database that is using the REST Endpoint Invocation feature from, select it. It then appears as a selected managed identity. Now, click the **Select** button on the bottom left of the blade.\n",
                "\n",
                "![A picture of clicking the select button on the bottom left of the blade after a database managed identity has been selected](./assets/files-add-roll5.png)\n",
                "\n",
                "Back on the Add role assignments page, you will see the selected database managed identity. Click the blue **Review + assign** button in the lower left of the page.\n",
                "\n",
                "![A picture of viewing the selected database managed identity on the page and then clicking the blue **Review + assign** button in the lower left of the page](./assets/files-add-roll6.png)\n",
                "\n",
                "On the final page, again click the **Review + assign** button in the lower left of the page.\n",
                "\n",
                "![A picture of clicking the **Review + assign** button in the lower left of the page](./assets/files-add-roll7.png)\n",
                "\n",
                "You can verify the role has been assigned on the **Role assignments** tab.\n",
                "\n",
                "![A picture of verifying the role has been assigned on the Role assignments tab](./assets/files-add-roll8.png)\n",
                "\n",
                "### Create the database scoped credentials for managed identity\n",
                "\n",
                "We need to create a new set of database scoped credentials. Use the following code to create a set of credentials for communicating with Azure Blob Storage."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "azdata_cell_guid": "8a8775d3-73cf-4e7b-a0d4-82d707c611f8",
                "language": "sql"
            },
            "outputs": [],
            "source": [
                "-- make sure a database master key exists\n",
                "if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##') begin\n",
                "    create master key encryption by password = 'LONg_Pa$$_w0rd!'\n",
                "end\n",
                "\n",
                "-- create database scoped credential\n",
                "if exists(select * from sys.database_scoped_credentials where [name] = 'blobby.blob.core.windows.net') begin\n",
                "    drop database scoped credential [https://blobby.blob.core.windows.net];\n",
                "end;\n",
                "create database scoped credential [https://blobby.blob.core.windows.net]\n",
                "with identity = 'Managed Identity', \n",
                "secret = '{\"resourceid\": \"https://storage.azure.com\" }';"
            ]
        },
        {
            "attachments": {},
            "cell_type": "markdown",
            "metadata": {
                "azdata_cell_guid": "59fb3012-317e-4aba-a59e-d4f97efea998"
            },
            "source": [
                "Once this is done, you can send a request to Azure Blob Storage with the managed identity credentials. In this example, we will create a new container. Just note, you need to update the date (\"x-ms-date\" : \"Wed, 09 Aug 2023 19:54:40 GMT\") to a recent timestamp otherwise you will get an error on submission of the request."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {
                "azdata_cell_guid": "823b7c9d-0d13-4e45-914a-7c322db9bafe",
                "language": "sql"
            },
            "outputs": [],
            "source": [
                "declare @response nvarchar(max);\n",
                "declare @url nvarchar(max) = 'https://blobby.blob.core.windows.net/mycontainer?restype=container'\n",
                "exec sp_invoke_external_rest_endpoint\n",
                "    @url = @url,\n",
                "    @headers = '{\"Accept\":\"application/xml\",\"x-ms-version\" : \"2023-08-03\",\"x-ms-date\" : \"Wed, 09 Aug 2023 19:54:40 GMT\"}',\n",
                "    @method = 'PUT',\n",
                "    @credential = [https://blobby.blob.core.windows.net],\n",
                "    @response = @response output\n",
                "select cast(@response as xml)\n",
                "go"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "We can also read blob just as before as well. Use the following code to read the json file we created earlier but now using a managed identity. As before, you need to update the date (\"x-ms-date\" : \"Wed, 09 Aug 2023 19:54:40 GMT\") to a recent timestamp otherwise you will get an error on submission of the request."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "declare @response nvarchar(max);\n",
                "declare @url nvarchar(max) = 'https://blobby.blob.core.windows.net/myblobs/test-me-from-azure-sql.json'\n",
                "exec sp_invoke_external_rest_endpoint\n",
                "    @url = @url,\n",
                "    @headers = '{\"Accept\":\"application/xml\",\"x-ms-version\" : \"2023-08-03\",\"x-ms-date\" : \"Wed, 09 Aug 2023 20:04:55 GMT\",\"Content-Type\":\"application/xml\"}',\n",
                "    @method = 'GET',\n",
                "    @credential = [https://blobby.blob.core.windows.net],\n",
                "    @response = @response output\n",
                "select cast(@response as xml)\n",
                "go\n"
            ]
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "SQL",
            "language": "sql",
            "name": "SQL"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat": 4,
    "nbformat_minor": 2
}