Connecting to Azure SQL Server with Azure Functions Using Native Drivers and Managed Identity

Welcome to our practical guide on connecting Azure Functions to Azure SQL Server using native drivers and Managed Identity. In this article, you will learn how to set up your development environment and implement a robust Azure Function with SQL Server. We will explore authentication methods, such as system-assigned managed identity, user-assigned managed identity, and service principal. 🌐

· What is Azure SQL Server?
· Step 1: Setting Up the Environment
· Step 2: Architecture with SQLClientFactory
· Step 3: Using the Factory in an Azure Function
∘ Example 1: Azure Function with System-assigned Managed Identity
∘ Example 2: Azure Function with User-assigned Managed Identity
∘ Example 3: Azure Function with Service Principal
· Conclusion

What is Azure SQL Server?

Azure SQL Server is a fully managed relational database service provided by Microsoft. It offers a robust solution for hosting your cloud applications, providing high performance, scalability, and security.

Key Features and Benefits:

• Simplified Administration: Azure SQL Server handles critical tasks such as automated backups, updates, and disaster recovery, allowing developers to focus on building applications.
• Elastic Scalability: Increase or decrease computing and storage capacity as needed, without downtime.
• High Availability: With built-in replication and automatic failover, Azure SQL Server ensures your data is always accessible.
• Advanced Security: Provides encryption at rest and in transit, with identity-based authentication via Microsoft Entra ID.
• Full Compatibility: Compatible with on-premises SQL Server, making it easy to migrate to the cloud without code changes.

Step 1: Setting Up the Environment

Ensure you have the necessary dependencies installed:

npm install @azure/identity mssql

Step 2: Architecture with SQLClientFactory

Below is the code for the SQLClientFactory, which handles different authentication methods (managed identity and service principal):

import sql from 'mssql';

/**
 * SQLClientFactory is responsible for creating a connection to Azure SQL Server
 * using different authentication methods, such as Managed Identity or Service Principal.
 */
class SQLClientFactory {
  private server: string;
  private database: string;
  private port: number;
  private authenticationType: string;
  private clientId?: string;
  private clientSecret?: string;
  private tenantId?: string;

  /**
   * Creates an instance of SQLClientFactory.
   * 
   * @param server - The Azure SQL Server address.
   * @param database - The name of the Azure SQL database.
   * @param port - The port to connect to the Azure SQL database.
   * @param authenticationType - The type of authentication to use (e.g., 'ManagedIdentity' or 'ServicePrincipal').
   * @param clientId - (Optional) The client ID for user-assigned managed identity or service principal.
   * @param clientSecret - (Optional) The client secret for service principal authentication.
   * @param tenantId - (Optional) The tenant ID for service principal authentication.
   */
  constructor(
    server: string,
    database: string,
    port: number,
    authenticationType: string,
    clientId?: string,
    clientSecret?: string,
    tenantId?: string
  ) {
    this.server = server;
    this.database = database;
    this.port = port;
    this.authenticationType = authenticationType;
    this.clientId = clientId;
    this.clientSecret = clientSecret;
    this.tenantId = tenantId;
  }

  /**
   * Builds the configuration object based on the authentication type.
   * 
   * @returns The configuration object to be used by mssql to establish a connection.
   */
  private getConfig() {
    const config: any = {
      server: this.server,
      port: this.port,
      database: this.database,
      authentication: {
        type: this.authenticationType,
      },
      options: {
        encrypt: true,
      },
    };

    // Config for user-assigned managed identity
    if (this.authenticationType === 'ManagedIdentity' && this.clientId) {
      config.options.clientId = this.clientId;
    }

    // Config for service principal
    if (this.authenticationType === 'ServicePrincipal') {
      config.options.clientId = this.clientId;
      config.options.clientSecret = this.clientSecret;
      config.options.tenantId = this.tenantId;
    }

    return config;
  }

  /**
   * Creates and returns an SQL connection pool using the configuration.
   * 
   * @returns A Promise that resolves to the SQL connection pool.
   */
  async createClient() {
    const config = this.getConfig();
    return await sql.connect(config);
  }
}

import sql from 'mssql';

/**
 * SQLClientFactory is responsible for creating a connection to Azure SQL Server
 * using different authentication methods, such as Managed Identity or Service Principal.
 */
class SQLClientFactory {
  private server: string;
  private database: string;
  private port: number;
  private authenticationType: string;
  private clientId?: string;
  private clientSecret?: string;
  private tenantId?: string;

  /**
   * Creates an instance of SQLClientFactory.
   * 
   * @param server - The Azure SQL Server address.
   * @param database - The name of the Azure SQL database.
   * @param port - The port to connect to the Azure SQL database.
   * @param authenticationType - The type of authentication to use (e.g., 'ManagedIdentity' or 'ServicePrincipal').
   * @param clientId - (Optional) The client ID for user-assigned managed identity or service principal.
   * @param clientSecret - (Optional) The client secret for service principal authentication.
   * @param tenantId - (Optional) The tenant ID for service principal authentication.
   */
  constructor(
    server: string,
    database: string,
    port: number,
    authenticationType: string,
    clientId?: string,
    clientSecret?: string,
    tenantId?: string
  ) {
    this.server = server;
    this.database = database;
    this.port = port;
    this.authenticationType = authenticationType;
    this.clientId = clientId;
    this.clientSecret = clientSecret;
    this.tenantId = tenantId;
  }

  /**
   * Builds the configuration object based on the authentication type.
   * 
   * @returns The configuration object to be used by mssql to establish a connection.
   */
  private getConfig() {
    const config: any = {
      server: this.server,
      port: this.port,
      database: this.database,
      authentication: {
        type: this.authenticationType,
      },
      options: {
        encrypt: true,
      },
    };

    // Config for user-assigned managed identity
    if (this.authenticationType === 'ManagedIdentity' && this.clientId) {
      config.options.clientId = this.clientId;
    }

    // Config for service principal
    if (this.authenticationType === 'ServicePrincipal') {
      config.options.clientId = this.clientId;
      config.options.clientSecret = this.clientSecret;
      config.options.tenantId = this.tenantId;
    }

    return config;
  }

  /**
   * Creates and returns an SQL connection pool using the configuration.
   * 
   * @returns A Promise that resolves to the SQL connection pool.
   */
  async createClient() {
    const config = this.getConfig();
    return await sql.connect(config);
  }
}

Step 3: Using the Factory in an Azure Function

Now, we will integrate the SQLClientFactory into an Azure Function, handling different authentication methods.

Example 1: Azure Function with System-assigned Managed Identity

import { AzureFunction, Context, HttpRequest } from "@azure/functions";
import { SQLClientFactory } from "./SQLClientFactory";

/**
 * Azure Function using system-assigned managed identity to connect to Azure SQL Server.
 * 
 * @param context - The Azure Function context.
 * @param req - The incoming HTTP request.
 * @returns A promise that resolves with a response containing SQL query results.
 */
const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
  const factory = new SQLClientFactory(
    process.env.AZURE_SQL_SERVER,
    process.env.AZURE_SQL_DATABASE,
    parseInt(process.env.AZURE_SQL_PORT),
    'ManagedIdentity'
  );

  const pool = await factory.createClient();
  const result = await pool.request().query('SELECT TOP 10 * FROM YourTable');

  context.res = {
    body: result.recordset,
  };
};

export default httpTrigger;

Example 2: Azure Function with User-assigned Managed Identity

import { AzureFunction, Context, HttpRequest } from "@azure/functions";
import { SQLClientFactory } from "./SQLClientFactory";

/**
 * Azure Function using user-assigned managed identity to connect to Azure SQL Server.
 * 
 * @param context - The Azure Function context.
 * @param req - The incoming HTTP request.
 * @returns A promise that resolves with a response containing SQL query results.
 */
const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
  const factory = new SQLClientFactory(
    process.env.AZURE_SQL_SERVER,
    process.env.AZURE_SQL_DATABASE,
    parseInt(process.env.AZURE_SQL_PORT),
    'ManagedIdentity',
    process.env.AZURE_SQL_CLIENTID
  );

  const pool = await factory.createClient();
  const result = await pool.request().query('SELECT TOP 10 * FROM YourTable');

  context.res = {
    body: result.recordset,
  };
};

export default httpTrigger;

Example 3: Azure Function with Service Principal

import { AzureFunction, Context, HttpRequest } from "@azure/functions";
import { SQLClientFactory } from "./SQLClientFactory";

/**
 * Azure Function using service principal to connect to Azure SQL Server.
 * 
 * @param context - The Azure Function context.
 * @param req - The incoming HTTP request.
 * @returns A promise that resolves with a response containing SQL query results.
 */
const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
  const factory = new SQLClientFactory(
    process.env.AZURE_SQL_SERVER,
    process.env.AZURE_SQL_DATABASE,
    parseInt(process.env.AZURE_SQL_PORT),
    'ServicePrincipal',
    process.env.AZURE_SQL_CLIENTID,
    process.env.AZURE_SQL_CLIENTSECRET,
    process.env.AZURE_SQL_TENANTID
  );

  const pool = await factory.createClient();
  const result = await pool.request().query('SELECT TOP 10 * FROM YourTable');

  context.res = {
    body: result.recordset,
  };
};

export default httpTrigger;

Conclusion

You have successfully set up Azure SQL Server using authentication in Azure Functions, with a modular and flexible architecture to support different authentication methods (Managed Identity and Service Principal). This approach ensures scalability and maintainability of your system.