Koneksi ke dan mengkueri Azure SQL Database menggunakan .NET dan pustaka Microsoft.Data.SqlClient

Berlaku untuk:Azure SQL Database

Mulai cepat ini menjelaskan cara menyambungkan aplikasi ke database di Azure SQL Database dan melakukan kueri menggunakan .NET dan pustaka Microsoft.Data.SqlClient . Mulai cepat ini mengikuti pendekatan tanpa kata sandi yang direkomendasikan untuk menyambungkan ke database. Anda dapat mempelajari lebih lanjut tentang koneksi tanpa kata sandi di hub tanpa kata sandi.

Prasyarat

• Langganan Azure.
• Database Azure SQL yang dikonfigurasi untuk autentikasi dengan ID Microsoft Entra (sebelumnya Azure Active Directory). Anda bisa membuatnya menggunakan mulai cepat Buat database.
• Versi terbaru dari Azure CLI.
• Visual Studio atau yang lebih baru dengan beban kerja ASP.NET dan pengembangan web.
• .NET 7.0 atau yang lebih baru.

Mengonfigurasi database

Koneksi aman tanpa kata sandi ke Azure SQL Database memerlukan konfigurasi database tertentu. Verifikasi pengaturan berikut di server logis Anda di Azure untuk menyambungkan dengan benar ke Azure SQL Database di lingkungan lokal dan yang dihosting:

1. Untuk koneksi pengembangan lokal, pastikan server logis Anda dikonfigurasi untuk memungkinkan alamat IP komputer lokal Anda dan layanan Azure lainnya tersambung:

   • Navigasi ke halaman Jaringan server Anda.

   • Alihkan tombol radio Jaringan terpilih untuk menampilkan opsi konfigurasi tambahan.

   • Pilih Tambahkan alamat IPv4 klien Anda(xx.xx.xx.xx) untuk menambahkan aturan firewall yang akan mengaktifkan koneksi dari alamat IPv4 komputer lokal Anda. Atau, Anda juga dapat memilih + Tambahkan aturan firewall untuk memasukkan alamat IP tertentu pilihan Anda.

   • Pastikan kotak centang Izinkan layanan dan sumber daya Azure untuk mengakses server ini dipilih.

     

     Peringatan

     Mengaktifkan pengaturan Izinkan layanan dan sumber daya Azure untuk mengakses server ini bukanlah praktik keamanan yang direkomendasikan untuk skenario produksi. Aplikasi nyata harus menerapkan pendekatan yang lebih aman, seperti pembatasan firewall yang lebih kuat atau konfigurasi jaringan virtual.

     Anda bisa membaca selengkapnya tentang konfigurasi keamanan database pada sumber daya berikut:

     • Mengonfigurasi aturan firewall Azure SQL Database.
     • Konfigurasikan jaringan virtual dengan titik akhir privat.

2. Server juga harus mengaktifkan autentikasi Microsoft Entra dan memiliki akun admin Microsoft Entra yang ditetapkan. Untuk koneksi pengembangan lokal, akun admin Microsoft Entra harus menjadi akun yang juga dapat Anda masuk ke Visual Studio atau Azure CLI dengan secara lokal. Anda dapat memverifikasi apakah server Anda mengaktifkan autentikasi Microsoft Entra di halaman ID Microsoft Entra server logis Anda.

   

3. Jika Anda menggunakan akun Azure pribadi, pastikan Anda memiliki penyiapan Microsoft Entra dan dikonfigurasi untuk Azure SQL Database untuk menetapkan akun Anda sebagai admin server. Jika Anda menggunakan akun perusahaan, ID Microsoft Entra kemungkinan besar sudah dikonfigurasi untuk Anda.

Membuat proyek

Untuk langkah-langkah ke depan, buat .NET Minimal Web API menggunakan .NET CLI atau Visual Studio 2022.

Visual Studio

1. Di menu Visual Studio, navigasikan ke File>Proyek Baru>...

2. Di jendela dialog, masukkan ASP.NET ke dalam kotak pencarian templat proyek dan pilih hasil ASP.NET Core Web API. Pilih Berikutnya di bagian bawah dialog.

3. Untuk Nama Proyek, masukkan DotNetSQL. Biarkan nilai default di bidang lain dan pilih Berikutnya.

4. Untuk Kerangka Kerja, pilih .NET 7.0 dan hapus centang Gunakan pengontrol (hapus centang untuk menggunakan API minimal). Mulai cepat ini menggunakan templat API Minimal untuk menyederhanakan pembuatan dan konfigurasi titik akhir.

5. Pilih Buat. Proyek baru terbuka di dalam lingkungan Visual Studio.

.NET CLI

1. Di jendela konsol (seperti cmd, PowerShell, atau Bash), gunakan dotnet new perintah untuk membuat aplikasi API Web baru dengan nama DotNetSQL. Perintah ini membuat proyek C# "Halo Dunia" sederhana dengan file sumber tunggal: Program.cs.

   dotnet new web -o DotNetSQL
   

2. Navigasikan ke direktori DotNetSQL yang baru dibuat dan buka proyek di Visual Studio.

Menambahkan pustaka Microsoft.Data.SqlClient

Untuk menyambungkan ke Azure SQL Database dengan menggunakan .NET, instal Microsoft.Data.SqlClient. Paket ini bertindak sebagai penyedia data untuk menyambungkan ke database, menjalankan perintah, dan mengambil hasil.

Catatan

Pastikan untuk menginstal Microsoft.Data.SqlClient dan bukan System.Data.SqlClient. Microsoft.Data.SqlClient adalah versi pustaka klien SQL yang lebih baru yang menyediakan kemampuan tambahan.

Visual Studio

1. Di jendela Penjelajah Solusi, klik kanan simpul Dependensi proyek dan pilih Kelola Paket NuGet.

2. Di jendela yang dihasilkan, cari SqlClient. Temukan hasilnya Microsoft.Data.SqlClient dan pilih Instal.

.NET CLI

Gunakan perintah berikut untuk menginstal Microsoft.Data.SqlClient paket:

dotnet add package Microsoft.Data.SqlClient

Mengonfigurasi string koneksi

Tanpa Kata Sandi (Disarankan)

Untuk pengembangan lokal dengan koneksi tanpa kata sandi ke Azure SQL Database, tambahkan bagian berikut ConnectionStrings ke appsettings.json file. <database-server-name> Ganti tempat penampung dan <database-name> dengan nilai Anda sendiri.

"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\";"
}

string koneksi tanpa kata sandi menetapkan nilai Authentication="Active Directory Default"konfigurasi , yang menginstruksikan Microsoft.Data.SqlClient pustaka untuk terhubung ke Azure SQL Database menggunakan kelas yang disebut DefaultAzureCredential. DefaultAzureCredential mengaktifkan koneksi tanpa kata sandi ke layanan Azure dan disediakan oleh pustaka Identitas Azure tempat pustaka klien SQL bergantung. DefaultAzureCredential mendukung beberapa metode autentikasi dan menentukan mana yang akan digunakan pada runtime untuk lingkungan yang berbeda.

Misalnya, saat aplikasi berjalan secara lokal, DefaultAzureCredential mengautentikasi melalui pengguna yang Anda gunakan untuk masuk ke Visual Studio, atau alat lokal lainnya seperti Azure CLI. Setelah aplikasi disebarkan ke Azure, kode yang sama menemukan dan menerapkan identitas terkelola yang terkait dengan aplikasi yang dihosting, yang akan Anda konfigurasi nanti. Gambaran umum pustaka Azure Identity menjelaskan urutan dan lokasi di mana DefaultAzureCredential mencari kredensial.

Catatan

String koneksi tanpa kata sandi aman untuk berkomitmen pada kontrol sumber, karena tidak berisi rahasia seperti nama pengguna, kata sandi, atau kunci akses.

Autentikasi SQL

Untuk pengembangan lokal dengan Autentikasi SQL ke Azure SQL Database, tambahkan bagian berikut ConnectionStrings ke appsettings.json file. <database-server-name>Ganti tempat penampung , <database-name>, <user-id>, dan <password> dengan nilai Anda sendiri.

"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;"
}

Peringatan

Berhati-hatilah saat mengelola string koneksi yang berisi rahasia seperti nama pengguna, kata sandi, atau kunci akses. Rahasia ini tidak boleh berkomitmen untuk kontrol sumber atau ditempatkan di lokasi yang tidak aman di mana mereka mungkin diakses oleh pengguna yang tidak diinginkan. Selama pengembangan lokal, pada aplikasi nyata, Anda umumnya akan terhubung ke database lokal yang tidak memerlukan penyimpanan rahasia atau menyambungkan langsung ke Azure.

Menambahkan kode untuk menyambungkan ke Azure SQL Database

Ganti konten Program.cs file dengan kode berikut, yang melakukan langkah-langkah penting berikut:

• Mengambil string koneksi tanpa kata sandi dariappsettings.json
• Persons Membuat tabel dalam database selama startup (hanya untuk skenario pengujian)
• Membuat titik akhir HTTP GET untuk mengambil semua rekaman yang Persons disimpan dalam tabel
• Membuat titik akhir HTTP POST untuk menambahkan rekaman baru ke Persons tabel

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();

Terakhir, tambahkan Person kelas ke bagian Program.cs bawah file. Kelas ini mewakili satu rekaman dalam tabel database Persons .

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

Jalankan dan uji aplikasi secara lokal

Aplikasi ini siap untuk diuji secara lokal. Pastikan Anda masuk ke Visual Studio atau Azure CLI dengan akun yang sama dengan yang Anda tetapkan sebagai admin untuk database Anda.

1. Tekan tombol jalankan di bagian atas Visual Studio untuk meluncurkan proyek API.

2. Pada halaman UI Swagger, perluas metode POST dan pilih Coba.

3. Ubah contoh JSON untuk menyertakan nilai untuk nama depan dan belakang. Pilih Jalankan untuk menambahkan rekaman baru ke database. API mengembalikan respons yang berhasil.

   

4. Perluas metode GET pada halaman UI Swagger dan pilih Coba. Pilih Jalankan, dan orang yang baru saja Anda buat dikembalikan.

Menyebarkan ke Azure App Service

Aplikasi ini siap disebarkan ke Azure. Visual Studio dapat membuat Azure App Service dan menyebarkan aplikasi Anda dalam satu alur kerja.

1. Pastikan aplikasi dihentikan dan berhasil dibuat.

2. Di jendela Penjelajah Solusi Visual Studio, klik kanan pada simpul proyek tingkat atas dan pilih Terbitkan.

3. Dalam dialog penerbitan, pilih Azure sebagai target penyebaran, lalu pilih Berikutnya.

4. Untuk target tertentu, pilih Azure App Service (Windows), lalu pilih Berikutnya.

5. + Pilih ikon untuk membuat App Service baru untuk disebarkan dan masukkan nilai berikut:

   • Nama: Biarkan nilai default.

   • Nama langganan: Pilih langganan yang akan disebarkan.

   • Grup sumber daya: Pilih Baru dan buat grup sumber daya baru yang disebut msdocs-dotnet-sql.

   • Paket Hosting: Pilih Baru untuk membuka dialog paket hosting. Biarkan nilai default dan pilih OK.

   • Pilih Buat untuk menutup dialog asli. Visual Studio membuat sumber daya App Service di Azure.

     

6. Setelah sumber daya dibuat, pastikan sumber daya dipilih dalam daftar layanan aplikasi, lalu pilih Berikutnya.

7. Pada langkah API Management, pilih kotak centang Lewati langkah ini di bagian bawah lalu pilih Selesai.

8. Pada langkah Selesai, pilih Tutup jika dialog tidak ditutup secara otomatis.

9. Pilih Terbitkan di kanan atas ringkasan profil penerbitan untuk menyebarkan aplikasi ke Azure.

Setelah penyebaran selesai, Visual Studio meluncurkan browser untuk menampilkan aplikasi yang dihosting, tetapi pada titik ini aplikasi tidak berfungsi dengan benar di Azure. Anda masih perlu mengonfigurasi koneksi aman antara App Service dan database SQL untuk mengambil data Anda.

Koneksi App Service ke Azure SQL Database

Tanpa Kata Sandi (Disarankan)

Langkah-langkah berikut diperlukan untuk membuat koneksi tanpa kata sandi antara instans App Service dan Azure SQL Database:

1. Buat identitas terkelola untuk App Service. Pustaka Microsoft.Data.SqlClient yang disertakan dalam aplikasi Anda akan secara otomatis menemukan identitas terkelola, sama seperti menemukan pengguna Visual Studio lokal Anda.
2. Buat pengguna database SQL dan kaitkan dengan identitas terkelola App Service.
3. Tetapkan peran SQL ke pengguna database yang memungkinkan izin baca, tulis, dan berpotensi lainnya.

Ada beberapa alat yang tersedia untuk menerapkan langkah-langkah ini:

Service Koneksi or (Disarankan)

Service Koneksi or adalah alat yang menyederhanakan koneksi terautentikasi antara berbagai layanan di Azure. Service Koneksi or saat ini mendukung menyambungkan App Service ke database SQL melalui Azure CLI menggunakan az webapp connection create sql perintah . Perintah tunggal ini menyelesaikan tiga langkah yang disebutkan di atas untuk Anda.

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

Anda dapat memverifikasi perubahan yang dibuat oleh Koneksi Layanan pada pengaturan App Service.

1. Navigasi ke halaman Identitas untuk App Service Anda. Di bawah tab Sistem yang ditetapkan, Status harus diatur ke Aktif. Nilai ini berarti bahwa identitas terkelola yang ditetapkan sistem diaktifkan untuk aplikasi Anda.

2. Navigasi ke halaman Konfigurasi untuk App Service Anda. Di bawah tab string Koneksi ion, Anda akan melihat string koneksi yang disebut AZURE_SQL_CONNECTIONSTRING. Pilih Klik untuk menampilkan teks nilai untuk melihat string koneksi tanpa kata sandi yang dihasilkan. Nama string koneksi ini cocok dengan yang Anda konfigurasi di aplikasi Anda, sehingga akan ditemukan secara otomatis saat berjalan di Azure.

Portal Azure

portal Azure memungkinkan Anda bekerja dengan identitas terkelola dan menjalankan kueri terhadap Azure SQL Database. Selesaikan langkah-langkah berikut untuk membuat koneksi tanpa kata sandi dari instans App Service Anda ke Azure SQL Database:

Buat identitas terkelola

1. Di portal Azure, navigasikan ke App Service Anda dan pilih Identitas di navigasi kiri.

2. Pada tab Sistem yang ditetapkan halaman Identitas, pastikan tombol Status diatur ke Aktif. Saat pengaturan ini diaktifkan, identitas terkelola yang ditetapkan sistem dibuat dengan nama yang sama dengan App Service Anda. Identitas yang ditetapkan sistem terkait dengan instans layanan dan dihancurkan dengan aplikasi saat dihapus.

Membuat pengguna database dan menetapkan peran

1. Di portal Azure, telusuri ke database SQL Anda dan pilih Editor kueri (pratinjau).

2. Pilih Lanjutkan seperti <your-username> di sisi kanan layar untuk masuk ke database menggunakan akun Anda.

3. Pada tampilan editor kueri, jalankan perintah T-SQL berikut ini:

   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
   

   

   Skrip SQL ini membuat pengguna database SQL yang memetakan kembali ke identitas terkelola instans App Service Anda. Ini juga menetapkan peran SQL yang diperlukan kepada pengguna untuk memungkinkan aplikasi Anda membaca, menulis, dan memodifikasi data dan skema database Anda. Setelah langkah ini selesai, layanan Anda tersambung.

Penting

Meskipun solusi ini menyediakan pendekatan sederhana untuk memulai, ini bukan praktik terbaik untuk lingkungan tingkat produksi. Dalam skenario tersebut, aplikasi tidak boleh melakukan semua operasi menggunakan satu identitas yang ditingkatkan. Anda harus mencoba menerapkan prinsip hak istimewa paling sedikit dengan mengonfigurasi beberapa identitas dengan izin tertentu untuk tugas tertentu.

Anda dapat membaca selengkapnya tentang mengonfigurasi peran database dan keamanan pada sumber daya berikut:

• Tutorial: Mengamankan database pada Azure SQL Database
• Mengotorisasi akses database ke SQL Database

Autentikasi SQL

Tidak ada langkah tambahan yang diperlukan untuk menyambungkan App Service ke Azure SQL Database menggunakan Autentikasi SQL. string koneksi yang Anda konfigurasi dalam appsettings.json file menyertakan kredensial yang diperlukan untuk mengautentikasi.

Peringatan

Berhati-hatilah saat mengelola string koneksi yang berisi rahasia seperti nama pengguna, kata sandi, atau kunci akses. Rahasia ini tidak boleh berkomitmen untuk kontrol sumber atau ditempatkan di lokasi yang tidak aman di mana mereka mungkin diakses oleh pengguna yang tidak diinginkan. Untuk aplikasi nyata di lingkungan Azure tingkat produksi, Anda dapat menyimpan string koneksi di lokasi yang aman seperti pengaturan konfigurasi App Service atau Azure Key Vault. Selama pengembangan lokal, Anda umumnya akan tersambung ke database lokal yang tidak memerlukan penyimpanan rahasia atau menyambungkan langsung ke Azure.

Menguji aplikasi yang disebarkan

1. Pilih tombol Telusuri di bagian atas halaman ringkasan App Service untuk meluncurkan url akar aplikasi Anda.

2. Tambahkan /swagger/index.html jalur ke URL untuk memuat halaman pengujian Swagger yang sama dengan yang Anda gunakan secara lokal.

3. Jalankan permintaan UJI GET dan POST untuk memverifikasi bahwa titik akhir berfungsi seperti yang diharapkan.

Tip

Jika Anda menerima kesalahan 500 Server Internal saat pengujian, itu mungkin karena konfigurasi jaringan database Anda. Verifikasi bahwa server logis Anda dikonfigurasi dengan pengaturan yang diuraikan di bagian Konfigurasikan database .

Selamat! Aplikasi Anda sekarang terhubung ke Azure SQL Database di lingkungan lokal dan yang dihosting.

Membersihkan sumber daya

Setelah selesai bekerja dengan Azure SQL Database, hapus sumber daya untuk menghindari biaya yang tidak diinginkan.

Portal Azure

1. Di bilah pencarian portal Azure, cari Azure SQL dan pilih hasil yang cocok.

2. Temukan dan pilih database Anda dalam daftar database.

3. Pada halaman Gambaran Umum Azure SQL Database Anda, pilih Hapus.

4. Pada halaman Azure yang Anda yakin ingin hapus... yang terbuka, ketik nama database Anda untuk mengonfirmasi, lalu pilih Hapus.

Azure CLI

Hapus database Anda dengan menggunakan az sql db delete perintah . Ganti parameter tempat penampung dengan nilai Anda sendiri.

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