azure-api-management.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 REST API published via API Management\r\n",
                "\r\n",
                "Make sure you have create an API Management instance. If you need help, follow the Getting Started guide here: [Create a new Azure API Management service instance by using the Azure portal](https://learn.microsoft.com/en-us/azure/api-management/get-started-create-service-instance)\r\n",
                "\r\n",
                "As an example of an API not residing in Azure that you can call, you may use the [NIST Certified cryptographically secure random number generator](https://csrng.net/) API, that are available here: https://csrng.net/csrng/csrng.php\r\n",
                "\r\n",
                "For example: https://csrng.net/csrng/csrng.php?min=0&max=100\r\n",
                "\r\n",
                "will return the something like:\r\n",
                "\r\n",
                "```\r\n",
                "[{\"status\":\"success\",\"min\":0,\"max\":100,\"random\":99}]\r\n",
                "```\r\n",
                "\r\n",
                "The HTTPS REST API is not in an domain that is allowed for `sp_invoke_external_rest_endpoint` usage. See the allowed list of endpoints here: [Allowed Endpoints](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=azuresqldb-current&tabs=request-headers#allowed-endpoints)\r\n",
                "\r\n",
                "In order to make the NIST endpoint available to be used by `sp_invoke_external_rest_endpoint`, it must published using Azure API Management.\r\n",
                "\r\n",
                "In the next samples is assumed that the API Management instance is deployed at https://external.azure-api.net and that the NIST API has been published at the `/random` path. \r\n",
                "\r\n",
                "Once the API is published, get the Subscription key you can use to call the created API and store it into a Database Scoped Credential\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "2fa150ef-9af9-491d-9c6b-50f49a0b67d9"
            },
            "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://api-mgmt.azure-api.net/random') begin\r\n",
                "    drop database scoped credential [https://api-mgmt.azure-api.net/random];\r\n",
                "end\r\n",
                "create database scoped credential [https://api-mgmt.azure-api.net/random]\r\n",
                "with identity = 'HTTPEndpointHeaders', secret = '{\"Ocp-Apim-Subscription-Key\":\"***\"}';\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "6fd989ea-205e-424d-9c72-980b6cd9fc50",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "And then call random number generator via the API Management endpoint:"
            ],
            "metadata": {
                "azdata_cell_guid": "22ff69f0-86d3-40e1-9689-16e6a43762ee"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @ret int, @response nvarchar(max);\r\n",
                "exec @ret = sp_invoke_external_rest_endpoint \r\n",
                "    @url = N'https://api-mgmt.azure-api.net/random?min=0&max=100',\r\n",
                "    @method = 'GET', \r\n",
                "    @credential = [https://api-mgmt.azure-api.net/random],\r\n",
                "    @response = @response OUTPUT\r\n",
                "select \r\n",
                "    @ret as ReturnCode,     \r\n",
                "    json_query(@response, '$.response') as Response,\r\n",
                "    json_query(@response, '$.result') as Result;\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "e7b5fbba-1938-4a0e-a433-7874b79cf47c",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}