#DefaultAzureCredential | Explore Tumblr Posts and Blogs

joelby · 3 years

Text

NodeJS, Azure MySQL, Managed Identity

I couldn’t find any good information on how to connect to Azure Database for MySQL using DefaultAzureCredential, which is a nice way of supporting Azure CLI auth and Managed Identity. I spent some time figuring it out!

Use the mysql2 library - this did not work with mysql

const { DefaultAzureCredential } = require("@azure/identity"); const mysql = require('mysql2'); const credential = new DefaultAzureCredential(); const token = await credential.getToken("https://ossrdbms-aad.database.windows.net") const username = "yourdatabaseusername@youraaddomain@DATABASENAME" var connection = mysql.createConnection({ host: "DATABASENAME.mysql.database.azure.com", user: mysqlUsername, password: token.token, database: "databasename", ssl: { rejectUnauthorized: true }, insecureAuth: true, authPlugins: { mysql_clear_password: () => () => { return Buffer.from(token.token + '\0') } } }); connection.connect();

Instead of supplying your database username, if you're using az cli authentication (e.g. for local development) you could extract your AAD username from token.token, since it's a JWT token. This would probably be a nice convenience.

Unfortunately this doesn't work for managed identity, since the token doesn't contain a username. So! This could be made a bit smarter - either you pass in a database username for a managed identity, fall back to trying to extract it from the token for local development, or else throw an error.

0 notes

dinakar · 4 years

Text

Azure function to Pause/ Resume Synapse Compute

If you have ever provisioned an Azure Synapse data warehouse, it wouldn't take long for you to notice that it doesn't come cheap. Sure thing, it makes creating and managing data warehouses way better than previous Microsoft offerings, but you'd be wise to keep an eye on the costs.

Synapse's architecture decouples compute and storage, which means you can pause compute when not in use and stop incurring costs. Ideally you'd first determine time slot(s) where compute can be paused and then have an time triggered program pause and resume the compute as needed.

Here is the time triggered Azure function written in Python to pause/ resume synapse. For the sake of brevity, I have included very basic schedule - pause between 6pm to 10pm on weekdays and 7am to 10pm on weekends. Run this function every hour. It looks at the schedule and pauses and resumes as needed. Follow along with comments.

You'd need service principal details - appid, password and tentantid - to call the API's.

Store above in Azure Keyvault.

Access Keyvault using DefaultAzureCredentials and System Assigned Identity for the function.

Authentication + Authorization for API's using access token as bearer. Details here

Link to github gist

import datetime import logging import os import requests from pytz import timezone import azure.functions as func from azure.identity import DefaultAzureCredential from azure.keyvault.secrets import SecretClient def main(mytimer: func.TimerRequest) -> None: utc_timestamp = datetime.datetime.utcnow().replace( tzinfo=datetime.timezone.utc).isoformat() if mytimer.past_due: logging.info('The timer is past due!') try: #obtain the credential object and use function's system assigned identity to access service principal details from keyvault #enable System Assigned Identity for the function. #make sure function has access to list the secrets. credential = DefaultAzureCredential() #get service principal id, password and tenant id. Used to call Azure API's tenant_id,client_id,client_password = get_credential_entities(credential) if not tenant_id and client_id and client_password: raise Exception("missing credentials") else: access_token = get_access_token(tenant_id,client_id,client_password) if not access_token: raise Exception("missing access token") else: what_to_do = detemine_what_to_do() synapse_status = get_current_status(access_token) if synapse_status: #use the access toke to pause synapse if((what_to_do == "pause" and synapse_status != "Paused") or (what_to_do == "resume" and synapse_status != "Online")): control_synapse(what_to_do,access_token) else: logging.info("***** nothing to do in this run") else: raise Exception("could not detemine current status") except Exception as ex: logging.exception(f"exception in main module {ex}") logging.info('Python timer trigger function ran at %s', utc_timestamp) def get_credential_entities(credential): try: #get keyvault url from application setting. vault_url = os.environ["custom_keyvault_url"] #define secret_client_object secret_client = SecretClient(vault_url=vault_url, credential=credential) #get the secret value. both authentication and authorization happen here using DefaultAzureCredentials #enable System Assigned Identity for the function. #make sure function has access to list the secrets. retireved_client_password = secret_client.get_secret(os.environ['custom_keyvault_secret_client_password']) retireved_client_id = secret_client.get_secret(os.environ['custom_keyvault_secret_clientid']) retireved_tenantd_id = secret_client.get_secret(os.environ['custom_keyvault_secret_tenantid']) return retireved_tenantd_id.value,retireved_client_id.value,retireved_client_password.value except Exception as ex: logging.exception(f"exception in get_credential_entities module {ex}") return "","","" def get_access_token(tenant_id,client_id,client_password): try: #build the access token api url using tenantid token_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/token" #build payload using clientid and client password payload = f"grant_type=client_credentials&client_id={client_id}&client_secret={client_password}&resource=https%3A//management.azure.com/" #define headers headers = {"Content-Type": "application/x-www-form-urlencoded"} #use requests to get token token_response = requests.request("POST", token_url, headers=headers, data = payload).json() return token_response["access_token"] except Exception as ex: logging.exception(f"exception in get_access_token module: {ex}") return "" def control_synapse(operation,access_token): try: #build the pause/ resume api call url with access token control_url = f"https://management.azure.com/subscriptions/{os.environ['custom_setting_subscriptionid']}/resourceGroups/{os.environ['custom_setting_resource_group']}/providers/Microsoft.Sql/servers/{os.environ['custom_setting_db_server']}/databases/{os.environ['custom_setting_db_name']}/{operation}?api-version=2017-10-01-preview" payload = {} headers = {"Authorization": f"Bearer {access_token}"} response = requests.request("POST", control_url, headers=headers, data = payload) except Exception as ex: logging.exception(f"exception in pause_synapse module: {ex}") def get_current_status(access_token): try: #build the status api call url with access token status_url = f"https://management.azure.com/subscriptions/{os.environ['custom_setting_subscriptionid']}/resourceGroups/{os.environ['custom_setting_resource_group']}/providers/Microsoft.Sql/servers/{os.environ['custom_setting_db_server']}/databases/{os.environ['custom_setting_db_name']}?api-version=2014-04-01" payload = {} headers = {"Authorization": f"Bearer {access_token}"} response = requests.request("GET", status_url, headers=headers, data = payload) converted_response = response.text synapse_staus = converted_response[converted_response.index("") + len(""):converted_response.index("")] return synapse_staus except Exception as ex: logging.exception(f"exception in pause_synapse module: {ex}") return "" def detemine_what_to_do(): try: #get time in est. est = timezone('US/Eastern') runtime = datetime.datetime.now(est) runtime_hour,runtime_weekday = runtime.hour,runtime.strftime("%A") return { (runtime_weekday not in ["Saturday","Sunday"] and (runtime_hour >= 18 and runtime_hour < 22)) :"pause", (runtime_weekday in ["Saturday","Sunday"] and (runtime_hour >= 7 and runtime_hour < 22)) :"pause" }.get(True,"resume") except Exception as ex: logging.exception("exception in detemine_what_to_do module: {ex}")

0 notes