Securely Connect to Azure SQL Database with SQLAlchemy and Microsoft Entra Authentication by info.odysseyx@gmail.com October 29, 2024 written by info.odysseyx@gmail.com October 29, 2024 0 comment 17 views 17 This blog will focus on common solutions showing how to securely connect to Azure SQL Database using: Microsoft ENTRA Certification With the currently logged in user. This leverages: SQLAlchemy A library for Python that integrates Entra’s secure identity framework with database connectivity. Key steps: Set current user as administrator: First, configure the Azure Entra account as an administrator for Azure SQL Server. Configure firewall rules: Make sure you can access your machine or application by adding the IP address to your Azure SQL Server firewall. Create a secure connection: Finally Python SQLAlchemy The library is used to connect to the database using Microsoft Entra authentication instead of hard-coded credentials. This setup allows you to achieve a secure connection without credentials to your Azure SQL Database! Comparison of Azure SQL authentication methods Before looking at the solutions, let’s compare authentication methods. When it comes to securing access to your Azure SQL database, the method you choose for authentication can have a significant impact on both the security and manageability of your application. The two primary methods commonly used are SQL authentication, which uses username and password credentials, and Microsoft Entra Managed Identity, which leverages Microsoft Entra Identity (formally Azure AD) for identity and access management. SQL Authentication Disadvantages SQL authentication is simple, but it comes with inherent security risks and management burden. One of the main concerns is that they rely on hard-coded or stored credentials, which are often passed through connection strings in application code or configuration files. Additionally, using stored static credentials increases the attack surface of the database because they remain accessible until explicitly revoked. For example, when using SQL authentication, developers can include connection credentials like the following: connection_string = "Driver={SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdb;Uid=yourusername;Pwd=yourpassword;" In this example, including a username and password in the application introduces several vulnerabilities. Credential Exposure: If your codebase is shared, leaked, or compromised, your database credentials may be exposed. secret management: Adding complexity by securely storing and rotating credentials requires a solution like Azure Key Vault. Credential Rotation: SQL credentials require manual or automatic rotation, increasing operational overhead. Increased security with Microsoft Entra certification Microsoft ENTRA Certification (formerly Azure AD) provides a more secure and easier-to-manage way to authenticate applications and users to Azure SQL Database. Instead of relying on stored credentials, Microsoft Entra uses dynamically and securely generated tokens from Azure’s identity management system, eliminating the need for static credentials in your application or configuration files. Key security benefits: Credentialless access: No need to store or transmit sensitive credentials (usernames and passwords) in code or configuration files. Time-limited access: Internally generated tokens have a limited lifetime, reducing the risk of long-term misuse or unauthorized access. Centralized Management: Entra integrates seamlessly with other Azure services to provide centralized identity and access control across your applications. Role-based access control (RBAC): Entra authentication allows you to further fine-tune access using RBAC. This means that users only get the permissions they need to do their jobs. Unlike SQL authentication, which requires manual revoking of credentials, Microsoft Entra authentication immediately affects all Azure services when access to an account is revoked, preventing further unauthorized access. This greatly reduces the risk of a security breach due to outdated credentials remaining in your code repository or configuration files. Prerequisites Database configuration Set current user as Azure SQL DB administrator First, you need to set up your current Azure AD user as the Azure SQL administrator for your database. Please follow the steps below: Go to Azure SQL Server. log in Azure portal. Search and select. Azure SQL Server (not a separate database) Set up an Azure AD administrator. From the left menu settingPlease click Microsoft Entra ID. choose Only Microsoft Entra authentication is supported for this server Ensure that no one can access your database server using your SQL login credentials. Please click Administrator Settings. In the Add Administrator window, search for your user account. Select your account and click. choose. This sets the user up as a database administrator and allows them to log in using Microsoft Entra authentication. Please click get. Add IP address to Azure SQL Server firewall To ensure that connections to Azure SQL Database are secure and permitted, you must add the IP address to the server’s firewall rules. This step prevents unauthorized IPs from accessing the server while allowing trusted IP connections. Please follow these steps: Go to Azure SQL Server. log in Azure portal. Search and select. Azure SQL Server. Configure firewall settings: From the menu on the left below securitychoose networking. at public network access section, active selected network Allow firewall rules to whitelist IP addresses. under firewall rules Click Next in the section. Add client IPv4 address. This will automatically detect your current IP address and add it to the list of allowed addresses. Please click Allow Azure services and resources to access this server. This allows web apps running in Azure to access the database. Please click get. You can now set up an Azure AD user as an administrator for Azure SQL Server, enforcing Entra ID (formerly Azure AD) authentication and eliminating the need for SQL login credentials. This simplifies identity management while reducing the risk of credential exposure. We’ve also added your IP to the Azure SQL Server firewall whitelist to ensure that only authenticated IP addresses can connect, minimizing your exposure to external threats. With these security measures in place, you can use Python to securely connect to and interact with Azure SQL Database and leverage Microsoft for seamless, credential-less authentication. Project settings Now that you have your database set up, you are ready to implement and use the code that will interact with the database. we will use SQLAlchemyprovides many database features to Python developers, such as ORM functionality and connection pooling. 1. Open Visual Studio Code, create a new folder for your project, and change directories to that folder. mkdir python-sql-azure cd python-sql-azure 2. Making Requirements.txt A file containing the following content: pyodbc fastapi uvicorn[standard] pydantic azure-identity sqlalchemy 3. Create a start.sh file (only needed if you plan to deploy this project to Azure). gunicorn -w 4 -k uvicorn.workers.UvicornWorker app:app 4. Create app.py A file containing the following: import struct import urllib from typing import Union, Optional from fastapi import FastAPI, HTTPException from pydantic import BaseModel import sqlalchemy as db from sqlalchemy import String, select, event from sqlalchemy.orm import Session, Mapped, mapped_column from sqlalchemy.ext.declarative import declarative_base from azure.identity import DefaultAzureCredential driver_name="{ODBC Driver 18 for SQL Server}" server_name="" database_name="" connection_string = 'Driver={};Server=tcp:{}.database.windows.net,1433;Database={};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'.format(driver_name, server_name, database_name) Base = declarative_base() credential = DefaultAzureCredential() class UserSchema(BaseModel): first_name: str last_name: Union[str, None] = None class User(Base): __tablename__ = "user" id: Mapped[int] = mapped_column(primary_key=True) first_name: Mapped[str] = mapped_column(String(30)) last_name: Mapped[Optional[str]] def __repr__(self) -> str: return f"User(id={self.id!r}, first_name={self.first_name!r}, last_name={self.last_name!r})" def get_engine(): params = urllib.parse.quote(connection_string) url = "mssql+pyodbc:///?odbc_connect={0}".format(params) return db.create_engine(url, pool_size=1, max_overflow=0) engine = get_engine() # from https://docs.sqlalchemy.org/en/20/core/engines.html#generating-dynamic-authentication-tokens @event.listens_for(engine, "do_connect") def provide_token(dialect, conn_rec, cargs, cparams): """ Called before the engine creates a new connection. Injects an EntraID token into the connection parameters. """ print('creating new token') token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE") token_struct = struct.pack(f' memo: Be sure to update server_name and database_name Change the variable in the above code to the name you used to create the SQL server and database. that Provided_token The method is called whenever a database connection is created by the engine. It is responsible for injecting the EntraID token to enable successful authentication to the database. This is necessary to always have a fresh token when creating a connection. Otherwise, you will not be able to reconnect to the database if you have a static token that has already expired. run locally 1. Create a virtual environment for your app py -m venv .venv .venv\scripts\activate 2. Installation Requirements pip install -r requirements.txt 3. Run app.py This is a file from Visual Studio Code. uvicorn app:app --reload 4. Open the Swagger UI: http://127.0.0.1:8000/docs 5. Create a new user using: Create user endpoint 6. Try it Get users and User Acquisition endpoint Run on Azure 1. use az web app up Deploy your code to App Service. az webapp up --resource-group --name 2. use az webapp configuration set Commands to configure App Service to use start.sh file. az webapp config set --resource-group --name --startup-file start.sh 3. az webapp ID assignment Command to enable a system-assigned managed identity for App Service. This is necessary because you will be using a specific role to grant database access to this identity. az webapp identity assign --resource-group --name 4. Grant permission to the web app ID by running the SQL command below in the database. The first command creates a database user for the web app, and the next command sets the Data Reader/Writer role (more information about roles can be found here). Database level roles – SQL Server | microsoft run). This ensures that your web app has minimal permissions. CREATE USER [] FROM EXTERNAL PROVIDER ALTER ROLE db_datareader ADD MEMBER [] ALTER ROLE db_datawriter ADD MEMBER [] 5. Open the Swagger UI: https://.azurewebsites.net/docs Try testing your endpoint again. References Source link Share 0 FacebookTwitterPinterestEmail info.odysseyx@gmail.com previous post Microsoft 365 Copilot - Small Business Guide to Set Up Copilot next post Poor video quality is costing your business more than you think—here’s how to fix it You may also like Ride-sharing and Robotaxis Decopled Revenue Model Problems February 17, 2025 Web Raiders run the Global Brut Force attack from 2.5M IPS February 12, 2025 Generator Tech, Robot, risk of emerging February 11, 2025 Robotaxis is bringing in the lift dallas’ with ‘2026 with’ February 11, 2025 Why did Qualcom lose his first leadership February 10, 2025 Lenovo’s ThinkPad X 1 Carbon has rewrite my MacBook Pro February 5, 2025 Leave a Comment Cancel Reply Save my name, email, and website in this browser for the next time I comment.