{"id":3441,"date":"2020-06-23T11:10:37","date_gmt":"2020-06-23T10:10:37","guid":{"rendered":"https:\/\/www.lieben.nu\/liebensraum\/?p=3441"},"modified":"2020-06-23T11:10:37","modified_gmt":"2020-06-23T10:10:37","slug":"using-azure-function-msi-to-connect-to-azure-sql-in-python","status":"publish","type":"post","link":"https:\/\/lieben.nu\/liebensraum\/2020\/06\/using-azure-function-msi-to-connect-to-azure-sql-in-python\/","title":{"rendered":"Using Azure Function MSI to connect to Azure SQL in Python"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Since I had to spend a few hours figuring this out, and all examples\/docs are wrong, here&#8217;s an example of how to use Python in an Azure Function to connect to an Azure PaaS database without credentials by utilizing the managed identity of the azure function app.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">__init__.py:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nimport logging\nimport os\nimport pyodbc\nimport requests \nimport struct\nimport sys\nimport azure.functions as func\n\nresource_uri=&quot;https:\/\/database.windows.net\/&quot;\nsql_server=&quot;XXXXXX.database.windows.net&quot;\nsql_database=&quot;primary&quot;\n\ndef get_bearer_token(resource_uri):\n    identity_endpoint = os.environ&#x5B;&quot;IDENTITY_ENDPOINT&quot;]\n    identity_header = os.environ&#x5B;&quot;IDENTITY_HEADER&quot;]\n    logging.info(&#039;identity_endpoint: {}&#039;.format(identity_endpoint))\n    logging.info(&#039;identity_header : {}&#039;.format(identity_header))\n    token_auth_uri = f&quot;{identity_endpoint}?resource={resource_uri}&amp;api-version=2017-09-01&quot;\n    head_msi = {&#039;X-IDENTITY-HEADER&#039;:identity_header}\n    resp = requests.get(token_auth_uri, headers=head_msi)\n    access_token = resp.json()&#x5B;&#039;access_token&#039;]\n    logging.info(&#039;response received from token endpoint: {}&#039;.format(access_token))\n    return access_token  \n\ndef main(req: func.HttpRequest) -&gt; func.HttpResponse:\n    logging.info(&#039;Function Starting&#039;)\n    try:\n        access_token = get_bearer_token(resource_uri)\n        accessToken = bytes(access_token, &#039;utf-8&#039;)\n        exptoken = b&quot;&quot;\n        for i in accessToken:\n                exptoken += bytes({i})\n                exptoken += bytes(1)\n        tokenstruct = struct.pack(&quot;=i&quot;, len(exptoken)) + exptoken  \n        conn = pyodbc.connect(&quot;Driver={ODBC Driver 17 for SQL Server};Server=tcp:{},1433;Database={}&quot;.format(sql_server,sql_database), attrs_before = { 1256:bytearray(tokenstruct) })\n        logging.info(&#039;connected to {} on {}&#039;.format(sql_server,sql_database))\n        cursor = conn.cursor()\n        cursor.execute(&quot;select @@version&quot;)\n        row = cursor.fetchall()\n        logging.info(&#039;sql data: {}&#039;.format(row&#x5B;0])) \n        logging.info(&#039;finished&#039;)              \n    except BaseException as error:\n        logging.info(&#039;An exception occurred: {}&#039;.format(error))   \n    return func.HttpResponse(&quot;done!&quot;)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">requirements.txt:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nastroid==2.4.2\nazure-functions==1.2.1\ncertifi==2020.6.20\nchardet==3.0.4\ncolorama==0.4.3\nidna==2.9\nisort==4.3.21\nlazy-object-proxy==1.4.3\nmccabe==0.6.1\npylint==2.5.3\npyodbc==4.0.30\nrequests==2.24.0\nsix==1.15.0\ntoml==0.10.1\nurllib3==1.25.9\nwrapt==1.12.1\n\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>Since I had to spend a few hours figuring this out, and all examples\/docs are wrong, here&#8217;s an example of how to use Python in an Azure Function to connect to an Azure PaaS database without credentials by utilizing the managed identity of the azure function app. __init__.py: requirements.txt:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[4,5,7,40],"tags":[],"class_list":["post-3441","post","type-post","status-publish","format-standard","hentry","category-automation","category-azure","category-azuread","category-python"],"_links":{"self":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/posts\/3441","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/comments?post=3441"}],"version-history":[{"count":0,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/posts\/3441\/revisions"}],"wp:attachment":[{"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/media?parent=3441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/categories?post=3441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lieben.nu\/liebensraum\/wp-json\/wp\/v2\/tags?post=3441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}