Bagikan melalui

Kolom kueri menggunakan Always Encrypted dengan Azure Data Studio

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Artikel ini menjelaskan cara mengkueri kolom, dienkripsi dengan Always Encrypted menggunakan Azure Data Studio. Dengan Azure Data Studio, Anda dapat:

  • Ambil nilai ciphertext yang disimpan dalam kolom terenkripsi.
  • Ambil nilai teks biasa yang disimpan dalam kolom terenkripsi.
  • Kirim nilai teks biasa yang menargetkan kolom terenkripsi (misalnya, dalam INSERT pernyataan atau UPDATE dan sebagai parameter pencarian klausa WHERE dalam SELECT pernyataan).

Mengambil nilai ciphertext yang disimpan dalam kolom terenkripsi

Bagian ini menjelaskan cara mengambil data yang disimpan dalam kolom terenkripsi sebagai ciphertext.

Langkah-langkah

  1. Pastikan Anda telah menonaktifkan Always Encrypted untuk koneksi database untuk jendela kueri, tempat Anda akan menjalankan kueri yang SELECT mengambil nilai ciphertext. Lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database di bawah ini.
  2. Jalankan kueri Anda SELECT . Setiap data yang diambil dari kolom terenkripsi akan dikembalikan sebagai nilai biner (terenkripsi).

Contoh

Dengan SSN asumsi adalah kolom terenkripsi dalam Patients tabel, kueri yang diperlihatkan di bawah ini akan mengambil nilai ciphertext biner, jika Always Encrypted dinonaktifkan untuk koneksi database.

Cuplikan layar SELECT * FROM [dbo]. Kueri [Pasien] dan hasil kueri yang ditampilkan sebagai nilai ciphertext biner.

Mengambil nilai teks biasa yang disimpan dalam kolom terenkripsi

Bagian ini menjelaskan cara mengambil data yang disimpan dalam kolom terenkripsi sebagai ciphertext.

Prasyarat

  • Azure Data Studio versi 17.1 atau yang lebih baru.
  • Anda harus memiliki akses ke kunci master kolom dan metadata tentang kunci yang melindungi kolom yang Anda jalankan kuerinya. Untuk detailnya, lihat Izin untuk mengkueri kolom terenkripsi di bawah ini.
  • Kunci master kolom Anda harus disimpan dalam brankas kunci di Azure Key Vault atau Windows Certificate Store. Azure Data Studio tidak mendukung penyimpanan kunci lain dan tidak mendukung kunci master kolom yang disimpan di HSM terkelola di Azure Key Vault.

Langkah-langkah

  1. Aktifkan Always Encrypted untuk koneksi database untuk jendela kueri, tempat Anda akan menjalankan kueri yang SELECT mengambil dan mendekripsi data Anda. Ini akan menginstruksikan Penyedia Data Microsoft .NET untuk SQL Server (digunakan oleh Azure Data Studio) untuk mendekripsi kolom terenkripsi dalam kumpulan hasil kueri. Lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database di bawah ini.
  2. Jalankan kueri Anda SELECT . Setiap data yang diambil dari kolom terenkripsi akan dikembalikan sebagai nilai teks biasa dari jenis data asli.

Contoh

Dengan asumsi SSN adalah kolom terenkripsi dalam tabel, kueri yang ditunjukkan Patients di bawah ini akan mengembalikan nilai teks biasa jika Always Encrypted diaktifkan untuk koneksi database, dan jika Anda memiliki akses ke kunci master kolom yang dikonfigurasi untuk kolom.SSN

Cuplikan layar SELECT * FROM [dbo]. Kueri [Pasien] dan hasil kueri yang ditampilkan sebagai nilai teks biasa.

Mengirim nilai teks biasa yang menargetkan kolom terenkripsi

Bagian ini menjelaskan cara menjalankan kueri yang mengirim nilai yang menargetkan kolom terenkripsi. Misalnya, kueri yang menyisipkan, memperbarui, atau memfilter menurut nilai yang disimpan dalam kolom terenkripsi:

Prasyarat

  • Azure Data Studio versi 18.1 atau yang lebih baru.
  • Anda harus memiliki akses ke kunci master kolom dan metadata tentang kunci yang melindungi kolom yang Anda jalankan kuerinya. Untuk detailnya, lihat Izin untuk mengkueri kolom terenkripsi di bawah ini.
  • Kunci master kolom Anda harus disimpan dalam brankas kunci di Azure Key Vault atau Windows Certificate Store. Azure Data Studio tidak mendukung penyimpanan kunci lain dan tidak mendukung kunci master kolom yang disimpan di HSM terkelola di Azure Key Vault.

Langkah-langkah

  1. Aktifkan Always Encrypted untuk koneksi database untuk jendela kueri, tempat Anda akan menjalankan kueri yang SELECT mengambil dan mendekripsi data Anda. Ini akan menginstruksikan Penyedia Data Microsoft .NET untuk SQL Server (digunakan oleh Azure Data Studio) untuk mengenkripsi parameter kueri yang menargetkan kolom terenkripsi dan mendekripsi hasil yang diambil dari kolom terenkripsi. Lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database di bawah ini.
  2. Aktifkan Parameterisasi untuk Always Encrypted untuk jendela kueri. Lihat Parameterisasi untuk Always Encrypted di bawah ini untuk detailnya.
  3. Deklarasikan variabel Transact-SQL dan inisialisasi dengan nilai yang ingin Anda kirim (sisipkan, perbarui, atau filter menurut) ke database.
  4. Jalankan kueri Anda yang mengirim nilai variabel Transact-SQL ke database. Azure Data Studio akan mengonversi variabel ke parameter kueri dan akan mengenkripsi nilainya sebelum mengirimkannya ke database.

Contoh

Dengan asumsi SSN adalah kolom terenkripsi char(11) dalam Patients tabel, skrip di bawah ini akan mencoba menemukan baris yang berisi '795-73-9838' di kolom SSN. Hasilnya dikembalikan jika Always Encrypted diaktifkan untuk koneksi database, Parameterisasi untuk Always Encrypted diaktifkan untuk jendela kueri, dan Anda memiliki akses ke kunci master kolom yang dikonfigurasi untuk kolom.SSN

Cuplikan layar kelas DECLARE <span=@SSN karakter(11) = '795-73-9838' SELECT * FROM [dbo].[ Pasien] WHERE [SSN] = @SSN kueri dan hasil kueri." />

Izin untuk mengkueri kolom terenkripsi

Untuk menjalankan kueri apa pun terhadap kolom terenkripsi, termasuk kueri yang mengambil data dalam ciphertext, Anda memerlukan izin LIHAT DEFINISI KUNCI MASTER KOLOM APA PUN dan LIHAT DEFINISI KUNCI ENKRIPSI KOLOM APA PUN dalam database.

Selain izin di atas, untuk mendekripsi hasil kueri apa pun atau untuk mengenkripsi parameter kueri apa pun (diproduksi dengan membuat parameter variabel Transact-SQL), Anda juga memerlukan izin penyimpanan kunci untuk mengakses dan menggunakan kunci master kolom yang melindungi kolom target. Untuk informasi terperinci tentang izin penyimpanan kunci, buka Membuat dan menyimpan kunci master kolom untuk Always Encrypted dan temukan bagian yang relevan untuk penyimpanan kunci Anda.

Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database

Saat menyambungkan ke database di Azure Data Studio, Anda dapat mengaktifkan atau menonaktifkan Always Encrypted untuk koneksi database. Secara default, Always Encrypted dinonaktifkan.

Mengaktifkan Always Encrypted untuk koneksi database menginstruksikan Penyedia Data .NET Microsoft untuk SQL Server, yang digunakan oleh Azure Data Studio, untuk mencoba secara transparan:

  • Dekripsi nilai apa pun yang diambil dari kolom terenkripsi dan dikembalikan dalam hasil kueri.
  • Enkripsi nilai variabel Transact-SQL berparameter yang menargetkan kolom database terenkripsi.

Jika Anda tidak mengaktifkan Always Encrypted untuk koneksi, Penyedia Data .NET Microsoft untuk SQL Server tidak akan mencoba mengenkripsi parameter kueri atau mendekripsi hasil.

Anda bisa mengaktifkan atau menonaktifkan Always Encrypted saat menyambungkan ke database. Untuk informasi umum tentang cara menyambungkan ke database, lihat:

Untuk mengaktifkan (menonaktifkan) Always Encrypted:

  1. Dalam dialog Koneksi, klik Tingkat Lanjut....
  2. Untuk mengaktifkan Always Encrypted untuk koneksi, atur bidang Always Encrypted ke Diaktifkan. Untuk menonaktifkan Always Encrypted, biarkan nilai bidang Always Encrypted kosong atau atur ke Dinonaktifkan.
  3. Klik OK untuk menutup Properti Tingkat Lanjut.

Video singkat memperlihatkan langkah-langkah untuk mengaktifkan Always Encrypted untuk koneksi.

Untuk menjalankan pernyataan yang memanfaatkan enklave aman sisi server saat Anda menggunakan Always Encrypted dengan enklave aman, Anda perlu menentukan protokol pengesahan enklave dan URL pengesahan enklave, selain mengaktifkan Always Encrypted untuk koneksi. Untuk informasi selengkapnya, lihat Prasyarat untuk menjalankan pernyataan T-SQL menggunakan enklave di Azure Data Studio.

Tip

Untuk beralih antara Always Encrypted diaktifkan dan dinonaktifkan untuk jendela kueri yang sudah ada, klik Putuskan sambungan lalu klik Connnect dan selesaikan langkah-langkah di atas untuk menyambungkan kembali ke database Anda dengan nilai yang diinginkan dari bidang Always Encrypted.

Catatan

Tombol Ubah Koneksi di jendela kueri saat ini tidak mendukung pengalihan antara Always Encrypted yang diaktifkan dan dinonaktifkan.

Parameterisasi untuk Always Encrypted

Parameterisasi untuk Always Encrypted adalah fitur di Azure Data Studio 18.1 dan yang lebih baru yang secara otomatis mengonversi variabel Transact-SQL menjadi parameter kueri (instans Kelas SqlParameter). Ini memungkinkan Penyedia Data Microsoft .NET yang mendasar untuk SQL Server mendeteksi data yang menargetkan kolom terenkripsi, dan untuk mengenkripsi data tersebut sebelum mengirimkannya ke database.

Tanpa parameterisasi, Penyedia Data Microsoft .NET untuk SQL Server meneruskan setiap pernyataan yang Anda buat di jendela kueri sebagai kueri yang tidak berparameter. Jika kueri berisi variabel literal atau Transact-SQL yang menargetkan kolom terenkripsi, Penyedia Data .NET Framework untuk SQL Server tidak akan dapat mendeteksi dan mengenkripsinya sebelum mengirim kueri ke database. Akibatnya, kueri akan gagal karena ketidakcocokan jenis (antara variabel Transact-SQL literal teks biasa dan kolom terenkripsi). Misalnya, kueri berikut akan gagal tanpa parameterisasi, dengan SSN asumsi kolom dienkripsi.

DECLARE @SSN CHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN

Mengaktifkan dan menonaktifkan Parameterisasi untuk Always Encrypted

Parameterisasi untuk Always Encrypted dinonaktifkan secara default.

Untuk mengaktifkan/menonaktifkan Parameterisasi untuk Always Encrypted:

  1. Pilih Pengaturan Preferensi>File>(Pengaturan Preferensi>Kode>di Mac).
  2. Navigasi ke Data>Microsoft SQL Server.
  3. Pilih atau batal pilih Aktifkan Parameterisasi untuk Always Encrypted.
  4. Tutup jendela Pengaturan .

Video singkat memperlihatkan cara mengaktifkan/menonaktifkan Parameterisasi untuk Always Encrypted.

Catatan

Parameterisasi untuk Always Encrypted hanya berfungsi dalam kueri yang menggunakan koneksi database dengan Always Encrypted diaktifkan (lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database). Tidak ada variabel Transact-SQL yang akan diparameterkan jika jendela kueri menggunakan koneksi database tanpa Always Encrypted diaktifkan.

Cara kerja Parameterisasi untuk Always Encrypted

Jika parameterisasi untuk Always Encrypted dan Always Encrypted diaktifkan untuk jendela kueri, Azure Data Studio akan mencoba membuat parameter variabel Transact-SQL yang memenuhi kondisi prasyarat berikut:

  • Dinyatakan dan diinisialisasi dalam pernyataan yang sama (inisialisasi sebaris). Variabel yang dideklarasikan menggunakan pernyataan terpisah SET tidak akan diparameterkan.
  • Diinisialisasi menggunakan satu literal. Variabel yang diinisialisasi menggunakan ekspresi termasuk operator atau fungsi apa pun tidak akan diparameterkan.

Di bawah ini adalah contoh variabel yang akan diparameterkan Oleh Azure Data Studio.

DECLARE @SSN char(11) = '795-73-9838';
   
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;

Berikut adalah beberapa contoh variabel Azure Data Studio tidak akan mencoba membuat parameter:

DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';

DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal

DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal

Agar parameterisasi yang dicoba berhasil:

  • Jenis literal yang digunakan untuk inisialisasi variabel yang akan diparmetrized harus cocok dengan jenis dalam deklarasi variabel.
  • Jika jenis variabel yang dideklarasikan adalah jenis tanggal atau jenis waktu, variabel harus diinisialisasi menggunakan string menggunakan salah satu format yang sesuai dengan ISO 8601 berikut.

Berikut adalah contoh deklarasi variabel Transact-SQL yang akan mengakibatkan kesalahan parameterisasi:

DECLARE @BirthDate date = '01/04/1999' -- unsupported date format   
   
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable

Azure Data Studio menggunakan Intellisense untuk memberi tahu Anda variabel mana yang dapat berhasil diparameterkan dan upaya parameterisasi mana yang gagal (dan mengapa).

Deklarasi variabel yang dapat berhasil diparameterkan ditandai dengan garis bawah pesan info di jendela kueri. Jika Anda mengarahkan mouse ke pernyataan deklarasi yang ditandai dengan garis bawah pesan info, Anda akan melihat pesan yang berisi hasil proses parameterisasi, termasuk nilai properti kunci objek Kelas SqlParameter yang dihasilkan (variabel dipetakan ke: SqlDbType, Size, Presisi, Scale, SqlValue). Anda juga dapat melihat daftar lengkap semua variabel yang telah berhasil diparameterkan dalam tampilan Masalah . Untuk membuka tampilan Masalah, pilih Tampilkan>Masalah.

Jika Azure Data Studio telah mencoba membuat parameter variabel, tetapi parameterisasi telah gagal, deklarasi variabel akan ditandai dengan garis bawah kesalahan. Jika Anda mengarahkan mouse ke pernyataan deklarasi yang telah ditandai dengan garis bawah kesalahan, Anda akan mendapatkan hasil tentang kesalahan tersebut. Anda juga dapat melihat daftar lengkap kesalahan parameterisasi untuk semua variabel dalam tampilan Masalah .

Catatan

Karena Always Encrypted mendukung subset konversi jenis terbatas, dalam banyak kasus diperlukan bahwa jenis data variabel Transact-SQL sama dengan jenis kolom database target yang ditargetkannya. Misalnya, dengan asumsi jenis SSN kolom dalam Patients tabel adalah char(11), kueri di bawah ini akan gagal, karena jenis @SSN variabel, yaitu nchar(11), tidak cocok dengan jenis kolom.

DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;

Msg 402, Level 16, State 2, Line 5   
The data types char(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2' 
and nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') are incompatible in the equal to operator.

Catatan

Tanpa parameterisasi, seluruh kueri, termasuk konversi jenis diproses di dalam SQL Server/Azure SQL Database. Dengan parameterisasi diaktifkan, beberapa konversi jenis dilakukan oleh Microsoft .NET Data Provider untuk SQL Server di dalam Azure Data Studio. Karena perbedaan antara sistem jenis Microsoft .NET dan sistem jenis SQL Server (misalnya, presisi yang berbeda dari beberapa jenis, seperti float), kueri yang dijalankan dengan parameterisasi diaktifkan dapat menghasilkan hasil yang berbeda dari kueri yang dijalankan tanpa parameterisasi diaktifkan.

Langkah berikutnya

Lihat juga