Announcing Python in Excel: Combining the power of Python and the flexibility of Excel.
Since its inception, Microsoft Excel has changed how people organize, analyze, and visualize their data, providing a basis for decision-making for the millions of people who use it each day. Today we’re announcing a significant evolution in the analytical capabilities available within Excel by releasing a Public Preview of Python in Excel. Python in Excel makes it possible to natively combine Python and Excel analytics within the same workbook - with no setup required. With Python in Excel, you can type Python directly into a cell, the Python calculations run in the Microsoft Cloud, and your results are returned to the worksheet, including plots and visualizations.Fetching Windows Auth User ID Issue in Python Flask Application on IIS with HttpPlatformHandler
Problem : Deploying Python Flask applications on IIS can be a smooth process, but occasionally, issues arise that require careful troubleshooting. One such issue involves the failure of a Flask application to retrieve the Windows Authentication user ID when using the HttpPlatformHandler. Please note that retrieving the user details was successful using WFastCGI but not with HttpPlatformHandler. Let’s see how we can fetch the user details in such scenario. Few Pointers : Move to HttpPlateFormHandlers form WFastCGI: WFastCGI is no longer maintained. Refer to this. Configure Python web apps for IIS - Visual Studio (Windows) | Microsoft Learn Configuration Adjustment: A key step was enabling the ForwardWindowsAuthToken option in the HttpPlatformHandler configuration. This setting forwards the Windows Authentication token to the application, allowing it to be accessed and processed within the code. Code Implementation: After adjusting the configuration, you can update the Flask application code to fetch the Windows Authentication user ID. The following code snippet demonstrates how this was done: from flask import Flask, request, render_template import os import win32api import win32security def create_app(): app = Flask(__name__) @app.route("/") def hello_world(): s_vars = request.environ user = os.environ.get('USERNAME') handle_str = request.headers['x-iis-windowsauthtoken'] handle = int(handle_str,16) win32security.ImpersonateLoggedOnUser(handle) user1 = win32api.GetUserName() win32api.CloseHandle(handle) return f"Hello World!: {user1}" return app This code snippet demonstrates how to use the win32api and win32security modules to impersonate the logged-on user and retrieve their username. The important element here is the x-iis-windowsauthtoken header, which contains the Windows Authentication token passed on by the HttpPlatformHandler. Ensure Dependencies: Please ensure that the pywin32 package is installed, as it provides the necessary functionality to interact with Windows APIs within the Python environment. For further information, refer to the following resources: Configure Web Apps for IIS in Python HttpPlatformHandler Configuration ReferenceGenerative AI for Beginners - Full Videos Series Released!
With so many new technologies, tools and terms in the world of Generative AI, it can be hard to know where to start or what to learn next. "Generative AI for Beginners" is designed to help you on your learning journey no matter where you are now. We are happy announce that the "Generative AI for Beginners" course has received a major refresh - 18 new videos for each lesson.Introducing Advanced KQL Free Online Course
Are you analyze data on ADX? User Analytics? Geographic? Machine Learning? Time Series Analysis? Anomaly Detection? You have come to the right place! You are welcome to take our new Advanced KQL course that will boost your knowledge and skills for performing advanced analytics in ADX
How to connect Azure SQL database from Python Function App using managed identity or access token
This blog will demonstrate on how to connect Azure SQL database from Python Function App using managed identity or access token. If you are looking for how to implement it in Windows App Service, you may refer to this post: https://techcommunity.microsoft.com/t5/apps-on-azure-blog/how-to-connect-azure-sql-database-from-azure-app-service-windows/ba-p/2873397. Note that Azure Active Directory managed identity authentication method was added in ODBC Driver since version 17.3.1.1 for both system-assigned and user-assigned identities. In Azure blessed image for Python Function, the ODBC Driver version is 17.8. Which makes it possible to leverage this feature in Linux App Service. Briefly, this post will provide you a step to step guidance with sample code and introduction on the authentication workflow. Steps: 1. Create a Linux Python Function App from portal 2. Set up the managed identity in the new Function App by enable Identity and saving from portal. It will generate an Object(principal) ID for you automatically. 3. Assign role in Azure SQL database. Search for your own account and save as admin. Note: Alternatively, you can search for the function app's name and set it as admin, then that function app would own admin permission on the database and you can skip step 4 and 5 as well. 4. Got to Query editor in database and be sure to login using your account set in previous step rather than username and password. Or step 5 will fail with below exception. "Failed to execute query. Error: Principal 'xxxx' could not be created. Only connections established with Active Directory accounts can create other Active Directory users." 5. Run below queries to create user for the function app and alter roles. You can choose to alter part of these roles per your demand. CREATE USER "yourfunctionappname" FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER "yourfunctionappname" ALTER ROLE db_datawriter ADD MEMBER "yourfunctionappname" ALTER ROLE db_ddladmin ADD MEMBER "yourfunctionappname" 6. Leverage below sample code to build your own project and deploy to the function app. Sample Code: Below is the sample code on how to use Azure access token when run it from local and use managed identity when run in Function app. The token part needs to be replaced with your own. Basically, it is using "pyodbc.connect(connection_string+';Authentication=ActiveDirectoryMsi')" to authenticate with managed identity. Also, "MSI_SECRET" is used to tell if we are running it from local or function app, it will be created automatically as environment variable when the function app is enabled with Managed Identity. The complete demo project can be found from: https://github.com/kevin808/azure-function-pyodbc-MI import logging import azure.functions as func import os import pyodbc import struct def main(req: func.HttpRequest) -> func.HttpResponse: logging.info('Python HTTP trigger function processed a request.') server="your-sqlserver.database.windows.net" database="your_db" driver="{ODBC Driver 17 for SQL Server}" query="SELECT * FROM dbo.users" # Optional to use username and password for authentication # username = 'name' # password = 'pass' db_token = '' connection_string = 'DRIVER='+driver+';SERVER='+server+';DATABASE='+database #When MSI is enabled if os.getenv("MSI_SECRET"): conn = pyodbc.connect(connection_string+';Authentication=ActiveDirectoryMsi') #Used when run from local else: SQL_COPT_SS_ACCESS_TOKEN = 1256 exptoken = b'' for i in bytes(db_token, "UTF-8"): exptoken += bytes({i}) exptoken += bytes(1) tokenstruct = struct.pack("=i", len(exptoken)) + exptoken conn = pyodbc.connect(connection_string, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct }) # Uncomment below line when use username and password for authentication # conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = conn.cursor() cursor.execute(query) row = cursor.fetchone() while row: print(row[0]) row = cursor.fetchone() return func.HttpResponse( 'Success', status_code=200 ) Workflow: Below are the workflow in these two authentication ways, with them in mind, we can understand what happened under the hood. Managed Identity: When we enable the managed identify for function app, a service principal will be generated automatically for it, then it follows the same steps as below to authenticate in database. Function App with managed identify -> send request to database with service principal -> database check the corresponding database user and its permission -> Pass authentication. Access Token: The access toke can be generated by executing ‘az account get-access-token --resource=https://database.windows.net/ --query accessToken’ from local, we then hold this token to authenticate. Please note that the default lifetime for the token is one hour, which means we would need to retrieve it again when it expires. az login -> az account get-access-token -> local function use token to authenticate in SQL database -> DB check if the database user exists and if the permissions granted -> Pass authentication. Thanks for reading. I hope you enjoy it.
