azure-functions.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": [
                "# Call an Azure Function from Azure SQL DB\r\n",
                "\r\n",
                "Make sure to have an Azure Function deployed in Azure to run the following samples. If you need help in creating your first Azure Function, please take a look here: [Getting started with Azure Functions](https://learn.microsoft.com/azure/azure-functions/functions-get-started). \r\n",
                "\r\n",
                "**Please note** that the Azure Function must have an [HTTP Trigger](https://learn.microsoft.com/azure/azure-functions/functions-bindings-http-webhook) to be able to be called by Azure SQL DB: [Azure Functions HTTP trigger](https://learn.microsoft.com/azure/azure-functions/functions-bindings-http-webhook-trigger)\r\n",
                "\r\n",
                "In the next samples is assumed that there is an Azure Function with HTTP Trigger support deployed at `https://azure-sql-function.azurewebsites.net/api/sample-function`. To have the samples working in your environment make sure to use the URL of your Azure Function. "
            ],
            "metadata": {
                "azdata_cell_guid": "8411c7b8-5798-44aa-8f07-87c8ecd0496d"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Call a public (or anonymous) Azure Function\r\n",
                "\r\n",
                "If the function doesn't require any authentication, it can be called without any additional requirement"
            ],
            "metadata": {
                "azdata_cell_guid": "5d1731bf-3fbb-4f66-8c36-be5a3e2a71d3"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @url nvarchar(4000) = N'https://azure-sql-function.azurewebsites.net/api/sample-function';\r\n",
                "declare @headers nvarchar(4000) = N'{\"header1\":\"value_a\", \"header2\":\"value2\", \"header1\":\"value_b\"}'\r\n",
                "declare @payload nvarchar(max) = N'{\"some\":{\"data\":\"here\"}}'\r\n",
                "declare @ret int, @response nvarchar(max);\r\n",
                "\r\n",
                "exec @ret = sp_invoke_external_rest_endpoint \r\n",
                "\t@url = @url,\r\n",
                "\t@method = 'GET',\r\n",
                "\t@headers = @headers,\r\n",
                "\t@payload = @payload,\r\n",
                "\t@response = @response output;\r\n",
                "\t\r\n",
                "select @ret as ReturnCode, @response as Response;"
            ],
            "metadata": {
                "azdata_cell_guid": "a07b64e8-b0f4-42fc-9252-8028a6f0036e",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Call an Azure Function protected by a secret key\n",
                "\n",
                "Azure Funtions can be protected via the usage of a \"authorization key\" that must be passed to the function in order to execution to happen: [Authorization level](https://learn.microsoft.com/azure/azure-functions/functions-bindings-http-webhook-trigger?tabs=in-process%2Cfunctionsv2&pivots=programming-language-csharp#http-auth)\n",
                "\n",
                "Since the authorization key is a sensitive value, it is recommended to store its value in a `DATABASE SCOPED CREDENTIAL` (more info here: [Credentials (Database Engine)](https://learn.microsoft.com/sql/relational-databases/security/authentication-access/credentials-database-engine))"
            ],
            "metadata": {
                "azdata_cell_guid": "3d4540a5-eb05-4e38-b8f9-5aea80121945"
            },
            "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-sql-function.azurewebsites.net/api/sample-function]\r\n",
                "with identity = 'HTTPEndpointHeaders', secret = '{\"x-functions-key\":\"<your-function-key-here>\"}';\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "854cb13b-d2bc-4f27-a051-518819f1b1e3",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Once the `DATABASE SCOPED CREDENTIAL` has been defined, it can be used by anyone who has been granted the `REFERENCE` permissions on it (see: [Grant permissions to use credential](https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=azuresqldb-current&tabs=request-headers#grant-permissions-to-use-credential)):"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "1a72e6b1-6a7c-4a48-85b4-ebcc236e8634"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @url nvarchar(4000) = N'https://azure-sql-function.azurewebsites.net/api/sample-function';\r\n",
                "declare @headers nvarchar(4000) = N'{\"header1\":\"value_a\", \"header2\":\"value2\", \"header1\":\"value_b\"}'\r\n",
                "declare @payload nvarchar(max) = N'{\"some\":{\"data\":\"here\"}}'\r\n",
                "declare @ret int, @response nvarchar(max);\r\n",
                "\r\n",
                "exec @ret = sp_invoke_external_rest_endpoint \r\n",
                "\t@url = @url,\r\n",
                "\t@method = 'GET',\r\n",
                "\t@headers = @headers,\r\n",
                "\t@payload = @payload,\r\n",
                "    @credential = [https://azure-sql-function.azurewebsites.net/api/sample-function],\r\n",
                "\t@response = @response output;\r\n",
                "\t\r\n",
                "select @ret as ReturnCode, @response as Response;"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "cf6bc5cd-1a33-446d-9e97-becca68f5065"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Call an Azure Function protected by Azure AD\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 enable Azure AD authentication in your Azure Function as explained here: [Tutorial: Add app authentication to your web app running on Azure App Service](https://learn.microsoft.com/azure/app-service/scenario-secure-app-authentication-app-service).  \n",
                "\n",
                "After the Azure AD principal has been enabled on Azure Function, you'll see that there is a App (client) ID available (for example: `02f5c654-0d70-4074-a82f-40d6a0dce8ff`). The provided App ID must be used to allow Azure SQL Database to correctly make the authenticated call to the Azure Function. Create a `DATABASE SCOPED CREDENTIAL` and specify `Managed Identity` as the `identity` value. The `secret` value must be a flat JSON that contains the APP ID value in the `resourceid` property:"
            ],
            "metadata": {
                "language": "sql",
                "azdata_cell_guid": "9b0da9bc-ce54-482b-8277-fab07e51a0d5"
            },
            "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-sql-function.azurewebsites.net/api/sample-function]\r\n",
                "with identity = 'Managed Identity', secret = '{\"resourceid\":\"02f5c654-0d70-4074-a82f-40d6a0dce8ff\"}';\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "a1337a1c-f307-4501-ad4a-645a83c09f6c",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Once the `DATABASE SCOPED CREDENTIAL` has been defined, it can be used by anyone who has been granted the `REFERENCE` permissions on it (see: [Grant permissions to use credential](https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=azuresqldb-current&tabs=request-headers#grant-permissions-to-use-credential)):"
            ],
            "metadata": {
                "azdata_cell_guid": "d6579560-6d31-4880-9014-09de408af25e"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @url nvarchar(4000) = N'https://azure-sql-function.azurewebsites.net/api/sample-function';\r\n",
                "declare @headers nvarchar(4000) = N'{\"header1\":\"value_a\", \"header2\":\"value2\", \"header1\":\"value_b\"}'\r\n",
                "declare @payload nvarchar(max) = N'{\"some\":{\"data\":\"here\"}}'\r\n",
                "declare @ret int, @response nvarchar(max);\r\n",
                "\r\n",
                "exec @ret = sp_invoke_external_rest_endpoint \r\n",
                "\t@url = @url,\r\n",
                "\t@method = 'GET',\r\n",
                "\t@headers = @headers,\r\n",
                "\t@payload = @payload,\r\n",
                "    @credential = [https://azure-sql-function.azurewebsites.net/api/sample-function],\r\n",
                "\t@response = @response output;\r\n",
                "\t\r\n",
                "select @ret as ReturnCode, @response as Response;"
            ],
            "metadata": {
                "azdata_cell_guid": "bdccbcf9-e54e-4ce6-a0d9-3fac412a1a17",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}