Connect to Azure SQL Database - Azure Documentation

Introduction

Azure SQL Database is a fully managed relational database service that supports your application workloads. This tutorial demonstrates how to connect to an Azure SQL Database from common development environments and tools. We'll cover methods for programmatic access using various programming languages and connection strings.

Prerequisites

Before you begin, ensure you have the following:

An Azure account. If you don't have one, sign up for a free account.
An Azure SQL Database instance created. If you need to create one, follow the Quickstart: Create an Azure SQL Database.
The server name, database name, and login credentials for your Azure SQL Database. You can find these details in the Azure portal.

Obtaining Connection Strings

The most straightforward way to connect is by using a connection string. You can find pre-generated connection strings for your SQL Database in the Azure portal.

Navigate to your Azure SQL Database resource in the Azure portal.
In the left-hand menu, under "Settings," select Connection strings.
You will find various connection strings for different libraries and tools. Choose the one that best suits your needs (e.g., ADO.NET, ODBC, PHP, Python).

Important: For security, avoid hardcoding sensitive credentials directly in your application code. Consider using Azure Key Vault or other secure methods for storing and retrieving secrets.

Connecting with .NET (ADO.NET)

Here's a C# example using ADO.NET to connect and query your Azure SQL Database.

Example Code

using System;
using System.Data.SqlClient;

public class AzureSqlConnector
{
    public static void Main(string[] args)
    {
        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!");

                // Example query
                string sql = "SELECT @@VERSION";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"SQL Server Version: {reader.GetString(0)}");
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Connection failed: {e.Message}");
            }
        }
    }
}

Connecting with Python

Connect to Azure SQL Database using Python with the pyodbc library.

Prerequisites

Install the library:

pip install pyodbc

Example Code

import pyodbc

server = 'your_server_name.database.windows.net'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'
driver= '{ODBC Driver 17 for SQL Server}'

cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

# Example query
cursor.execute("SELECT @@VERSION;")
row = cursor.fetchone()
if row:
    print(row[0])

Connecting with Node.js

Use the mssql package for Node.js.

Prerequisites

Install the package:

npm install mssql

Example Code

const sql = require('mssql');

const config = {
    user: 'your_username',
    password: 'your_password',
    server: 'your_server_name.database.windows.net',
    database: 'your_database_name',
    options: {
        encrypt: true,
        trustServerCertificate: false
    }
};

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

        const result = await sql.query`SELECT @@VERSION`;
        console.log('SQL Server Version:', result.recordset[0]['@@VERSION']);
    } catch (err) {
        console.error('Connection failed:', err.message);
    } finally {
        sql.close();
    }
}

connectAndQuery();

Troubleshooting Common Issues

Firewall Rules: Ensure that your Azure SQL Database server has firewall rules configured to allow connections from your IP address or virtual network.
Credentials: Double-check your username, password, server name, and database name.
Driver/Library Versions: Verify that you are using compatible versions of ODBC drivers or programming language libraries.
Network Connectivity: Ensure there are no network restrictions preventing your application from reaching the Azure SQL Database endpoint.

Next Steps

Once you have successfully connected, you can start performing CRUD operations, implementing stored procedures, and managing your database.