Connect Your Application to Azure SQL Managed Instance

This tutorial guides you through the process of connecting your application to an Azure SQL Managed Instance. We'll cover network configuration, obtaining connection strings, and providing code examples for popular programming languages.

Introduction

Azure SQL Managed Instance is a cloud-based managed instance of the Microsoft SQL Server engine that provides near 100% compatibility with on-premises SQL Server. Connecting your applications securely and efficiently is a crucial step in leveraging this powerful service.

Prerequisites

An existing Azure SQL Managed Instance.
An application that needs to connect to the Managed Instance.
Appropriate network connectivity established between your application environment and the Managed Instance's virtual network.

Step 1: Configure Network Security

Ensuring secure access to your SQL Managed Instance is paramount. You'll typically need to configure:

Virtual Network (VNet) Integration: If your application is hosted on Azure services like App Service or VMs within a VNet, ensure they are connected to the Managed Instance's VNet.
Firewall Rules: If your application is outside the Managed Instance's VNet, you'll need to configure firewall rules to allow inbound traffic from your application's IP address or range.
Private Endpoint: For enhanced security, consider using a Private Endpoint to access your Managed Instance privately from your VNet.

Refer to the Azure SQL Managed Instance networking documentation for detailed guidance.

Step 2: Obtain Connection Strings

Connection strings provide the necessary information for your application to establish a connection. You can find these in the Azure portal:

Navigate to your Azure SQL Managed Instance resource in the Azure portal.
Under the "Settings" section, select "Connection strings".
You will find different connection strings for various authentication methods (SQL Authentication, Azure Active Directory). Copy the one that suits your application's needs.

A typical SQL authentication connection string looks like this:

Server=your_managed_instance_name.your_region.database.windows.net;Database=your_database_name;User ID=your_username;Password=your_password;

For Azure Active Directory authentication, the format might vary depending on the specific library or provider you are using.

Step 3: Connect with Application

Here are examples of how to connect using common programming languages and their respective database drivers/libraries.

Connecting with .NET

Using Microsoft.Data.SqlClient:

using Microsoft.Data.SqlClient;
using System;

public class SqlConnector
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=your_managed_instance_name.your_region.database.windows.net;Database=your_database_name;User ID=your_username;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection established successfully!");
                // Perform database operations here
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error: {e.Message}");
            }
        }
    }
}

Connecting with Java

Using JDBC driver:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JavaSqlConnector {
    public static void main(String[] args) {
        String url = "jdbc:sqlserver://your_managed_instance_name.your_region.database.windows.net:1433;databaseName=your_database_name;user=your_username;password=your_password;";

        try (Connection connection = DriverManager.getConnection(url)) {
            System.out.println("Connection established successfully!");
            // Perform database operations here
        } catch (SQLException e) {
            System.err.println("Error: " + e.getMessage());
        }
    }
}

Connecting with Python

Using pyodbc:

import pyodbc

server = 'your_managed_instance_name.your_region.database.windows.net'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'
driver= '{ODBC Driver 17 for SQL Server}' # Ensure you have the driver installed

try:
    conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)
    cursor = conn.cursor()
    print("Connection established successfully!")
    # Perform database operations here
    cursor.close()
    conn.close()
except pyodbc.Error as ex:
    sqlstate = ex.args[0]
    if sqlstate == '28000':
        print("Authentication failed. Check username and password.")
    else:
        print(f"Error connecting to database: {ex}")

Connecting with Node.js

Using tedious (for SQL Server):

const Connection = require('tedious').Connection;
const config = {
    server: 'your_managed_instance_name.your_region.database.windows.net',
    authentication: {
        type: 'default',
        options: {
            userName: 'your_username',
            password: 'your_password'
        }
    },
    options: {
        database: 'your_database_name',
        encrypt: true // Consider enabling encryption
    }
};
const connection = new Connection(config);

connection.on('connect', (err) => {
    if (err) {
        console.error('Error connecting:', err);
    } else {
        console.log('Connection established successfully!');
        // Perform database operations here
        connection.close();
    }
});

connection.connect();

Connection Pooling

For performance-critical applications, it is highly recommended to use connection pooling. Most database drivers and ORMs provide built-in support for connection pooling. This allows you to reuse existing connections rather than establishing a new one for every request, significantly reducing latency.

Always configure connection pooling in your application's data access layer.

Next Steps

Now that you can connect to your Azure SQL Managed Instance, you can proceed to:

< Previous: Configure networking Next: Deploy application >