Connecting to Azure SQL Database using SQLAlchemy and Microsoft Entra authentication

In this blog, we will focus on a common solution that demonstrates how to securely connect to an Azure SQL Database using Microsoft Entra Authentication with the current logged in user. It leverages the SQLAlchemy library for Python, integrating Entra's secure identity framework with your database connection.

 

Key Steps:

1. Set Current User as Admin: You begin by configuring an Azure Entra account as the admin for the Azure SQL Server.
2. Configure Firewall Rules: Ensure that your machine or application has access by adding its IP address to the Azure SQL Server firewall.
3. Create Secure Connection: Finally, the Python SQLAlchemy library is used to connect to the database, relying on Microsoft Entra authentication instead of hard-coded credentials.

With this setup, we achieve a secure, credential-less connection to Azure SQL Database!

 

Comparing Azure SQL Authentication Methods

 

Before diving into the solution, let's compare authentication methods. When it comes to securing access to your Azure SQL Database, the method you choose for authentication can significantly impact both the security and manageability of your applications. There are two primary methods commonly used: SQL Authentication, which relies on username and password credentials, and Microsoft Entra Managed Identity, which utilizes Microsoft Entra ID (formally Azure AD) for identity and access management.

 

SQL Authentication Drawbacks

 

SQL Authentication, while straightforward, comes with inherent security risks and management burdens. One of the main concerns is the reliance on hard-coded or stored credentials, often passed through connection strings in application code or configuration files. Additionally, using the stored static credentials allows continued access until explicitly revoked, enlarging your database's attack surface. For example, when using SQL Authentication, developers might include connection credentials like this:

 

connection_string = "Driver={SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdb;Uid=yourusername;Pwd=yourpassword;"

 

In this example, embedding the username and password in the application introduces several vulnerabilities:

• Credential Exposure: If the codebase is shared, leaked, or compromised, database credentials can be exposed.
• Secret Management: You need solutions like Azure Key Vault to securely store and rotate credentials, adding complexity.
• Credential Rotations: SQL credentials require manual or automated rotation, increasing operational overhead.

 

Improved Security with Microsoft Entra authentication

 

Microsoft Entra authentication (formerly known as Azure AD) offers a more secure and manageable way to authenticate applications and users to Azure SQL Database. Instead of relying on stored credentials, Microsoft Entra uses tokens generated dynamically and securely by Azure's identity management system, eliminating the need for static credentials in your applications or configuration files.

 

Key Security Advantages:

1. Credential-less Access: No need to store or transmit sensitive credentials (username and password) in code or configuration files.
2. Time-Limited Access: Entra-generated tokens have limited lifetimes, reducing the risk of misuse or unauthorized access over extended periods.
3. Centralized Management: Entra integrates seamlessly with other Azure services, providing centralized identity and access control across your applications.
4. Role-Based Access Control (RBAC): By using Entra authentication, access can be more finely tuned using RBAC, meaning users only get the permissions they need to perform their tasks.

In contrast to SQL Authentication, which requires manually revoking credentials, Microsoft Entra authentication ensures that when access to an account is revoked, it immediately affects all Azure services, preventing further unauthorized access. This vastly reduces the risk of security breaches due to stale credentials lingering in code repositories or configuration files.

 

Pre-requisites

• An Azure subscription.
• An Azure SQL database configured with Microsoft Entra authentication. You can create one using the Create database quickstart.
• The latest version of the Azure CLI.
• Visual Studio Code with the Python extension.
• Python 3.8 or later.
• ODBC Driver for SQL Server

 

Configure the Database

 

Setting Current User as Azure SQL DB Admin

 

First, you need to set your current Azure AD user as the Azure SQL Admin for your database. Follow the steps below:

1. Navigate to Your Azure SQL Server:
   • Log in to the Azure Portal.
   • Search for and select your Azure SQL Server (not the individual database).
2. Set Azure AD Admin:
   • In the left-hand menu, under Settings, click on Microsoft Entra ID.
   • Select Support Only Microsoft Entra authentication for this server to ensures no one can access the database server using SQL login credentials.
   • Click on Set admin.
     • In the Add admin pane, search for your user account.
     • Select your account and click Select.
     • This will set your user as a database admin and allow it to login using Microsoft Entra authentication.
   • Click on Save.

 

Adding Your IP Address to the Azure SQL Server Firewall

 

To ensure your connection to Azure SQL Database is secure and allowed, you will need to add your IP address to the server's firewall rules. This step prevents unauthorized IPs from accessing your server while allowing your trusted IP to connect. Follow these steps:

1. Navigate to Your Azure SQL Server:
   • Log in to the Azure Portal.
   • Search for and select your Azure SQL Server.
2. Configure Firewall Settings:
   • In the left-hand menu under Security, select Networking.
   • In the Public network access section, enable Selected networks to allow the firewall rule in order to whitelist your IP address.
   • Under the Firewall rules section, click on Add your client IPv4 address. This will automatically detect your current IP address and add it to the list of allowed addresses.
   • Click on Allow Azure services and resources to access this server. This will allow your web app running on Azure to access the database.
   • Click on Save.

 

 

At this point, we have set up an Azure AD user as the admin for the Azure SQL Server, enforcing Entra ID (formerly Azure AD) authentication and eliminating the need for SQL login credentials. This reduces the risk of credential exposure while streamlining identity management. We also added your IP to the Azure SQL Server firewall whitelist, ensuring only authorized IP addresses can connect, minimizing exposure to external threats.

 

With these security measures in place, we are ready to securely connect and interact with the Azure SQL Database using Python, leveraging Microsoft for seamless, credential-free authentication.

 

Set up the project

 

Now that the database setup is complete, we are ready to implement and use the code that will interact with the database. We will be using SQLAlchemy, which provides many database capabilities for python developers, like ORM capabilities and connection pooling.

 

1. Open Visual Studio Code and create a new folder for your project and change directory into it.

 

mkdir python-sql-azure
cd python-sql-azure

 

2. Create a requirements.txt file with the following content:

 

pyodbc
fastapi
uvicorn[standard]
pydantic
azure-identity
sqlalchemy

 

3. Create a start.sh file (this is only needed if you plan to deploy this project to azure)

 

gunicorn -w 4 -k uvicorn.workers.UvicornWorker app:app

 

4. Create an app.py file with the content below:

 

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 = 'sql-fg-database-s4ujd'
database_name = 'sqldb-fg-database-s4ujd'

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 PersonSchema(BaseModel):
    first_name: str
    last_name: Union[str, None] = None


class Person(Base):
    __tablename__ = "Persons"

    id: Mapped[int] = mapped_column(primary_key=True, name="ID")
    first_name: Mapped[str] = mapped_column(String(30), name="FirstName")
    last_name: Mapped[Optional[str]] = mapped_column(name="LastName")

    def __repr__(self) -> str:
        return f"Person(ID={self.id!r}, FirstName={self.first_name!r}, LastName={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)


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'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h

    cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}


# set up the database
Base.metadata.create_all(engine)


app = FastAPI()


@app.get("/all")
def get_persons():
    with Session(engine) as session:
        stmt = select(Person)

        rows = []
        for person in session.scalars(stmt):
            print(person.id, person.first_name, person.last_name)
            rows.append(f"{person.id}, {person.first_name}, {person.last_name}")

        return rows


@app.get("/person/{person_id}")
def get_person(person_id: int):
    with Session(engine) as session:
        stmt = select(Person).where(Person.id == person_id)
        person = session.execute(stmt).scalar()

        if not person:
            raise HTTPException(status_code=404, detail="Person not found")

        return f"{person.id}, {person.first_name}, {person.last_name}"


@app.post("/person")
def create_person(item: PersonSchema):
    with Session(engine) as session:
        person = Person(first_name=item.first_name, last_name=item.last_name)
        session.add(person)
        session.commit()

    return item

 

Notes:

• make sure to update the server_name and database_name variables in the code above with the names you used to create both the SQL server and the database
• The provide_token method will be called every time a database connection is created by the engine. It's responsible for injecting the EntraID token so it can successfully authenticate to the database. This is necessary in order to always have a fresh token when creating a connection, otherwise if we had a static token that was already expired, it would never be able to connect again to the database.

 

Running Locally

 

1. Create a virtual environment for the app

 

py -m venv .venv
.venv\scripts\activate

 

2. Install requirements

 

pip install -r requirements.txt

 

3. Run the app.py file in Visual Studio Code.

 

uvicorn app:app --reload

 

4. Open the Swagger UI at http://127.0.0.1:8000/docs

5. Create a new user using the Create Person endpoint

6. Try Get Person and Get Persons endpoints

 

Running on Azure

 

1. Use the az webapp up to deploy the code to App Service. 

 

az webapp up --resource-group <resource-group-name> --name <web-app-name>   

 

2. Use the az webapp config set command to configure App Service to use the start.sh file.

 

az webapp config set --resource-group <resource-group-name> --name <web-app-name> --startup-file start.sh

 

3. Use the az webapp identity assign command to enable a system-assigned managed identity for the App Service. This is needed because we will grant database access to this identity, with specific roles.

 

az webapp identity assign --resource-group <resource-group-name> --name <web-app-name>

 

4. Grant permissions to the web app identity by running the SQL commands below on your database. The first commanda creates a database user for the web app and the following ones sets data reader/writer roles (you can find more details about roles at Database-level roles - SQL Server | Microsoft Learn). By doing this we guarantee that the web app has the least privilege.

 

CREATE USER [<web-app-name>] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [<web-app-name>]
ALTER ROLE db_datawriter ADD MEMBER [<web-app-name>]

 

5. Open the Swagger UI at https://<web-app-name>.azurewebsites.net/docs and test the endpoints again

 

References

• Connect to and query Azure SQL Database using Python and the pyodbc library - Azure SQL Database | Microsoft Learn