Home NewsX Securely Connect to Azure SQL Database with SQLAlchemy and Microsoft Entra Authentication

Securely Connect to Azure SQL Database with SQLAlchemy and Microsoft Entra Authentication

by info.odysseyx@gmail.com
0 comment 17 views


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:

  1. Set current user as administrator: First, configure the Azure Entra account as an administrator for Azure SQL Server.
  2. Configure firewall rules: Make sure you can access your machine or application by adding the IP address to your Azure SQL Server firewall.
  3. 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:

  1. Credentialless access: No need to store or transmit sensitive credentials (usernames and passwords) in code or configuration files.
  2. Time-limited access: Internally generated tokens have a limited lifetime, reducing the risk of long-term misuse or unauthorized access.
  3. Centralized Management: Entra integrates seamlessly with other Azure services to provide centralized identity and access control across your applications.
  4. 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:

  1. Go to Azure SQL Server.
    • log in Azure portal.
    • Search and select. Azure SQL Server (not a separate database)
  2. 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.

AzureSQLDB-UserSetAdmin-Setup.png

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:

  1. Go to Azure SQL Server.
  2. 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.

AzureSQLDB-IpAddress-Setup.png

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

You may also like

Leave a Comment

Our Company

Welcome to OdysseyX, your one-stop destination for the latest news and opportunities across various domains.

Newsletter

Subscribe my Newsletter for new blog posts, tips & new photos. Let's stay updated!

Laest News

@2024 – All Right Reserved. Designed and Developed by OdysseyX