azure-event-hubs.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Send an Event to an Azure Event Hub\r\n",
                "\r\n",
                "Make sure to have an Azure Event Hub deployed in Azure to run the following samples. If you need help in creating your first Azure Event Hubs, please take a look here: [Quickstart: Create an event hub using Azure portal](https://learn.microsoft.com/azure/event-hubs/event-hubs-create). \r\n",
                "\r\n",
                "Event Hubs REST endpoint are documented here: [Event Hubs service REST](https://learn.microsoft.com/en-us/rest/api/eventhub/event-hubs-runtime-rest). Specifically in the next samples the [Send Event](https://learn.microsoft.com/en-us/rest/api/eventhub/send-event) API will be used.\r\n",
                "\r\n",
                "In the next samples is assumed that there is an Azure Event Hubs deployed at `https://azure-event-hubs.azurewebsites.net/`, that has a Event Hubs Instance named `myeventhub`. To have the samples working in your environment make sure adjust URL and the Event Hub Instance name so that they will match yours."
            ],
            "metadata": {
                "azdata_cell_guid": "3f79ad9b-7872-479a-9813-26e34e49ba4f"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Send Events using SAS Token\n",
                "\n",
                "Only authenticated requests can send events to Event Hubs. One way to authenticate a request is to provide a Shared Access Signature token: \n",
                "- [Authorizing access to Event Hubs resources using Shared Access Signatures](https://learn.microsoft.com/en-us/azure/event-hubs/authorize-access-shared-access-signature)\n",
                "- [Generate SAS token](https://learn.microsoft.com/en-us/rest/api/eventhub/generate-sas-token). \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:"
            ],
            "metadata": {
                "azdata_cell_guid": "ad7d9205-5d90-47aa-916a-56f9378a9fcc"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- make sure a database master key exists\r\n",
                "if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##') begin\r\n",
                "    create master key encryption by password = 'LONg_Pa$$_w0rd!'\r\n",
                "end\r\n",
                "\r\n",
                "-- create database scoped credential\r\n",
                "create database scoped credential [https://azure-event-hubs.servicebus.windows.net]\r\n",
                "with identity = 'HTTPEndpointHeaders', \r\n",
                "secret = '{\"Authorization\": \"SharedAccessSignature sr=azure-event-hubs.servicebus.windows.net%2fmyeventhub&sig=RVDJM1cSo71j73%2bWR0t7ZCZukIjMEvBn%2bWWqSlqkJeM%3d&se=1697310598&skn=RootManageSharedAccessKey\"}';"
            ],
            "metadata": {
                "azdata_cell_guid": "3fdacfa3-b5ac-4c3a-8cd3-e77f287cd992",
                "language": "sql",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can then send messages to Event Hubs using the \"Send Event\" API, which is available at `https://azure-event-hubs.servicebus.windows.net/myeventhub/messages` :"
            ],
            "metadata": {
                "azdata_cell_guid": "5e6e4469-209d-4946-9546-a6acd793b82a"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @payload nvarchar(max) = '{\"UserId\": \"6C5E29A2-A5E7-449D-BD14-259D61ADC6BE\", \"FirstName\": \"John\", \"LastName\": \"Doe\"}';\r\n",
                "declare @headers nvarchar(4000) = N'{\"BrokerProperties\": \"' + string_escape('{\"PartitionKey\": \"6C5E29A2-A5E7-449D-BD14-259D61ADC6BE\"}', 'json') + '\"}'\r\n",
                "declare @ret int, @response nvarchar(max)\r\n",
                "\r\n",
                "exec @ret = sp_invoke_external_rest_endpoint \r\n",
                "        @url = 'https://azure-event-hubs.servicebus.windows.net/myeventhub/messages',\r\n",
                "        @headers = @headers,\r\n",
                "        @payload = @payload,\r\n",
                "\t\t@credential = [https://azure-event-hubs.servicebus.windows.net],\r\n",
                "        @response = @response output;\r\n",
                "\r\n",
                "select @response;"
            ],
            "metadata": {
                "azdata_cell_guid": "a87411a4-84c5-493d-9a84-dea4eeffa98b",
                "language": "sql",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Send Events using Managed Identities\n",
                "\n",
                "Follow the instructions here: [Enable Managed Identity in Azure SQL](./azure-sql-enable-msi.ipynb) to make sure you have Managed Identity enabled for your Azure SQL database, and then check how to grant to right permission on Event Hubs to the Azure SQL Manage Identity, following the instructions here: [Grant permissions to a managed identity in Azure AD](https://learn.microsoft.com/azure/event-hubs/authenticate-managed-identity?tabs=latest#grant-permissions-to-a-managed-identity-in-azure-ad).\n",
                "\n",
                "Once that is done you just need to create a Database Scoped Credentials with the string `Managed Identity` as identity and `https://eventhubs.azure.net` as the `resourceid`:"
            ],
            "metadata": {
                "azdata_cell_guid": "0afadd91-d62b-4d30-80d2-b7f0c14753ec"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- make sure a database master key exists\r\n",
                "if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##') begin\r\n",
                "    create master key encryption by password = 'LONg_Pa$$_w0rd!'\r\n",
                "end\r\n",
                "\r\n",
                "-- create database scoped credential\r\n",
                "if exists(select * from sys.database_scoped_credentials where [name] = 'https://azure-event-hubs.servicebus.windows.net') begin\r\n",
                "    drop database scoped credential [https://azure-event-hubs.servicebus.windows.net];\r\n",
                "end;\r\n",
                "create database scoped credential [https://azure-event-hubs.servicebus.windows.net]\r\n",
                "with identity = 'Managed Identity', \r\n",
                "secret = '{\"resourceid\": \"https://eventhubs.azure.net\" }';"
            ],
            "metadata": {
                "azdata_cell_guid": "8a8775d3-73cf-4e7b-a0d4-82d707c611f8",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Once this is done you can send the message using the same code as before:"
            ],
            "metadata": {
                "azdata_cell_guid": "59fb3012-317e-4aba-a59e-d4f97efea998"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @payload nvarchar(max) = '{\"UserId\": \"6C5E29A2-A5E7-449D-BD14-259D61ADC6BE\", \"FirstName\": \"John\", \"LastName\": \"Doe\"}';\r\n",
                "declare @headers nvarchar(4000) = N'{\"BrokerProperties\": \"' + string_escape('{\"PartitionKey\": \"6C5E29A2-A5E7-449D-BD14-259D61ADC6BE\"}', 'json') + '\"}'\r\n",
                "declare @ret int, @response nvarchar(max)\r\n",
                "\r\n",
                "exec @ret = sp_invoke_external_rest_endpoint \r\n",
                "        @url = 'https://azure-event-hubs.servicebus.windows.net/myeventhub/messages',\r\n",
                "        @headers = @headers,\r\n",
                "        @payload = @payload,\r\n",
                "\t\t@credential = [https://azure-event-hubs.servicebus.windows.net],\r\n",
                "        @response = @response output;\r\n",
                "\r\n",
                "select @response;"
            ],
            "metadata": {
                "azdata_cell_guid": "823b7c9d-0d13-4e45-914a-7c322db9bafe",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}