Bu makalede database in(dikkat edelim backup değil) şifreli bir şekilde oluşturup kendisine bağlı olan sertifika yedeğinin kaybolması durumunda database i encryption dan kurtarma, sertifika silme ve master key silme işlemlerini yapmış olacağız. Bu şekliyle database i ilk kurulduğu gibi yapabiliriz. Rahatlıkla backup ve restore yöntemlerini kullanabiliriz.
Küçük bir bilgi vermek gerekirse TDE (Transparent Data Encryption), SQL Server’da veri dosyalarının disk üzerinde şifrelenmesini sağlayan bir güvenlik özelliğidir. TDE, veritabanını şeffaf bir şekilde şifreler; yani uygulamalar, kullanıcılar veya sorgular şifrelemeyi fark etmeden veriye erişebilir. Ama fiziksel veritabanı dosyaları (.mdf, .ldf, .bak) şifreli olur.
Not: Sürekli bir işlem yükü getirir. Veri her okunduğunda ve yazıldığında CPU şifreleme/çözme işlemi yapar.
TDE Ne İşe Yarar?
- Veritabanı dosyalarının disk üzerindeki halini şifreler: Yani sunucudan dosya çalınsa bile başka bir yere taşınıp açılamaz.
- Backup dosyalarını da şifreler: TDE aktifse alınan .bak dosyaları da şifreli olur.
- Şeffaf çalışır: Uygulamalarda ya da SQL sorgularında bir değişiklik yapmanıza gerek yoktur.
Örnek üzerinden yapılması anlaşılabilirliği açısından daha iyi olacaktır.
İlk başta herhangi bir database’i encryption’lı yapıda oluşturuyorum. Daha sonra kaldırma işlemini görmüş olacağız.
USE a
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE sertifika;
GOAşağıdaki komutla şifreli bir şekilde oluşturulan veritabanlarını görebiliriz.
select DB_NAME(database_id),encryptor_type from sys.dm_database_encryption_keys
Veritabanı şifreleme ve şifre çözümleme durumları aşağıda görülmektedir.
Aşağıdaki kod bloğundaki Encryption_State_Desc kısmının açıklaması.
| encryption_state | encryption_state_desc | Anlamı |
|---|---|---|
| 0 | Unencrypted | Veritabanı şifreli değil. |
| 1 | Encryption in progress | Şifreleme işlemi başlıyor/devam ediyor. |
| 2 | Encrypted | Veritabanı tamamen şifrelenmiş durumda. |
| 3 | Key change in progress | Şifreleme anahtarı değiştiriliyor. |
| 4 | Decryption in progress | Veritabanı şifresi çözülüyor (TDE kapatılıyor). |
| 5 | Protection change in progress | Sertifika ya da güvenlik yapısı değiştiriliyor. |
| 6 | Protection changed | Güvenlik yapısı başarıyla değiştirildi. |
Select DB_NAME(database_id) As Database_Name,Encryption_State,
--encryptor_type
FROM sys.dm_database_encryption_keys
GO
----------------------------------------
SELECT name, is_encrypted
FROM sys.databases
Go
Database’i sertifikalı ve Encryption hala getirdim. Ama hale sertifika off olduğu için Encryption _state 1 gözüküyor. Database üzerine sağ tıklayıp options kısmından encryption enabled true yapabiliriz ya da aşağıdaki kod bloğu ile bu ifadeyi create script ile yapabiliriz.
SQL Server’ın arayüzünde (SSMS) veya kodla o değeri True yapmaya çalıştığında, eğer arka planda bir sertifika hazırlığı yoksa SQL Server buna izin vermez.
ALTER DATABASE [a]
SET ENCRYPTION on;
GO
ALTER DATABASE [SIFRELI]
SET ENCRYPTION on;
GO
Veritabanlarının Encryption’ı açtıktan sonra select sorgusunu çalıştırdığımızda veritabanı encryption hale gelmiş oldu.
Buraya kadar tüm işlemler bir veritabanını encryption(TDE) yapma konusu, master key ve sertifika işlemleri dahil edilirse TDE yapısı tamamlanmış olacak.
- Asıl Konumuz Burada Başlıyor.
Şifrelenmiş .bak uzantılı veritabanlarımızı alıp başka bir instance’a restore ettiğim zaman aşağıdaki hata mesajıyla karşılaşırız. Bu hata sebebi sertifika istiyor.
Ama benim elimde SERTİFİKA YOK ve bundan dolayı database’in şifreli bu yapısından kurtulmak istiyorum. Bunun için aşağıdaki yöntemleri teker teker deniyorum.
En doğru sonuca gidebilirdim adım adım teyit etmek istedim.
Öncelikle veritabanı üzerinde encryption’ı kapatalım öyle backup alıp tekrar restore edelim.(Bu yöntemin sadece işe yarayıp yaramadığını kontrol etme)
ALTER DATABASE a
SET ENCRYPTION OFF;
GO Veritabanı üzerinde encryption’ı off yaptıktan sonrada aynı hatayı veriyor.( Demek bu işlem yeterli değil)
Aşağıdaki Script ile hangi veritabanında şifreleme açıksa kapatıyoruz sonra işlemlerimize başlıyoruz
(Hedefim tüm db’lerde şifrelemeyi kapatıp instance bazında sertifika ve master key i silmek)
Use MASTER
ALTER DATABASE SIFRELI SET ENCRYPTION OFF Büyük database’lerde şifre çözme işlemi başlayınca 5 olan sayıyı veriyor ve Decryption key işlemine alıyor. Makalenin başında bu ifadelerin açıklamasını vermiştik.
Gerçek sistemden alınmış büyük bir veritabanının encryption key’ini kapattıktan sonra şifre çözme(Decryption key) işleminin yaptığını görüyoruz. Yukarıdaki küçük veritabanımızda bu ifade hemen 1 olmaktadır.
Encryption off yaptıktan sonra Encryption_state 5 veya 1 oluyor 1 değerine düştükten sonra drop database encryption key komutu ile tamamen database’i encryption key’den koparabiliriz.
Refresh yaptıkça şifreleme çözülüyor sonra drop database encryption key deyip veritabanı üzerinden tamamen kaldırıyoruz. Burada 5 ifadesi şifreyi çözdüğü anlamına gelmektedir.Aşağıdaki listede hangi encryption_state’in ne anlama geldiği belirtilmektedir.
Tüm database’ler için bu işlemi yapıyoruz sadece tempdb hariç.
USE a;
GO
DROP DATABASE ENCRYPTION KEY;
GO
USE SIFRELI;
GO
DROP DATABASE ENCRYPTION KEY;
GOTamamen veritabanını şifreli yapıdan çıkarıyoruz. Aynı işlemler a veritabanı içinde yapılacak.
Aşağıdaki işlemi instance altında bulunan tüm veritabanlarında is_encryptied’i 0 yaptıktan sonra sertifika ve master key silinmesi yapılmaktadır.
USE master
Go
DROP CERTIFICATE sertifika;
Go
--En sonda master key'imizi siliyoruz.
USE master
Go
DROP MASTER KEY;
GOSon olarak db’lerin hepsinin şifreli bir yapıda olmadığını görmüş olacağız.
Tek db’nin encryption yapıda olması restore işleminde hata vermesini sebep olacak zaten her bir veritabanında encryption’ı kapatsak bile sertifika ve master key’i silmeden restore yapamayız.
Yukarıdaki tüm adımları yapıp encryption key,sertifika ve master key’i sildikten sonra restore işlemimiz başka bir instance’a başarılı bir şekilde yapıldı.
En sonda select sorgumuzu tekrar çektiğimizde tüm database’lerde sertifika olmadığını görüyoruz diğer databaselerde is_encryted 0 değerinde. Ama master key altında bulunan Security>Certificates bölümünde sertifikamızı sağ tıklayıp silebiliriz. Ya da komutla silebiliriz.
Özetle Bir ENCRYPTION DB Oluşturma
Bir veritabanı yedeği, aslında veritabanının belirli bir andaki anlık görüntüsüdür (snapshot). Siz canlı veritabanında şifrelemeyi kapatsanız bile, daha önce aldığınız backup dosyası fiziksel olarak hala şifrelenmiş bloklardan oluşur.
- Geçmişe Etki Etmez: Veritabanı üzerindeki “Decryption” (şifre çözme) işlemi sadece canlı verideki sayfaları çözer.
- Sertifika Gereksinimi: Şifreli alınan bir yedeği restore etmek istediğinizde, SQL Server o dosyanın içindeki veriyi okuyabilmek için hala o dosyayı şifreleyen Master Key ve Certificate ikilisine ihtiyaç duyar.
Eğer başka bir instance’a sertifika taşımakla uğraşmak istemiyorsanız ve yedeğin şifresiz olmasını istiyorsanız şu adımları izlemelisiniz:
- Encryption’ı Kaldırın: Canlı veritabanında şifrelemeyi kapatın (ALTER DATABASE […] SET ENCRYPTION OFF).
- İşlemin Bitmesini Bekleyin: Şifre çözme işlemi arka planda bir süre devam eder. Şu sorguyla durumunu kontrol edebilirsiniz: 1 = Unencrypted / Şifresiz olması gerekir
SELECT encryption_state FROM sys.dm_database_encryption_keys- Yeni Backup Alın: Şifreleme tamamen kalktıktan sonra yeni bir Full Backup alın. Bu yeni yedek dosyası artık sertifika gerektirmeyecektir.
Eğer kullanıcı veritabanlarınızdan en az biri şifreliyse, SQL Server TempDB’yi otomatik olarak şifreler. Tüm kullanıcı veritabanlarında şifrelemeyi kapatsanız bile, TempDB şifreli kalmaya devam edebilir.
Encryption State 1: sys.dm_database_encryption_keys üzerinde gördüğünüz State 1, veritabanının “Unencrypted” (Şifresiz) olduğunu ifade eder.
Değerlerin Anlamı:
- 1: Unencrypted (Şifresiz)
- 2: Encryption in progress (Şifreleniyor)
- 3: Encrypted (Şifreli)
Yani TempDB’de encryption_state = 1 görüyorsanız, şifreleme başarıyla kalkmış demektir. Servisi yeniden başlatmak, TempDB’nin temiz bir sayfa açmasını sağlar ve bekleyen şifreleme kalıntılarını temizler.
Primary üzerinde şifrelemeyi başlattığınızda bu işlem Transaction Log aracılığıyla Secondary sunucuya aktarılır. Secondary sunucuda şifreleme işleminin (veya şifreli DB’nin erişilebilirliğinin) devam edebilmesi için Primary’deki Sertifika (Certificate) ve Master Key‘in yedeğinin Secondary sunucuya manuel olarak yüklenmiş olması şarttır. Sertifika hazırsa, Primary’de şifrelemeyi açtığınızda Secondary’de de veriler diskte şifreli (at-rest) olarak saklanır. Primary sunucuda şifrelemeyi kapattığınızda (SET ENCRYPTION OFF), bu komut Secondary sunucuya da yansır. Secondary’de ayrıca işlem yapmanıza gerek yoktur.
SQL Server’da (veya benzer ilişkisel veritabanlarında) veritabanı düzeyinde “Encryption ON” yapmak, TDE mekanizmasını devreye sokar.
ALTER DATABASE SIFRELI SET ENCRYPTION ON;TDE hiyerarşisi şu şekildedir:
- Windows İşletim Sistemi Seviyesi (DPAPI)
- Service Master Key (Sunucu seviyesi)
- Database Master Key (Master DB seviyesi)
- Certificate (Sertifika)
- Database Encryption Key (DEK) (Veritabanı seviyesi – Yukarıda komut ile açtığınız kısım burasıdır)
Hiyerarşide sadece 5. adımı (DEK) tetiklemiş olmanız, o anahtarın tek başına çalıştığı anlamına gelmez. TDE bir güven zinciri (Chain of Trust) mantığıyla çalışır. Çünkü TDE (Şeffaf Veri Şifreleme) bir zincirleme reaksiyondur. 5. adımı (DEK) aktif edebilmeniz için 4. adımın (Sertifika) hazır olması şarttır.
SQL Server’ın arayüzünde (SSMS) veya kodla o değeri True yapmaya çalıştığında, eğer arka planda bir sertifika hazırlığı yoksa SQL Server buna izin vermez.
Encryption Enabled: True görüyorsan, bu şu anlama gelir: Sistemde hali hazırda bir sertifika var ve bu veritabanı o sertifika ile şifrelenmiş durumdadır.
“Şifreleme” dendiğinde aslında önümüzde iki ana yol var. Hangisini seçeceğiniz ihtiyacınıza bağlıdır:
| Özellik | TDE (Transparent Data Encryption) | Always Encrypted |
| Amacı | Diskin/Dosyanın çalınmasına karşı koruma. | DBA (Veritabanı Yöneticisi) dahil kimsenin veriyi görememesi. |
| Uygulama | Veritabanı seviyesindedir, kod değişikliği gerekmez. | Sütun seviyesindedir, uygulama tarafında sürücü desteği gerekir. |
| Performans | Çok düşük maliyet (%3-%5 civarı). | Şifreleme istemci tarafında olduğu için işlemci yükü artabilir. |
| Görünürlük | Yönetici veriyi görebilir. | Yönetici sadece şifreli (anlamsız) karakterler görür. |
Yedek dosyalarınızın çalınması durumunda okunmasını engellemek istiyorsanız TDE veya Backup Encryption kullanmalısınız.
Not: Service Master Key her sunucunun kendine özel anahtarıdır.
Başka bir makalede görüşmek üzere.
“De ki: Ey Rabbim! İlmimi artır.” Taha-114
