Category Archives: python

Using Azure Function MSI to connect to Azure SQL in Python

Since I had to spend a few hours figuring this out, and all examples/docs are wrong, here’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:

import logging
import os
import pyodbc
import requests 
import struct
import sys
import azure.functions as func

resource_uri="https://database.windows.net/"
sql_server="XXXXXX.database.windows.net"
sql_database="primary"

def get_bearer_token(resource_uri):
    identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
    identity_header = os.environ["IDENTITY_HEADER"]
    logging.info('identity_endpoint: {}'.format(identity_endpoint))
    logging.info('identity_header : {}'.format(identity_header))
    token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&api-version=2017-09-01"
    head_msi = {'X-IDENTITY-HEADER':identity_header}
    resp = requests.get(token_auth_uri, headers=head_msi)
    access_token = resp.json()['access_token']
    logging.info('response received from token endpoint: {}'.format(access_token))
    return access_token  

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Function Starting')
    try:
        access_token = get_bearer_token(resource_uri)
        accessToken = bytes(access_token, 'utf-8')
        exptoken = b""
        for i in accessToken:
                exptoken += bytes({i})
                exptoken += bytes(1)
        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken  
        conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=tcp:{},1433;Database={}".format(sql_server,sql_database), attrs_before = { 1256:bytearray(tokenstruct) })
        logging.info('connected to {} on {}'.format(sql_server,sql_database))
        cursor = conn.cursor()
        cursor.execute("select @@version")
        row = cursor.fetchall()
        logging.info('sql data: {}'.format(row[0])) 
        logging.info('finished')              
    except BaseException as error:
        logging.info('An exception occurred: {}'.format(error))   
    return func.HttpResponse("done!")

requirements.txt:

astroid==2.4.2
azure-functions==1.2.1
certifi==2020.6.20
chardet==3.0.4
colorama==0.4.3
idna==2.9
isort==4.3.21
lazy-object-proxy==1.4.3
mccabe==0.6.1
pylint==2.5.3
pyodbc==4.0.30
requests==2.24.0
six==1.15.0
toml==0.10.1
urllib3==1.25.9
wrapt==1.12.1

Uploading a file to onedrive for business with Python

For a Raspberry Pi project that’ll take a number of pictures of my house for an as of yet unknown period of time I’m sharing my very first Python script with you.

All it has to do is upload all files from a given folder to a given Onedrive for Business path, as obviously the Pi can’t store much data on its tiny SD card. You’ll need to register an azure ad app and give it the appropriate permissions. You’ll have to consent to the application once (url format = https://login.microsoftonline.com/common/adminconsent?client_id={client-id}).

Then the schedule below Python script on your Pi, it will retrieve an Azure token without the need for external libraries, parse the directory and upload everything in it to the given onedrive for business URL, simple as that! It can also be used for Sharepoint or Teams by adjusting the path.

import requests 
import json
directory = r"c:\temp\uploads"
data = {'grant_type':"client_credentials", 
        'resource':"https://graph.microsoft.com", 
        'client_id':'XXXXX', 
        'client_secret':'XXXXX'} 
URL = "https://login.windows.net/YOURTENANTDOMAINNAME/oauth2/token?api-version=1.0"
r = requests.post(url = URL, data = data) 
j = json.loads(r.text)
TOKEN = j["access_token"]
URL = "https://graph.microsoft.com/v1.0/users/YOURONEDRIVEUSERNAME/drive/root:/fotos/HouseHistory"
headers={'Authorization': "Bearer " + TOKEN}
r = requests.get(URL, headers=headers)
j = json.loads(r.text)
print("Uploading file(s) to "+URL)
for root, dirs, files in os.walk(directory):
    for filename in files:
        filepath = os.path.join(root,filename)
        print("Uploading "+filename+"....")
        fileHandle = open(filepath, 'rb')
        r = requests.put(URL+"/"+filename+":/content", data=fileHandle, headers=headers)
        fileHandle.close()
        if r.status_code == 200 or r.status_code == 201:
            #remove folder contents
            print("succeeded, removing original file...")
            os.remove(os.path.join(root, filename)) 
print("Script completed")
raise SystemExit