Mengonfigurasi enkripsi kolom di tempat dengan Transact-SQL

  • Artikel
  • 5 menit untuk membaca

Berlaku untuk:yes Dimulai dengan SQL Server 2019 (15.x) - Windows hanya YesAzure SQL Database

Artikel ini menjelaskan cara melakukan operasi kriptografi di tempat pada kolom menggunakan Always Encrypted dengan enklave aman dengan pernyataan AlTER TABLE Statement/ALTER COLUMN. Untuk informasi dasar tentang enkripsi di tempat dan prasyarat umum, lihat Mengonfigurasi enkripsi kolom di tempat menggunakan Always Encrypted dengan enklave aman.

ALTER TABLE Dengan pernyataan atau ALTER COLUMN , Anda dapat mengatur konfigurasi enkripsi target untuk kolom. Saat Anda menjalankan pernyataan, enklave aman sisi server akan mengenkripsi, mengenkripsi ulang, atau mendekripsi data yang disimpan di kolom, tergantung pada konfigurasi enkripsi saat ini dan target, yang ditentukan dalam definisi kolom dalam pernyataan.

  • Jika kolom saat ini tidak dienkripsi, kolom akan dienkripsi jika Anda menentukan ENCRYPTED WITH klausa dalam definisi kolom.
  • Jika kolom saat ini dienkripsi, kolom akan didekripsi (dikonversi ke kolom teks biasa), jika Anda tidak menentukan ENCRYPTED WITH klausa dalam definisi kolom.
  • Jika kolom saat ini dienkripsi, kolom akan dienkripsi ulang jika Anda menentukan ENCRYPTED WITH klausa dan jenis enkripsi kolom yang ditentukan atau kunci enkripsi kolom berbeda dari jenis enkripsi yang saat ini digunakan atau kunci enkripsi kolom.

Catatan

Anda tidak dapat menggabungkan operasi kriptografi dengan perubahan lain dalam satu ALTER TABLE/ALTER COLUMN pernyataan, kecuali mengubah kolom menjadi NULL atau NOT NULL, atau mengubah kolate. Misalnya, Anda tidak dapat mengenkripsi kolom DAN mengubah jenis data kolom dalam satu ALTER TABLE/ALTER COLUMN pernyataan Transact-SQL. Gunakan dua pernyataan terpisah.

Sebagai kueri apa pun yang menggunakan enklave aman sisi server, ALTER TABLE/ALTER COLUMN pernyataan yang memicu enkripsi di tempat harus dikirim melalui koneksi dengan komputasi Always Encrypted dan enklave diaktifkan.

Sisa artikel ini menjelaskan cara memicu enkripsi di tempat menggunakan ALTER TABLE/ALTER COLUMN pernyataan dari SQL Server Management Studio. Atau, Anda dapat mengeluarkan dari ALTER TABLE/ALTER COLUMN Azure Data Studio atau aplikasi Anda.

Catatan

Saat ini, cmdlet Invoke-Sqlcmd dalam modul SqlServer PowerShell dan sqlcmd, tidak mendukung penggunaan ALTER TABLE/ALTER COLUMN untuk operasi kriptografi di tempat.

Melakukan enkripsi di tempat dengan transact-SQL di SSMS

Prasyarat

Langkah-langkah

  1. Buka jendela kueri dengan komputasi Always Encrypted dan enklave diaktifkan dalam koneksi database. Untuk detailnya, lihat Mengaktifkan dan menonaktifkan Always Encrypted untuk koneksi database .

  2. Di jendela kueri, terbitkan ALTER TABLE/ALTER COLUMN pernyataan , tentukan konfigurasi enkripsi target untuk kolom yang ingin Anda enkripsi, dekripsi, atau enkripsi ulang. Jika Anda mengenkripsi atau mengenkripsi ulang kolom, menggunakan ENCRYPTED WITH klausa . Jika kolom Anda adalah kolom string (misalnya, , char, varcharnchar, nvarchar), Anda mungkin juga perlu mengubah kolatasi menjadi kolab BIN2.

    Catatan

    Jika kunci master kolom Anda disimpan di Azure Key Vault, Anda mungkin diminta untuk masuk ke Azure.

  3. Hapus cache paket untuk semua batch dan prosedur tersimpan yang mengakses tabel, untuk me-refresh informasi enkripsi parameter.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Catatan

    Jika Anda tidak menghapus rencana untuk kueri yang terkena dampak dari cache, eksekusi pertama kueri setelah enkripsi mungkin gagal.

    Catatan

    Gunakan ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE atau DBCC FREEPROCCACHE untuk menghapus cache paket dengan hati-hati, karena dapat mengakibatkan penurunan performa kueri sementara. Untuk meminimalkan dampak negatif dari menghapus cache, Anda dapat secara selektif menghapus rencana untuk kueri yang terkena dampak saja.

  4. Panggil sp_refresh_parameter_encryption untuk memperbarui metadata untuk parameter setiap modul (prosedur tersimpan, fungsi, tampilan, pemicu) yang bertahan di sys.parameters dan mungkin telah dibatalkan dengan mengenkripsi kolom.

Contoh

Mengenkripsi kolom di tempat

Contoh di bawah ini mengasumsikan:

  • CEK1 adalah kunci enkripsi kolom berkemampuan enklave.
  • Kolom SSN teks biasa dan saat ini menggunakan kolase database default, seperti Latin1, kolase non-BIN2 (misalnya, Latin1_General_CI_AI_KS_WS).

Pernyataan mengenkripsi SSN kolom menggunakan enkripsi acak dan kunci enkripsi kolom berkemampuan enklave di tempat. Ini juga menimpa kolase database default dengan kolase BIN2 (di halaman kode yang sama).

Operasi dilakukan secara online (ONLINE = ON). Perhatikan juga panggilan ke ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, yang membuat ulang paket kueri dipengaruhi oleh perubahan skema tabel.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char] COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Mengenkripsi ulang kolom di tempat untuk mengubah jenis enkripsi

Contoh di bawah ini mengasumsikan:

  • Kolom SSN dienkripsi menggunakan enkripsi deterministik dan kunci enkripsi kolom berkemampuan enklave, CEK1.
  • Kolase saat ini, yang diatur pada tingkat kolom, adalah Latin1_General_BIN2.

Pernyataan di bawah ini mengenkripsi ulang kolom menggunakan enkripsi acak dan kunci yang sama (CEK1)

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Mengenkripsi ulang kolom di tempat untuk memutar kunci enkripsi kolom

Contoh di bawah ini mengasumsikan:

  • Kolom SSN dienkripsi menggunakan enkripsi acak dan kunci enkripsi kolom berkemampuan enklave, CEK1.
  • CEK2 adalah kunci enkripsi kolom berkemampuan enklave (berbeda dari CEK1).
  • Kolase saat ini, yang diatur pada tingkat kolom, adalah Latin1_General_BIN2.

Pernyataan di bawah ini mengenkripsi ulang kolom dengan CEK2.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Mendekripsi kolom di tempat

Contoh di bawah ini mengasumsikan:

  • Kolom SSN dienkripsi menggunakan kunci enkripsi kolom yang diaktifkan enklave.
  • Kolase saat ini, yang diatur pada tingkat kolom, adalah Latin1_General_BIN2.

Pernyataan di bawah ini mendekripsi kolom dan menjaga kolase tidak berubah. Atau, Anda dapat memilih untuk mengubah kolamen. Misalnya, ubah kolajeasi menjadi kolace non-BIN2 dalam pernyataan yang sama.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Langkah berikutnya

Lihat juga