Get Connection - Azure SQL Database

Getting a Connection to Azure SQL Database

This document provides guidance on how to establish a connection to your Azure SQL Database instance from various applications and services.

Overview

Connecting to Azure SQL Database involves using connection strings that contain the necessary credentials and server information. The specific method and authentication type depend on your application's requirements and the security policies you've implemented.

Connection Strings

A typical connection string for Azure SQL Database has the following format:

Server=tcp:your_server_name.database.windows.net,1433;Initial Catalog=your_database_name;Persist Security Info=False;User ID=your_username;Password=your_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Authentication Methods

Azure SQL Database supports several authentication methods:

SQL Authentication: Uses a username and password to authenticate.
Azure Active Directory (Azure AD) Authentication: Leverages your Azure AD identity for authentication, offering more robust security features like multi-factor authentication.
Managed Identities: Allows Azure services to authenticate to Azure SQL Database without credentials stored in code.

Connecting with Different Tools and Languages

C# (.NET) Example

Using the System.Data.SqlClient library:

// Replace with your actual connection string details
string connectionString = "Server=tcp:your_server_name.database.windows.net,1433;Initial Catalog=your_database_name;Persist Security Info=False;User ID=your_username;Password=your_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

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

Python Example

Using the pyodbc library:

import pyodbc

# Replace with your actual connection string details
connection_string = (
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=tcp:your_server_name.database.windows.net,1433;'
    r'DATABASE=your_database_name;'
    r'UID=your_username;'
    r'PWD=your_password;'
)

try:
    conn = pyodbc.connect(connection_string)
    print("Connection successful!")
    cursor = conn.cursor()
    # Perform database operations here
except pyodbc.Error as ex:
    sqlstate = ex.args[0]
    if sqlstate == '42000':
        print("Authentication failed. Check credentials.")
    else:
        print(f"Error connecting to database: {ex}")
finally:
    if 'conn' in locals() and conn:
        conn.close()

JDBC (Java) Example

Using the JDBC driver:

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

public class AzureSqlConnect {
    public static void main(String[] args) {
        // Replace with your actual connection string details
        String url = "jdbc:sqlserver://your_server_name.database.windows.net:1433;database=your_database_name;encrypt=true;trustServerCertificate=false;loginTimeout=30;";
        String user = "your_username";
        String password = "your_password";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connection successful!");
            // Perform database operations here
        } catch (SQLException e) {
            System.err.println("Error connecting to database: " + e.getMessage());
        }
    }
}

Node.js Example

Using the mssql package:

const sql = require('mssql');

// Replace with your actual connection string details
const config = {
    user: 'your_username',
    password: 'your_password',
    server: 'your_server_name.database.windows.net',
    database: 'your_database_name',
    options: {
        encrypt: true,
        trustServerCertificate: false
    },
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
};

async function connectToDatabase() {
    try {
        await sql.connect(config);
        console.log('Connection successful!');

        // Example query
        const result = await sql.query`SELECT @@VERSION`;
        console.log('Database version:', result.recordset[0]['(No column name)']);

    } catch (err) {
        console.error('Database connection error:', err);
    } finally {
        sql.close();
    }
}

connectToDatabase();

Security Considerations

Important Security Practices:

Avoid hardcoding credentials directly in your application code. Use secure methods like Azure Key Vault or environment variables.
Prefer Azure AD authentication over SQL authentication whenever possible.
Ensure you are using SSL/TLS encryption for all connections by setting Encrypt=True and TrustServerCertificate=False in your connection string.
Configure firewall rules for your Azure SQL Database server to restrict access to authorized IP addresses.

Troubleshooting

Common issues include:

Incorrect server name, database name, username, or password.
Firewall blocking access (check Azure portal firewall rules).
Network connectivity problems.
Incorrect driver or library versions.

Azure SQL Database Documentation