Introduction

Connecting to an Azure SQL Database from Virtual Machines (VMs) is a common scenario when you want your applications running on VMs to access data stored in Azure SQL. In this guide, we'll cover the steps to establish a connection, best practices, and provide sample code to help you get started.


Prerequisites

Before you begin, ensure you have the following prerequisites in place:

  • An Azure subscription with an Azure SQL Database provisioned.
  • A virtual machine running in Azure (Windows or Linux).
  • Access to the virtual machine (SSH for Linux or RDP for Windows).

Configuring Azure SQL Database

To configure your Azure SQL Database for VM access, you need to:

  1. Allow the VM's public IP address to access the database using firewall rules in the Azure portal.
  2. Create a server-level login and user in the database for authentication.
  3. Note the database server name, username, and password for your connection string.

Sample Code: Connecting from a .NET Application (C#)

Here's an example of connecting to an Azure SQL Database from a C# application running on a Windows VM:

using System;
using System.Data.SqlClient;
var connectionString = "Server=YourServerName.database.windows.net;Database=YourDatabaseName;User Id=YourUserName;Password=YourPassword;";
using var connection = new SqlConnection(connectionString);
connection.Open();
// Perform database operations here
connection.Close();

Sample Code: Connecting from a Python Application (Linux VM)

Here's an example of connecting to an Azure SQL Database from a Python application running on a Linux VM:

import pyodbc
server = 'YourServerName.database.windows.net'
database = 'YourDatabaseName'
username = 'YourUserName'
password = 'YourPassword'
driver= '{ODBC Driver 17 for SQL Server}'
connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'
conn = pyodbc.connect(connection_string)
# Perform database operations here
conn.close()

Best Practices

When connecting from VMs to Azure SQL Database, consider implementing these best practices:

  • Use Managed Identity or Managed Service Identity (MSI) for secure authentication.
  • Utilize Azure Key Vault to store and retrieve connection strings securely.
  • Optimize query performance by using indexing and query tuning.

Conclusion

Connecting to Azure SQL Database from Virtual Machines is a crucial step in building applications that rely on cloud-based data storage. By following the steps outlined in this guide and using sample code, you can establish a secure and efficient connection between your VMs and Azure SQL Database.