MSDN Documentation

Connecting to Azure SQL Database

This document guides you through the process of connecting to your Azure SQL Database from various client applications and development environments.

Prerequisites

• An Azure SQL Database instance has been provisioned.
• You have the server name, database name, and authentication credentials (SQL authentication or Azure Active Directory authentication).
• Firewall rules are configured to allow access from your client IP address or range.

Connection Strings

Connection strings are essential for specifying how your application connects to the database. The format varies depending on the client library or tool you are using.

Finding Your Connection String

You can find basic connection string templates in the Azure portal:

1. Navigate to your Azure SQL Database resource in the Azure portal.
2. In the left-hand menu, under Settings, select Connection strings.
3. Choose the driver or API you intend to use (e.g., ADO.NET, ODBC, PHP, JDBC).
4. Copy the provided connection string template and replace placeholders like {your_server_name}, {your_database_name}, {your_username}, and {your_password} with your actual credentials.

Example Connection Strings

Here are some common examples:

ADO.NET (SQL Authentication)

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;

ADO.NET (Azure AD Authentication)

Server=tcp:your_server_name.database.windows.net,1433;Initial Catalog=your_database_name;Persist Security Info=False;User ID=your_username@your_tenant.onmicrosoft.com;Password=your_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Password";

JDBC (SQL Authentication)

jdbc:sqlserver://your_server_name.database.windows.net:1433;database=your_database_name;user=your_username;password=your_password;encrypt=true;trustservercertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Connecting with SQL Server Management Studio (SSMS)

SSMS is a popular tool for managing SQL Server databases, including Azure SQL Database.

1. Launch SQL Server Management Studio.
2. In the Connect to Server dialog, enter your Azure SQL Database server name (e.g., your_server_name.database.windows.net).
3. Select the authentication type (SQL Server Authentication or Azure Active Directory).
4. Enter your login details.
5. Click Connect.

Connecting with Azure Data Studio

Azure Data Studio is a cross-platform database tool for data professionals.

1. Download and install Azure Data Studio.
2. Click the New Connection button.
3. In the Connection dialog:
   • Connection type: Microsoft SQL Server
   • Server: your_server_name.database.windows.net
   • Authentication type: SQL Login or Azure Active Directory
   • Enter your username and password.
4. Click Connect.

Connecting from Applications

Most programming languages and frameworks provide libraries for connecting to SQL Server. You will typically use the connection string generated from the Azure portal or constructed manually.

.NET (Entity Framework Core)

When using Entity Framework Core, you configure the connection string in your DbContext or appsettings.json.

// Example in DbContext configuration
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer("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;");
}

Python (pyodbc)

Ensure you have the ODBC driver installed.

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()
print(row)

cnxn.close()

Troubleshooting Connection Issues

• Firewall Rules: Ensure your client IP address is allowed through the Azure SQL Database firewall. You can configure this in the Azure portal under Networking.
• Authentication: Double-check your username, password, and authentication method. For Azure AD, ensure the user is correctly provisioned and has permissions.
• Server Name: Verify the server name is correct, including the .database.windows.net suffix.
• Driver/Library Versions: Make sure you are using compatible versions of SQL Server client libraries and drivers.