Se connecter à la base de données et interroger Azure SQL à l’aide de .NET et de la bibliothèque Microsoft.Data.SqlClient

S’applique à Azure SQL Database

Ce guide de démarrage rapide explique comment connecter une application à une base de données dans Azure SQL Database et effectuer des requêtes à l’aide de .NET et de la bibliothèque Microsoft.Data.SqlClient. Ce guide de démarrage rapide suit l’approche sans mot de passe recommandée pour se connecter à la base de données. Vous pouvez en apprendre plus sur les connexions sans mot de passe sur le Hub des connexions sans mot de passe.

Prérequis

• Un abonnement Azure.
• Une base de données Azure SQL configurée avec l’authentification Microsoft Entra ID (anciennement Azure Active Directory). Vous pouvez en créer une à l’aide du Guide de démarrage rapide Créer une base de données.
• La version la plus récente d’Azure CLI.
• Visual Studio ou version ultérieure avec la charge de travail de Développement web et ASP.NET.
• .NET 7.0 ou version ultérieure.

Configurer la base de données

Les connexions sécurisées et sans mot de passe à Azure SQL Database nécessitent certaines configurations de base de données. Vérifiez les paramètres suivants sur votre serveur logique dans Azure pour vous connecter correctement à Azure SQL Database dans les environnements locaux et hébergés :

1. Pour les connexions de développement local, vérifiez que votre serveur logique est configuré pour autoriser l’adresse IP de votre ordinateur local et d’autres services Azure à se connecter :

   • Accédez à la page Réseau de votre serveur.

   • Activez la case d’option Réseaux sélectionnés pour voir des options de configuration supplémentaires.

   • Sélectionnez Ajouter l’adresse IPv4 de votre client (xx.xx.xx.xx.xx.xx) pour ajouter une règle de pare-feu qui activera les connexions à partir de l’adresse IPv4 de votre ordinateur local. Vous pouvez également sélectionner + Ajouter une règle de pare-feu pour entrer une adresse IP spécifique de votre choix.

   • Vérifiez que la case Autoriser les services et les ressources Azure à accéder à ce serveur est cochée.

     

     Avertissement

     L’activation du paramètre Autoriser les services et les ressources Azure à accéder à ce serveur n’est pas une pratique de sécurité recommandée pour les scénarios de production. Les applications réelles doivent implémenter des approches plus sécurisées, telles que des restrictions de pare-feu ou des configurations de réseau virtuel plus strictes.

     Vous pouvez en apprendre davantage sur les configurations de sécurité de base de données avec les ressources suivantes :

     • Configurer des règles de pare-feu Azure SQL Database.
     • Configurer un réseau virtuel avec des points de terminaison privés.

2. Le serveur doit également activer l’authentification Microsoft Entra et disposer d’un compte d’administrateur Microsoft Entra affecté. Pour les connexions de développement local, le compte d'administrateur de Microsoft Entra doit être un compte que vous pouvez également connecter localement à Visual Studio ou à Azure CLI. Vous pouvez vérifier si l'authentification Microsoft Entra est activée sur la page Microsoft Entra ID de votre serveur logique.

   

3. Si vous utilisez un compte Azure personnel, assurez-vous d'avoir Microsoft Entra installé et configuré dans la base de données Azure SQL afin d'assigner votre compte en tant qu'administrateur de serveur. En revanche, si vous utilisez un compte d'entreprise, il est fort probable que Microsoft Entra ID soit déjà configuré pour vous.

Créer le projet

Pour les étapes à suivre, créez une API web minimale .NET à l’aide de l’interface CLI .NET ou de Visual Studio 2022.

Visual Studio

1. Dans le menu de Visual Studio, accédez à Fichier>Nouveau>Projet....

2. Dans la fenêtre de boîte de dialogue, entrez ASP.NET dans la zone de recherche du modèle de projet, et sélectionnez le résultat API web ASP.NET Core. Choisissez Suivant en bas de la boîte de dialogue.

3. Pour Nom du projet, entrez DotNetSQL. Conservez les valeurs par défaut pour le reste des champs, puis sélectionnez Suivant.

4. Pour Framework, sélectionnez .NET 7.0 et décochez Utiliser des contrôleurs (décocher pour utiliser un minimum d’API). Ce guide de démarrage rapide utilise un modèle d’API minimale pour simplifier la création et la configuration du point de terminaison.

5. Cliquez sur Créer. Le nouveau projet s’ouvre dans l’environnement Visual Studio.

CLI .NET

1. Dans une fenêtre de console (par exemple cmd, PowerShell ou Bash), utilisez la commande dotnet new pour créer une application d’API web avec le nom DotNetSQL. Cette commande crée un projet C# « Hello World » simple avec un seul fichier source : Program.cs.

   dotnet new web -o DotNetSQL
   

2. Accédez au répertoire DotNetSQL nouvellement créé et ouvrez le projet dans Visual Studio.

Ajouter la bibliothèque Microsoft.Data.SqlClient

Pour vous connecter à Azure SQL Database à l’aide de .NET, installez Microsoft.Data.SqlClient. Ce package joue le rôle de fournisseur de données pour la connexion aux bases de données, l’exécution de commandes et la récupération des résultats.

Notes

Veillez à installer Microsoft.Data.SqlClient et non System.Data.SqlClient. Microsoft.Data.SqlClient est une version plus récente de la bibliothèque cliente SQL qui fournit des fonctionnalités supplémentaires.

Visual Studio

1. Dans la fenêtre Explorateur de solutions, cliquez avec le bouton droit sur le nœud Dépendances du projet, puis sélectionnez Gérer les packages NuGet.

2. Dans la fenêtre résultante, recherchez SqlClient. Recherchez le résultat Microsoft.Data.SqlClient et sélectionnez Installer.

CLI .NET

Utilisez la commande suivante pour installer le package Microsoft.Data.SqlClient :

dotnet add package Microsoft.Data.SqlClient

Configurer la chaîne de connexion

Sans mot de passe (recommandé)

Pour un développement local avec des connexions sans mot de passe à Azure SQL Database, ajoutez la section ConnectionStrings ci-dessous au fichier appsettings.json. Remplacez les espaces réservés <database-server-name> et <database-name> par vos valeurs.

"ConnectionStrings": {
    "AZURE_SQL_CONNECTIONSTRING": "Server=tcp:<database-server-name>.database.windows.net,1433;Initial Catalog=<database-name>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=\"Active Directory Default\";"
}

La chaîne de connexion sans mot de passe définit la valeur de configuration Authentication="Active Directory Default", qui donne instruction à la bibliothèque Microsoft.Data.SqlClient de se connecter à Azure SQL Database en utilisant une classe appelée DefaultAzureCredential. DefaultAzureCredential permet les connexions sans mot de passe aux services Azure et est fourni par la bibliothèque Azure Identity dont dépend la bibliothèque de client SQL. DefaultAzureCredential prend en charge plusieurs méthodes d’authentification et détermine celle qui est utilisée au moment de l’exécution pour différents environnements.

Par exemple, quand l’application s’exécute localement, DefaultAzureCredential s’authentifie via l’utilisateur avec lequel vous êtes connecté dans Visual Studio ou d’autres outils locaux comme Azure CLI. Une fois l’application déployée sur Azure, le même code découvre et applique l’identité managée associée à l’application hébergée, que vous configurerez ultérieurement. La Vue d’ensemble de la bibliothèque d’identités Azure explique l’ordre et les emplacements dans lesquels DefaultAzureCredential recherche les informations d’identification.

Notes

Les chaînes de connexion sans mot de passe peuvent être validées sans risque dans le contrôle de code source, car elles ne contiennent pas de secrets comme des noms d’utilisateur, des mots de passe ou des clés d’accès.

Authentification SQL

Pour un développement local avec une authentification SQL auprès d’Azure SQL Database, ajoutez la section ConnectionStrings ci-dessous au fichier appsettings.json. Remplacez les espaces réservés <database-server-name>, <database-name>, <user-id> et <password> par vos valeurs.

"ConnectionStrings": {
    "AZURE_SQL_CONNECTIONSTRING": "Server=tcp:<database-server-name>.database.windows.net,1433;Initial Catalog=<database-name>;Persist Security Info=False;User ID=<user-id>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;"
}

Avertissement

Soyez prudent quand il est question de gérer des chaînes de connexion contenant des secrets tels que des noms d’utilisateur, des mots de passe ou des clés d’accès. Ces secrets ne doivent pas être validés dans le contrôle de code source ou placés à des emplacements non sécurisés auxquels pourraient accéder des utilisateurs indésirables. Pendant le développement local, sur une application réelle, vous vous connecterez généralement à une base de données locale qui ne demande pas de stocker des secrets ou de se connecter directement à Azure.

Ajouter le code pour se connecter à Azure SQL Database

Remplacez le contenu du fichier Program.cs par le code ci-dessous, qui effectue les étapes importantes suivantes :

• Récupère la chaîne de connexion sans mot de passe dans appsettings.json
• Crée une table Persons dans la base de données au démarrage (pour les scénarios de test uniquement)
• Crée un point de terminaison HTTP GET pour récupérer tous les enregistrements stockés dans la table Persons
• Crée un point de terminaison HTTP POST pour ajouter de nouveaux enregistrements dans la table Persons

using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// For production scenarios, consider keeping Swagger configurations behind the environment check
// if (app.Environment.IsDevelopment())
// {
    app.UseSwagger();
    app.UseSwaggerUI();
// }

app.UseHttpsRedirection();

string connectionString = app.Configuration.GetConnectionString("AZURE_SQL_CONNECTIONSTRING")!;

try
{
    // Table would be created ahead of time in production
    using var conn = new SqlConnection(connectionString);
    conn.Open();

    var command = new SqlCommand(
        "CREATE TABLE Persons (ID int NOT NULL PRIMARY KEY IDENTITY, FirstName varchar(255), LastName varchar(255));",
        conn);
    using SqlDataReader reader = command.ExecuteReader();
}
catch (Exception e)
{
    // Table may already exist
    Console.WriteLine(e.Message);
}

app.MapGet("/Person", () => {
    var rows = new List<string>();

    using var conn = new SqlConnection(connectionString);
    conn.Open();

    var command = new SqlCommand("SELECT * FROM Persons", conn);
    using SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            rows.Add($"{reader.GetInt32(0)}, {reader.GetString(1)}, {reader.GetString(2)}");
        }
    }

    return rows;
})
.WithName("GetPersons")
.WithOpenApi();

app.MapPost("/Person", (Person person) => {
    using var conn = new SqlConnection(connectionString);
    conn.Open();

    var command = new SqlCommand(
        "INSERT INTO Persons (firstName, lastName) VALUES (@firstName, @lastName)",
        conn);

    command.Parameters.Clear();
    command.Parameters.AddWithValue("@firstName", person.FirstName);
    command.Parameters.AddWithValue("@lastName", person.LastName);

    using SqlDataReader reader = command.ExecuteReader();
})
.WithName("CreatePerson")
.WithOpenApi();

app.Run();

Enfin, ajoutez la classe Person au bas du fichier Program.cs. Cette classe représente un enregistrement unique dans la table de la base de données Persons.

public class Person
{
    public required string FirstName { get; set; }
    public required string LastName { get; set; }
}

Exécuter et tester l’application localement

L’application est prête à être testée localement. Vérifiez que vous êtes connecté à Visual Studio ou à Azure CLI avec le même compte que l’administrateur de votre base de données.

1. Appuyez sur le bouton Exécuter en haut de Visual Studio pour lancer le projet d’API.

2. Dans la page de l’interface utilisateur Swagger, développez la méthode POST, puis sélectionnez Essayer.

3. Modifiez l’exemple JSON pour inclure des valeurs pour le prénom et le nom. Sélectionnez Exécuter pour ajouter un nouvel enregistrement à la base de données. L’API retourne une réponse de succès.

   

4. Développez la méthode GET sur la page de l’interface utilisateur Swagger, puis sélectionnez Essayer. Choisissez Exécuter, et la personne que vous venez de créer est retournée.

Déployer dans Azure App Service

L’application est prête à être déployée sur Azure. Visual Studio peut créer une instance Azure App Service et déployer votre application au cours d’un même workflow.

1. Vérifiez que l’application est arrêtée et générée correctement.

2. Dans la fenêtre Explorateur de solutions de Visual Studio, cliquez avec le bouton droit sur le nœud de projet de niveau supérieur, puis sélectionnez Publier.

3. Dans la boîte de dialogue de publication, sélectionnez Azure comme cible de déploiement, puis sélectionnez Suivant.

4. Pour la cible spécifique, sélectionnez Azure App Service (Windows), puis Suivant.

5. Sélectionnez l’icône + pour créer une instance App Service sur laquelle effectuer le déploiement, puis entrez les valeurs suivantes :

   • Nom : conservez la valeur par défaut.

   • Nom de l’abonnement : sélectionnez l’abonnement à déployer.

   • Groupe de ressources : sélectionnez Nouveau et créez un nouveau groupe de ressources appelé msdocs-dotnet-sql.

   • Plan d’hébergement : sélectionnez Nouveau pour ouvrir la boîte de dialogue Plan d’hébergement. Laissez les valeurs par défaut et sélectionnez OK.

   • Sélectionnez Créer pour fermer la boîte de dialogue d’origine. Visual Studio crée la ressource App Service dans Azure.

     

6. Une fois la ressource créée, vérifiez qu’elle est sélectionnée dans la liste des services d’application, puis sélectionnez Suivant.

7. Dans l’étape Gestion des API, cochez la case Ignorer cette étape en bas, puis choisissez Terminer.

8. À l’étape Terminer, sélectionnez Fermer si la boîte de dialogue ne se ferme pas automatiquement.

9. Sélectionnez Publier en haut à droite du résumé du profil de publication pour déployer l’application sur Azure.

Une fois le déploiement terminé, Visual Studio lance le navigateur pour afficher l’application hébergée, mais à ce stade, l’application ne fonctionne pas correctement sur Azure. Vous devez toujours configurer la connexion sécurisée entre App Service et la base de données SQL pour récupérer vos données.

Connecter App Service à Azure SQL Database

Sans mot de passe (recommandé)

Les étapes suivantes sont nécessaires pour créer une connexion sans mot de passe entre l’instance App Service et Azure SQL Database :

1. Créez une identité managée pour App Service. La bibliothèque Microsoft.Data.SqlClient incluse dans votre application découvre automatiquement l’identité managée, tout comme elle a découvert votre utilisateur Visual Studio local.
2. Créez un utilisateur de base de données SQL et associez-le à l’identité managée App Service.
3. Attribuez des rôles SQL à l’utilisateur de base de données qui octroient des autorisations de lecture, d’écriture et éventuellement d’autres autorisations.

Plusieurs outils sont disponibles pour implémenter ces étapes :

Service Connector (recommandé)

Service Connector est un outil qui simplifie les connexions authentifiées entre différents services dans Azure. Service Connector prend actuellement en charge la connexion d’App Service à une base de données SQL via Azure CLI à l’aide de la commande az webapp connection create sql. Cette commande unique effectue les trois étapes mentionnées ci-dessus pour vous.

az webapp connection create sql \
    -g <app-service-resource-group> \
    -n <app-service-name> \
    --tg <database-server-resource-group> \
    --server <database-server-name> \
    --database <database-name> \
    --system-identity

Vous pouvez vérifier les modifications apportées par Service Connector sur les paramètres App Service.

1. Accédez à la page Identité de votre App Service. Sous l’onglet Affecté par le système , l’État doit être défini sur Activé. Cette valeur signifie qu’une identité managée affectée par le système a été activée pour votre application.

2. Accédez à la page Configuration de votre App Service. Sous l’onglet Chaînes de connexion, vous devriez voir une chaîne de connexion appelée AZURE_SQL_CONNECTIONSTRING. Sélectionnez le texte Cliquer pour afficher la valeur pour afficher la chaîne de connexion sans mot de passe générée. Le nom de cette chaîne de connexion correspondant à celui que vous avez configuré dans votre application, il sera détecté automatiquement pendant l’exécution dans Azure.

Azure portal

Le Portail Azure vous permet d’utiliser des identités managées et d’exécuter des requêtes sur Azure SQL Database. Effectuez les étapes suivantes pour créer une connexion sans mot de passe à partir de votre instance App Service vers Azure SQL Database :

Créer l’identité managée

1. Dans le Portail Azure, accédez à votre App Service et sélectionnez Identité dans le volet de navigation de gauche.

2. Sous l’onglet Affecté(e) par le système de la page Identité, vérifiez que le bouton bascule État est défini sur Activé. Lorsque ce paramètre est activé, une identité managée affectée par le système est créée avec le même nom que votre App Service. Les identités affectées par le système sont liées à l’instance de service et sont détruites avec l’application lorsqu’elle est supprimée.

Créer l’utilisateur de base de données et attribuer des rôles

1. Dans le Portail Azure, accédez à votre base de données SQL et sélectionnez Éditeur de requête (préversion).

2. Sélectionnez Continuer en tant que <your-username> sur le côté droit de l’écran pour vous connecter à la base de données à l’aide de votre compte.

3. Dans la vue de l’éditeur de requête, exécutez les commandes T-SQL suivantes :

   CREATE USER <your-app-service-name> FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER <your-app-service-name>;
   ALTER ROLE db_datawriter ADD MEMBER <your-app-service-name>;
   ALTER ROLE db_ddladmin ADD MEMBER <your-app-service-name>;
   GO
   

   

   Ce script SQL crée un utilisateur de base de données SQL qui est mappé à l’identité managée de votre instance App Service. Il attribue également les rôles SQL nécessaires à l’utilisateur pour permettre à votre application de lire, d’écrire et de modifier les données et le schéma de votre base de données. Une fois cette étape terminée, vos services sont connectés.

Important

Bien que cette solution offre une approche simple pour se lancer, il ne s’agit pas d’une bonne pratique pour les environnements de niveau production. Dans ces scénarios, l’application ne doit pas effectuer toutes les opérations en utilisant une même identité avec privilèges élevés. Vous devez essayer d’implémenter le principe de privilège minimum en configurant plusieurs identités avec des autorisations spécifiques pour des tâches spécifiques.

Vous pouvez en apprendre plus sur la configuration des rôles de base de données et la sécurité avec les ressources suivantes :

• Tutoriel : Sécuriser une base de données dans Azure SQL Database
• Autoriser l’accès base de données à SQL Database

Authentification SQL

Aucune étape supplémentaire n’est nécessaire pour connecter l’instance App Service à Azure SQL Database en utilisant l’authentification SQL. La chaîne de connexion que vous avez configurée dans le fichier appsettings.json comporte les informations d’identification nécessaires à l’authentification.

Avertissement

Soyez prudent quand il est question de gérer des chaînes de connexion contenant des secrets tels que des noms d’utilisateur, des mots de passe ou des clés d’accès. Ces secrets ne doivent pas être validés dans le contrôle de code source ou placés à des emplacements non sécurisés auxquels pourraient accéder des utilisateurs indésirables. Pour une application réelle dans un environnement Azure de niveau production, vous pouvez stocker les chaînes de connexion à un emplacement sécurisé tel que les paramètres de configuration App Service ou Azure Key Vault. Pendant le développement local, vous vous connecterez généralement à une base de données locale qui ne demande pas de stocker des secrets ou de se connecter directement à Azure.

Tester l’application déployée

1. Sélectionnez le bouton Parcourir en haut de la page de présentation d’App Service pour lancer l’URL racine de votre application.

2. Ajoutez le chemin /swagger/index.html de l’URL pour charger la même page de test Swagger que celle utilisée localement.

3. Exécutez des requêtes de test GET et POST pour vérifier que les points de terminaison fonctionnent comme prévu.

Conseil

Si vous obtenez une erreur Serveur interne 500 pendant le test, ce sont peut-être vos configurations réseau de base de données qui sont en cause. Vérifiez que votre serveur logique est configuré avec les paramètres décrits dans la section Configurer la base de données.

Félicitations ! Votre application est maintenant connectée à Azure SQL Database dans les environnements locaux et hébergés.

Nettoyer les ressources

Une fois que vous avez terminé d’utiliser Azure SQL Database, supprimez la ressource pour éviter les coûts imprévus.

Azure portal

1. Dans la barre de recherche du portail Azure, recherchez Azure SQL et sélectionnez le résultat correspondant.

2. Recherchez et sélectionnez votre base de données dans la liste.

3. Dans la page Vue d’ensemble d’Azure SQL Database, sélectionnez Supprimer.

4. Dans la page Voulez-vous vraiment supprimer... qui s’ouvre, tapez le nom de la base de données pour confirmer, puis sélectionnez Supprimer.

Azure CLI

Supprimez votre base de données à l’aide de la commande az sql db delete. Remplacez les paramètres d’espace réservé par vos propres valeurs.

az sql db delete --name <database-name> --resource-group <resource-group-name> --server <logical-server-name>