MSSQL Server’da TDE(Transparent Data Encryption) ile Veritabanı Oluşturup AlwaysOn’a Eklemek

SQL Server dünyasında veri güvenliğini sağlamak için kullanılan en güçlü yöntemlerden biri Transparent Data Encryption (TDE)’dir. Özellikle Always On Availability Groups gibi yüksek erişilebilirlik çözümleriyle birlikte kullanıldığında, hem verinin güvenliğini hem de sürekliliğini garanti altına alır.

Transparent Data Encryption (TDE), SQL Server veri dosyalarını (mdf), günlük dosyalarını (ldf) ve yedekleme dosyalarını (backup) disk üzerinde şifreleyen bir güvenlik özelliğidir. “Şeffaf” (Transparent) olarak adlandırılmasının sebebi, uygulama katmanında herhangi bir kod değişikliği gerektirmemesidir. Veri diske yazılırken şifrelenir, bellekten okunurken ise çözülür.

TDE’nin temel amacı “At-Rest” (duragan) veriyi korumaktır. Yani, bir saldırgan fiziksel diskleri veya yedekleme dosyalarını ele geçirse bile, uygun sertifikalara sahip olmadığı sürece veriyi okuyamaz.

• Fiziksel Hırsızlığa Karşı Koruma: Disklerin veya teyp yedeklerinin çalınması durumunda veri anlamsızlaşır.
• Mevzuat Uyumluluğu: KVKK, GDPR ve PCI-DSS gibi standartların gerektirdiği şifreleme kriterlerini karşılar.

Nerelerde Kullanılmalıdır:

• Kişisel Verilerin (TCKN, İsim, Adres) tutulduğu veritabanlarında.
• Finansal kayıtların ve kredi kartı bilgilerinin depolandığı sistemlerde.
• Bulut veya paylaşımlı depolama alanlarında barındırılan kritik sunucularda.

Bir veritabanını TDE ile şifreleyip Always On yapısına dahil etmek istiyorsanız, en kritik nokta Sertifika Yönetimidir. Always On yapısındaki tüm sunucuların (Primary ve Secondary) aynı sertifikaya ve anahtara sahip olması gerekir. Aksi takdirde, Secondary sunucu şifrelenmiş veriyi çözemez ve veritabanı “Recovery Pending” durumuna düşer.

İlk olarak primary sunucusunda veritabanımızı bu yapıda oluşturup secondary sunucusuna ekleme işlemini yapalım:

1.adım

Birinci adım instance üzerinde bir master key oluşturmak. Başka bir ortama restore ederken bu master key kullanmak şart değil başka bir master key de oluşturulabilir. (Başka bir ortama sertifikamı restore yaparken , sertifikadan önce farklı bir master key oluşturdum sorunsuz çalıştı.)

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Parola01*-*-+-';  
GO  

2.adım

Master key oluşturulduktan sonra İkinci adım bir  sertifika oluşturmak.

USE master; 
CREATE CERTIFICATE sertifika  
   WITH SUBJECT = 'test sertifikam';  

3.adım

Oluşturulan sertifikayı kullanabilmek için sertifikanın bir backup’ını almak olmalı çünkü farklı ortamlara veritabanını restore etmek için gerekli  aynı instance üzerinde sertifikaya gerek yoktur. Alwayson yapısına veritabanımızı restore etmek için gerekli olacak. Yoksa veritabanı AG yapısına dahil edilmez.

   Use Master;
BACKUP CERTIFICATE sertifika   TO FILE = 'C:\sertifika\sertifika'  WITH PRIVATE KEY ( FILE = 'C:\sertifika\sertifikaPrivateKey' ,ENCRYPTION BY PASSWORD = 'Elazig23+-+-+-+-' );  

Not: Oluşan .bak ve .pvk dosyalarını manuel olarak Secondary sunucudaki aynı klasöre kopyalayın.

CER VE PVK OLARAK YEDEĞİ ALINIR.

Eğer backup alırken aşağıdaki gibi bir hata alınırsanız bu hata elinizde herhangi bir sertifika olmadığını söylemektedir.(2. adıma dönerek sertifika oluşturulması gerekmektedir.)

Cannot find the certificate ‘sertifika’, because it does not exist or you do not have permission.

Yukarıda oluşturmuş olduğumuz sertifika ve sertifika private key’in yedeğini almayı ve paraloyı bir yere kaydetmeyi unutmayalım. Veritabanını başka bir instance veya sunucuya taşırken olmazsa olmaz yapılarımız.

AdventureWorks2017 veritabanına sertifika yapımızı ekliyoruz. Database Encryption Key (DEK) oluşturuyoruz.

use AdventureWorks2017
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE sertifika;
GO

Aşağıdaki komut ile veritabanı üzerinde şifreleme yapısını başlatıyoruz. Aşağıdaki komutu çalıştırırken bazen şifreli bir şekilde log backup almamız yönünde hata mesajı düşmektedir. Aşağıdaki hata alınırsa log yedek alınır. Şifreli bir şekilde alınması gerekmektedir. log backup alındığında sertifika seçilmezse bile şifreli yapıda yedeği almaktadır.

This command requires a database encryption scan on database ‘AdventureWorks2017’. However, the database has changes from previous encryption scans that are pending log backup. Take a log backup and retry the command.

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.

ALTER DATABASE AdventureWorks2017 SET ENCRYPTION ON;

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.

Şifreleme işlemi verinin boyutuna göre zaman alabilir. Durumu şu sorguyla takip edebilirsiniz:

Select DB_NAME(database_id) As Database_Name,Encryption_State FROM sys.dm_database_encryption_keys

4.adım

İlgili instance altında veritabanını şifreli bir şekilde backup alalım ssms arayüzünden de yapılıyor.

BACKUP DATABASE [AdventureWorks2017] TO  DISK = N'C:\sertifika\tde2017.bak' WITH FORMAT, INIT, 
NAME = N'AdventureWorks2017-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [sertifika]),  STATS = 10
GO

Log backup yukarıda belirttiğim hata mesajı sonucu sertifika ile birlikte alınmaktadır.

BACKUP LOG [AdventureWorks2017] TO  DISK = N'C:\sertifika\LOG.trn' WITH FORMAT, INIT, 
NAME = N'AdventureWorks2017-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [sertifika]),  STATS = 10
GO

SSMS arayüzünden Şifreli backup alınması için media options kısmında belirtilen yerin aktif edilmesi gerekiyor.

Yukarıdaki bölüm işaretlenmezse Encryption kısmı pasif görülmektedir. Encryption bölümü pasif olsa bile yedeklerimiz artık şifreli bir şekilde alınmaktadır.

Yukarıdaki resimde bulunan yerin seçilmesi yeni bir media set oluşturulmasına sebebiyet verecektir. Görseldeki ifadenin anlamı ve teknik karşılığı şudur:

• “Back up to a new media set, and erase all existing backup sets”

Bu seçenek seçildiğinde yazılım şu işlemi yapar:

• Temizlik Yapar: Diskteki eski verileri temizlediği için depolama alanında yer açar ancak geçmişe dönük yedeklerinizi (versiyonları) kaybedersiniz.
• Sıfırdan Başlar: Mevcut yedekleme diskindeki veya medyasındaki (teyp, harici disk vb.) tüm eski yedekleri siler.
• Yeni Bir Küme Oluşturur: “Media set” adını verdiği yeni bir yapı oluşturur ve güncel yedeği buraya yazar.

Kısacası backup alınan dizinde bulunan şifresiz backupların silinmesi gerçekleşir. Karışıklığı engellemek içindir. Bilgisayardaki tüm yedekleri tarayıp bulup silmez. Sadece o an yedekleme hedefi (Backup Destination) olarak seçtiğin dizindeki verileri siler.

İlgili sertifikayı kullanarak backup options kısmından backup’ı şifreli bir şekilde alabiliriz.

Yukarıda TDE yapısında oluşturulan AdventureWorks2017 veritabanını alwayon yapısına dahil edelim.

Resimde dikkat edilirse şifrelenmiş veritabanımızın always on yapısına eklenemediği görülmektedir. AdventureWorks2017 veritabanının durumu “Contains a database encryption…” (Veritabanı şifreleme anahtarı içeriyor) olarak görünüyor. Password bölümünde şifreyi girmenize rağmen veritabanını seçememenizin temel nedeni, SQL Server’ın Always On Wizard üzerinden halihazırda TDE (Transparent Data Encryption) ile şifrelenmiş veritabanlarını eklemeyi desteklememesidir. 

Bu sorunu aşmak için işlemi manuel olarak (T-SQL kullanarak) yapmanız gerekir. İzlemeniz gereken adımlar şunlardır:

Şifrelenmiş veritabanları için bu arayüzü kullanamayız. Primary sunucusu üzerinden veritabanının full ve log yedeği alınır. Alınan bu full ve log backup’ın secondary sunucusuna No recovery modda eklenmesi gerekmektedir. Küçük database’lerde restore edilmesine gerek yoktur. Manuel eklediğimiz veritabanı auto seeding yöntemiyle otomatik olarak eklenmektedir. Sadece şart olan master key veya sertifikanın önceden secondary sunucusu üzerinde oluşturulması gerekmektedir.

Encrypted olarak oluşturulan veritabanının backup’ını alırken şifreli olarak almayıp secondary sunucusuna restore edildiğinde aşağıdaki gibi hata mesajı vermektedir. Her ne kadar da belirtmesek bile veritabanının şifreli olduğu için restore işlemi gerçekleşmez. Yani ne demek istiyorum aşağıdaki resimde gerçek ortamında alınan backup’ı ilgili bölümler seçilmezse bile şifreli bir şekilde alınmaktadır.

Encrypt backup bölümü seçili olmamasına rağmen backup alınıp farklı bir sunucuya restore edileceği zaman aşağıdaki hata mesajı alınmaktadır.

Yukarıdaki backup restore işlemini yapmayıp manuel olarak AG altına veritabanımızı ekleyelim. bakalım veritabanımız secondary sunucusunda ag altına eklenecekmi? Veritabanı boyutu küçük olduğu için bu yöntemi yapıyorum yoksa backup’ımızı restore edecektik.

ALTER AVAILABILITY GROUP [SQLAG] ADD DATABASE [AdventureWorks2017];

Secondary sunucusunda sertifika ve master key olmadığı için veritabanı senkron işlemi gerçekleşmedi. Buda neyi gösteriyor secondary sunucusunda master ve sertifika olmazsa veritabanı ayağa kalkmaz.

Secondary sunucusuna bir master key ve önceden alınmış sertifika backup’ımızı restore işlemini gerçekleştirelim.

Secondary sunucusunda oluşturulan master key primary sunucusundaki master key’den farklı olabilir.

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Parola01+-+-+-+-';  
GO  

Önceden alınmış sertifika backup dizininin belirtilmesi gerekmektedir.

   CREATE  CERTIFICATE sertifika  
   FROM  FILE = 'C:\sertifika\sertifika'  
    WITH PRIVATE KEY ( 
	FILE = 'C:\sertifika\sertifikaPrivateKey' ,   
    DECRYPTION  BY PASSWORD = 'Elazig23+-+-+-+-' );  

Secondary sunucuda sertifikanın doğru yüklendiğinden emin olmak için şu sorguyu çalıştırabilirsiniz:

SELECT * FROM sys.certificates WHERE name = 'sertifika';

Sertifikamızı ve master key secondary sunucusuna restore edildikten sonra full backup’ın restore edildiği görülmektedir.

Primary sunucusu üzerinde bu yapı çalıştırılır. Bu yapımız AG üzerinde bulunan Auto seeding modunu açmaktadır. Bu sebepten primary ve secondary sunucuya restore edilen veritabanı otomatik olarak bağlanmaktadır.

ALTER AVAILABILITY GROUP [SQLAG] ADD DATABASE [AdventureWorks2017];

Secondary sunucusunda ag altına veritabanını alma sorunu yaşarsanız kısacası otomatik olarak gelmezse aşağıdaki komut ile veritabanı secondary sunucusunda AG altına alınmaktadır.

ALTER DATABASE [AdventureWorks2017] SET HADR AVAILABILITY GROUP = [SQLAG]; 

NOT: Bir veritabanı Alwayson yapısında ise veritabanımızda encrypted yapısını aktif edersek secondary sunucusunda veritabanı Not synchronizing moduna geçer. İlgili sertifika secondary sunucusuna yüklense bile veritabanı ayağa kalkmaz.

Yukarıda yaptığımız işlemlerde encrypted yapısında olan bir veritabanını alwayson yapısına eklerken Wizard ekranından karşılaşılan bir sorun üzerine manuel olarak yapmış olduk. İkinci manuel işlem aşağıdaki gibi olmaktadır. Bu yapı içinde secondary sunucusunda master key ve sertifikanın bulunması gerekmektedir.

Script alındıktan sonra veritabanı ismi sertfikalı olan SSMS arayüzünün seçilmesine izin vermediği veritabanı olarak değiştirilir. Database büyükse secondary sunucuya norecovery modda restore edilir. en sonra seecondary sunucusunda AG altında join işlemi yapılmaktadır.

Başka makalede görüşmek dileğiyle..

“De ki: ‘Ey kendilerinin aleyhine aşırı giden kullarım! Allah’ın rahmetinden ümidinizi kesmeyin. Şüphesiz Allah, bütün günahları affeder. Çünkü O, çok bağışlayandır, çok merhamet edendir.’ “ Zümer Suresi; 53. Ayet