Kolom kueri menggunakan Always Encrypted dengan SQL Server Management Studio

  • Artikel
  • 10 menit untuk membaca

Berlaku untuk:yes SQL Server (semua versi yang didukung) YesAzure SQL Database YesAzure SQL Managed Instance

Artikel ini menjelaskan cara mengkueri kolom, dienkripsi dengan Always Encrypted menggunakan SQL Server Management Studio (SSMS). Dengan SSMS, 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).

Catatan

Menggunakan kunci master kolom yang disimpan dalam HSM terkelola di Azure Key Vault memerlukan SSMS 18.9 atau versi yang lebih baru.

Mengambil nilai ciphertext yang disimpan dalam kolom terenkripsi

Menjalankan kueri SELECT yang mengambil ciphertext data yang disimpan dalam kolom terenkripsi (tanpa mendekripsi data) tidak mengharuskan Anda memiliki akses ke kunci master kolom yang melindungi data. Untuk mengambil nilai dari kolom terenkripsi sebagai ciphertext di SSMS:

  1. Pastikan Anda dapat mengakses metadata tentang kunci yang melindungi kolom, yang Anda gunakan untuk menjalankan kueri. Meskipun Anda tidak perlu dapat mengakses kunci master kolom aktual, Anda memerlukan izin tingkat database untuk melihat kunci master kolom dan objek metadata kunci enkripsi kolom dalam database. Untuk detailnya, lihat Izin untuk mengkueri kolom terenkripsi di bawah ini.
  2. Pastikan Anda telah menonaktifkan Always Encrypted untuk koneksi database untuk jendela Editor Kueri, tempat Anda akan menjalankan kueri yang SELECT mengambil nilai ciphertext. Lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database di bawah ini.
  3. 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.

Screenshot of the SELECT [SSN] FROM [dbo].[Patients] query and the results of the query shown as binary ciphertext values.

Mengambil nilai teks biasa yang disimpan dalam kolom terenkripsi

Untuk mengambil nilai dari kolom terenkripsi sebagai teks biasa (untuk mendekripsi nilai):

  1. Pastikan Anda dapat mengakses kunci master kolom dan metadata tentang kunci yang melindungi kolom, yang Anda gunakan untuk menjalankan kueri. Untuk detailnya, lihat Izin untuk mengkueri kolom terenkripsi di bawah ini.
  2. Pastikan Anda telah mengaktifkan Always Encrypted untuk koneksi database untuk jendela Editor Kueri, tempat Anda akan menjalankan kueri yang SELECT mengambil dan mendekripsi data Anda. Ini akan menginstruksikan Penyedia Data .NET Framework untuk SQL Server (digunakan oleh SSMS) untuk mendekripsi kolom terenkripsi dalam kumpulan hasil kueri. Lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database di bawah ini.
  3. 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 char(11) 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

Screenshot of the SELECT [SSN] FROM [Clinic].[dbo].[Patients] query and the results of the query shown as plain text values.

Mengirim nilai teks biasa yang menargetkan kolom terenkripsi

Untuk menjalankan kueri yang mengirim nilai yang menargetkan kolom terenkripsi, misalnya kueri yang menyisipkan, memperbarui, atau memfilter menurut nilai yang disimpan dalam kolom terenkripsi:

  1. Pastikan Anda dapat mengakses kunci master kolom dan metadata untuk kunci yang melindungi kolom yang dijalankan kueri Anda. Untuk informasi selengkapnya, lihat Izin untuk mengkueri kolom terenkripsi di bawah ini.

  2. Pastikan Anda telah mengaktifkan Always Encrypted untuk koneksi database untuk jendela Editor Kueri, tempat Anda akan menjalankan kueri yang SELECT mengambil dan mendekripsi data Anda. Ini akan menginstruksikan Penyedia Data .NET Framework untuk SQL Server (digunakan oleh SSMS) untuk mendekripsi kolom terenkripsi dalam kumpulan hasil kueri. Lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database di bawah ini.

  3. Pastikan Parameterisasi untuk Always Encrypted diaktifkan untuk jendela Editor Kueri. (Memerlukan setidaknya SSMS versi 17.0.) Deklarasikan variabel Transact-SQL dan inisialisasi dengan nilai, yang ingin Anda kirim (sisipkan, perbarui, atau filter menurut) ke database. Lihat Parameterisasi untuk Always Encrypted di bawah ini untuk detailnya.

  4. Jalankan kueri Anda yang mengirim nilai variabel Transact-SQL ke database. SQL Server Management Directory akan mengonversi variabel ke parameter kueri dan akan mengenkripsi nilainya sebelum mengirimkannya ke database.

Contoh

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

Screenshot of the query using a variable for @SSN and the resulting row returned.

Izin untuk mengkueri kolom terenkripsi

Untuk menjalankan kueri apa pun terhadap kolom terenkripsi, termasuk kueri yang mengambil data dalam ciphertext, Anda memerlukan VIEW ANY COLUMN MASTER KEY DEFINITION izin dan VIEW ANY COLUMN ENCRYPTION KEY DEFINITION 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 ke 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 Anda menyambungkan ke database di SSMS, Anda bisa mengaktifkan atau menonaktifkan Always Encrypted untuk koneksi database. Secara default, Always Encrypted dinonaktifkan.

Mengaktifkan Always Encrypted untuk koneksi database menginstruksikan Penyedia Data .NET Framework untuk SQL Server, yang digunakan oleh SQL Server Management 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 Framework untuk SQL Server, yang digunakan SSMS, tidak akan mencoba mengenkripsi parameter kueri atau mendekripsi hasil.

Anda dapat mengaktifkan atau menonaktifkan Always Encrypted saat membuat koneksi baru atau mengubah koneksi yang sudah ada menggunakan dialog Sambungkan ke Server .

Untuk mengaktifkan (menonaktifkan) Always Encrypted:

  1. Buka dialog Sambungkan Ke Server (lihat Menyambungkan ke instans SQL Server untuk detailnya).
  2. Klik Opsi >>.
  3. Jika Anda menggunakan SSMS 18 atau yang lebih baru:
    1. Pilih tab Always Encrypted.
    2. Untuk mengaktifkan Always Encrypted, pilih Aktifkan Always Encrypted (enkripsi kolom). Untuk menonaktifkan Always Encrypted, pastikan Aktifkan Always Encrypted (enkripsi kolom) tidak dipilih.
  4. Jika Anda menggunakan SSMS 17 atau yang lebih lama:
    1. Pilih tab Properti Tambahan .
    2. Untuk mengaktifkan Always Encrypted, ketik Column Encryption Setting = Enabled. Untuk menonaktifkan Always Encrypted, tentukan Column Encryption Setting = Disabled atau hapus pengaturan Pengaturan Enkripsi Kolom dari tab Properti Tambahan (nilai defaultnya dinonaktifkan).
  5. Klik Sambungkan.

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

Tip

Untuk beralih antara Always Encrypted diaktifkan dan dinonaktifkan untuk jendela Editor Kueri yang sudah ada:

  1. Klik kanan di mana saja di jendela Editor Kueri.
  2. Pilih Koneksi>Ubah Koneksi .... Ini akan membuka dialog Sambungkan ke Server untuk koneksi saat ini untuk jendela Editor Kueri.
  3. Aktifkan atau nonaktifkan Always Encrypted, dengan mengikuti langkah-langkah di atas dan klik Sambungkan.

Parameterisasi untuk Always Encrypted

Parameterisasi untuk Always Encrypted adalah fitur di SQL Server Management Studio yang secara otomatis mengonversi variabel Transact-SQL menjadi parameter kueri (instans Kelas SqlParameter). (Memerlukan setidaknya SSMS versi 17.0.) Ini memungkinkan Penyedia Data .NET Framework dasar untuk SQL Server mendeteksi data yang menargetkan kolom terenkripsi, dan mengenkripsi data tersebut sebelum mengirimkannya ke database.

Tanpa parameterisasi, Penyedia Data .NET Framework meneruskan setiap pernyataan, Anda menulis di Editor Kueri, sebagai kueri non-parameter. Jika kueri berisi literal atau variabel 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 asumsi SSN kolom dienkripsi.

DECLARE @SSN NCHAR(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 untuk jendela Editor Kueri saat ini:

  1. Pilih Kueri dari menu utama.
  2. Pilih Opsi Kueri....
  3. Navigasi ke Eksekusi>Tingkat Lanjut.
  4. Pilih atau batal pilih Aktifkan Parameterisasi untuk Always Encrypted.
  5. Klik OK.

Untuk mengaktifkan/menonaktifkan Parameterisasi untuk Always Encrypted untuk jendela Editor Kueri di masa mendatang:

  1. Pilih Alat dari menu utama.
  2. Pilih Opsi... .
  3. Navigasi ke Kueri Eksekusi>SQL Server>Tingkat Lanjut.
  4. Pilih atau batal pilih Aktifkan Parameterisasi untuk Always Encrypted.
  5. Klik OK.

Jika Anda menjalankan kueri di jendela Editor Kueri yang menggunakan koneksi database dengan Always Encrypted diaktifkan, tetapi parameterisasi tidak diaktifkan untuk jendela Editor Kueri, Anda akan diminta untuk mengaktifkannya.

Catatan

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

Cara kerja Parameterisasi untuk Always Encrypted

Jika Parameterisasi untuk Always Encrypted dan perilaku Always Encrypted dalam koneksi database diaktifkan untuk jendela Editor Kueri, SQL Server Management Studio akan mencoba membuat parameter variabel Transact-SQL yang memenuhi kondisi prasyarat berikut:

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

Di bawah ini adalah contoh variabel, SQL Server Management Studio akan membuat parameter.

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

Dan, berikut adalah beberapa contoh variabel yang tidak akan coba diparameterkan oleh SQL Server Management Studio:

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

SQL Server Management 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 peringatan di Editor Kueri. Jika Anda mengarahkan kursor ke pernyataan deklarasi yang ditandai dengan garis bawah peringatan, Anda akan melihat hasil proses parameterisasi, termasuk nilai properti utama objek SqlParameter yang dihasilkan (variabel dipetakan ke): SqlDbType, Size, Precision, Scale, SqlValue. Anda juga dapat melihat daftar lengkap semua variabel yang telah berhasil diparameterkan di tab Peringatan dari tampilan Daftar Kesalahan . Untuk membuka tampilan Daftar Kesalahan , pilih Tampilan dari menu utama lalu pilih Daftar Kesalahan.

Jika SQL Server Management 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 di tab Kesalahan dari tampilan Daftar Kesalahan . Untuk membuka tampilan Daftar Kesalahan , pilih Tampilan dari menu utama lalu pilih Daftar Kesalahan.

Cuplikan layar di bawah ini menunjukkan contoh enam deklarasi variabel. SQL Server Management Studio berhasil membuat parameter tiga variabel pertama. Tiga variabel terakhir tidak memenuhi kondisi prasyarat untuk parameterisasi, dan oleh karena itu, SQL Server Management Studio tidak mencoba membuat parameter (deklarasinya tidak ditandai dengan cara apa pun).

Screenshot showing an example of six variable declarations with three successfully parameterized and three failures and the associated warning messages.

Contoh lain di bawah ini, menunjukkan dua variabel yang memenuhi kondisi prasyarat untuk parameterisasi, tetapi upaya parameterisasi telah gagal karena variabel salah diinisialisasi.

Screenshot showing an example of two variable declarations that ultimately fail with the associated error messages.

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, itu menargetkan. Misalnya, dengan asumsi jenis SSN kolom dalam Patients tabel adalah char(11), kueri di bawah ini akan gagal, sebagai 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 .NET Framework di dalam SQL Server Management Studio. Karena perbedaan antara sistem jenis .NET Framework 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