SQL Server TDE Hatası: “Cannot find server asymmetric key” – Çözüm, Güvenlik Açığı ve Otomatik İzleme
SQL Server’da TDE restore hatası ‘Cannot find server asymmetric key’ ile karşılaştınız mı? Certificate backup alma, restore etme ve format sonrası kurtarma yöntemleri. Ayrıca TDE’nin bilinmeyen güvenlik açığı: Sysadmin verilerinizi nasıl çalabilir?
Veritabanında TDE (Transparent Data Encryption) Kullanılmışsa Görülen Hata
**Hata Mesajı:**
Cannot find server asymmetric key with thumbprint ‘0x7b8626f7e9b646cb8702530018600bf3’
### Bu hata ne anlama geliyor?
Restore etmeye çalıştığınız veritabanı, eski SQL Server instance’ında bulunan bir **Certificate** veya **Asymmetric Key**’e bağlı. Yeni sunucuda bu güvenlik nesnesi bulunmadığı için SQL Server veritabanını açamıyor.
—
## Öncelikle TDE Kullanılıp Kullanılmadığını Kontrol Edin
**Eski sunucuda thumbprint ile sorgulama:**
SELECT name, thumbprint
FROM sys.certificates
WHERE thumbprint = 0x7b8626f7e9b646cb8702530018600bf3;
Eski sunucuda genel kontrol:
SELECT
db.name AS DatabaseName,
dek.encryption_state,
dek.percent_complete,
dek.key_algorithm,
dek.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek
ON db.database_id = dek.database_id;
✅ Eğer veritabanınız listeleniyorsa → TDE aktif demektir.
TDE Restore İşlemi İçin Gerekli Adımlar
Eski sunucudan aşağıdaki nesneleri almanız gerekir:
Database Master Key (DMK) – master sistem veritabanında oluşturulur ve certificate’ı korur
Certificate
Certificate Private Key
master veritabanında zaten bir DMK olup olmadığını kontrol etmek önemlidir:
SELECT * FROM sys.symmetric_keys WHERE name = ‘##MS_DatabaseMasterKey##’;
1. Adım: Eski sunucuda Certificate’ı Backup Alın
BACKUP CERTIFICATE MyTDECert
TO FILE = ‘D:\Backup\MyTDECert.cer’
WITH PRIVATE KEY
(
FILE = ‘D:\Backup\MyTDECert.pvk’,
ENCRYPTION BY PASSWORD = ‘StrongPassword123!’
);
Not: INIT seçeneği ile mevcut dosyanın üzerine yazabilirsiniz (iyi alışkanlık).
2. Adım: Yeni sunucuda Certificate’ı Restore Edin
— Master veritabanında çalıştırın
USE master;
GO
— Database Master Key oluşturun (daha önce yoksa)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MasterKeyPassword123!’;
GO
— Certificate’ı geri yükleyin
CREATE CERTIFICATE MyTDECert
FROM FILE = ‘D:\Backup\MyTDECert.cer’
WITH PRIVATE KEY
(
FILE = ‘D:\Backup\MyTDECert.pvk’,
DECRYPTION BY PASSWORD = ‘StrongPassword123!’
);
GO
Not: Certificate ismi (MyTDECert) eski sunucudakiyle aynı olabilir ama zorunlu değildir. Önemli olan thumbprint’in eşleşmesidir.
3. Adım: Artık veritabanını restore edebilirsiniz
RESTORE DATABASE MyDatabase
FROM DISK = ‘D:\Backup\MyDatabase.bak’
WITH MOVE ‘MyDatabase_Data’ TO ‘E:\Data\MyDatabase.mdf’,
MOVE ‘MyDatabase_Log’ TO ‘F:\Log\MyDatabase.ldf’,
REPLACE;
Restore Sonrası Kontrol
USE YourDatabaseName;
GO
SELECT
db.name AS DatabaseName,
dek.encryption_state,
dek.percent_complete,
dek.key_algorithm,
dek.key_length,
CASE dek.encryption_state
WHEN 0 THEN ‘No encryption’
WHEN 1 THEN ‘Unencrypted’
WHEN 2 THEN ‘Encryption in progress’
WHEN 3 THEN ‘Encrypted’
WHEN 4 THEN ‘Key change in progress’
WHEN 5 THEN ‘Decryption in progress’
WHEN 6 THEN ‘Protection change in progress’
END AS EncryptionStateDescription
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.databases db ON dek.database_id = db.database_id
WHERE db.name = ‘YourDatabaseName’;
Beklenen Sonuç:
encryption_state = 3 (Encrypted)
key_algorithm ve key_length eski sunucudakiyle aynı olmalı
❗ Format Sonrası Yalnızca .bak Dosyası Kaldıysa
Eğer format attıktan sonra yalnızca .bak dosyanız kaldıysa ve TDE Certificate ile Private Key yedeği yoksa, bu veritabanını yeni bir sunucuda açmanız teknik olarak imkansızdır.
⚠️ Neden Kurtaramıyorsunuz?
TDE’nin çalışma mantığı, veriyi şifrelemek için kullanılan “anahtar” ile veritabanı dosyasını (.bak veya .mdf) birbirinden ayırmaktır.
Database Encryption Key (DEK): Veritabanı içinde saklanır, ancak Certificate ile şifrelenmiştir
Certificate & Private Key: Veritabanını açan asıl anahtardır. Bu sertifika master veritabanında saklanır ve format attığınız için silinmiştir
Elinizde sadece .bak dosyası varsa, bu şifreli bir kutudur; ancak kutuyu açacak fiziksel anahtarınız (Certificate) yoktur.
Not: Diskten .mdf/.ldf dosyaları kurtarılsa bile, aynı certificate olmadan bu dosyalar da attach edilemez. TDE şifrelemesi, dosya düzeyinde de geçerlidir.
🛠️ Kurtulmak İçin Tek Çözüm (Eğer Mümkünse)
Bu noktada tek umudunuz, format atmadan önce aldığınız
Certificate ve Private Key yedeklerini (MyTDECert.cer ve MyTDECert.pvk dosyaları) bulmaktır.
| Durum | Sonuç
|———————|—————————————————————-
| Dosyalar var ise | Yeni sunucuda Certificate geri yüklenir, restore başarılı olur
| Dosyalar yok ise | Veri kaybı kesindir. Kurtarma imkansızdır.
🚨 Bilmeniz Gereken Kritik Güvenlik Riski: Sysadmin Verilerinizi Çalabilir
Bu makaleyi yazma sebeplerimden biri de, TDE hakkında yaygın bir yanılgıyı düzeltmek.
Birçok kişi (ben de dahil) TDE’nin verileri yetkisiz erişime karşı tamamen koruduğunu düşünüyor. Ancak durum tam olarak öyle değil.
Eğer bir saldırgan SQL Server’da sysadmin yetkisi elde ederse veya sunucuya fiziksel erişimi olursa, TDE’yi aşmak çok kolaydır.
Nasıl mı? Sadece şu adımları izler:
Adım Yaptığı İşlem
1 Sertifikayı Yedekler: BACKUP CERTIFICATE …
komutuyla TDE sertifikasını ve özel anahtarını kendi belirlediği şifreyle dosyaya yedekler
2 Veritabanını Yedekler: Şifreli veritabanının bir yedeğini alır
3 Kendi Sunucusunda Açar: Kendi SQL Server’ında sertifikayı geri yükleyerek
(CREATE CERTIFICATE …) veritabanını açar ve tüm verileri düz metin olarak okur
Peki Bu Ne Anlama Geliyor?
TDE, “veritabanı dosyalarını elinde bulunduran ancak sunucuya erişimi olmayan” kişilere karşı etkilidir (örneğin, bir SAN yöneticisi veya fiziksel diski çalan hırsız)
sysadmin gibi yüksek yetkili bir kullanıcıya veya yetkilerini ele geçiren bir saldırgana karşı TDE tek başına bir koruma sağlamaz
Bu nedenle, TDE’yi bir “derinlemesine savunma” stratejisinin sadece bir katmanı olarak görmeli,
Always Encrypted gibi veriyi kullanım sırasında da koruyan yöntemlerle veya sıkı erişim denetimleriyle desteklemelisiniz.
📢 Proaktif Çözüm: TDE Aktif Olduğunda Sizi Uyaracak SQL Job
Yukarıda anlattığımız senaryoya bir daha düşmemek için,
TDE’nin hangi veritabanlarında aktif olduğunu otomatik olarak izleyen bir SQL Agent Job’u kurabilirsiniz.
Bu job:
Her sabah çalışır
TDE açık olan veritabanlarını kontrol eder
Eğer TDE aktif veritabanı varsa size e-posta gönderir
🛠️ Hazır SQL Job Script’i
— 1. Önce bir operatör oluşturun (e-posta adresinizle)
USE msdb;
GO
EXEC msdb.dbo.sp_add_operator
@name = N’TDE Uyari’,
@enabled = 1,
@email_address = N’dba@sirketiniz.com’; — Kendi e-posta adresinizle değiştirin
GO
— 2. Job’u oluşturun
EXEC dbo.sp_add_job
@job_name = N’TDE – Aktif Veritabanlarini Kontrol Et’,
@enabled = 1,
@description = N’TDE şifrelemesi aktif olan veritabanlarını listeler ve uyarı gönderir’,
@owner_login_name = N’sa’;
GO
— 3. Job’a adım ekleyin
EXEC dbo.sp_add_jobstep
@job_name = N’TDE – Aktif Veritabanlarini Kontrol Et’,
@step_name = N’TDE Kontrol Sorgusu’,
@subsystem = N’TSQL’,
@command = N’
— TDE aktif veritabanlarını bul
DECLARE @Subject NVARCHAR(255);
DECLARE @Body NVARCHAR(MAX);
SET @Subject = ”TDE RAPORU – ” + @@SERVERNAME + ” – ” + CONVERT(NVARCHAR(10), GETDATE(), 104);
SET @Body = ”Aşağıdaki veritabanlarında TDE aktiftir:”
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ ”<table border=”1″ cellpadding=”5″>”
+ ”<tr><th>Veritabanı Adı</th><th>Şifreleme Durumu</th><th>Anahtar Algoritması</th><th>Anahtar Uzunluğu</th></td>”;
SELECT @Body = @Body +
”<tr>”
+ ”<td>” + db.name + ”</td>”
+ ”<td>” +
CASE dek.encryption_state
WHEN 0 THEN ”Şifreleme yok”
WHEN 1 THEN ”Şifrelenmemiş”
WHEN 2 THEN ”Şifreleme devam ediyor”
WHEN 3 THEN ”Şifrelenmiş”
WHEN 4 THEN ”Anahtar değişimi devam ediyor”
WHEN 5 THEN ”Şifre çözme devam ediyor”
WHEN 6 THEN ”Koruma değişimi devam ediyor”
END
+ ”</td>”
+ ”<td>” + ISNULL(dek.key_algorithm, ”-”) + ”</td>”
+ ”<td>” + ISNULL(CAST(dek.key_length AS NVARCHAR), ”-”) + ”</td>”
+ ”</tr>”
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek
ON db.database_id = dek.database_id
WHERE dek.encryption_state = 3;
SET @Body = @Body + ”</table>”;
IF EXISTS (SELECT 1 FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek
ON db.database_id = dek.database_id
WHERE dek.encryption_state = 3)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”MailProfiliniz”,
@recipients = ”dba@sirketiniz.com”,
@subject = @Subject,
@body = @Body,
@body_format = ”HTML”;
END
‘,
@database_name = N’master’;
GO
— 4. Job’a zamanlama ekleyin (Her gün sabah 08:00’de)
EXEC dbo.sp_add_schedule
@schedule_name = N’Gunluk Sabah 08:00′,
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 80000;
GO
— 5. Job’u schedule ile ilişkilendirin
EXEC dbo.sp_attach_schedule
@job_name = N’TDE – Aktif Veritabanlarini Kontrol Et’,
@schedule_name = N’Gunluk Sabah 08:00′;
GO
📧 Database Mail Nasıl Kurulur?
— Database Mail’i etkinleştir
EXEC sp_configure ‘Database Mail XPs’, 1;
RECONFIGURE;
GO
— Mail profili oluştur
EXEC sysmail_add_profile_sp @profile_name = ‘MailProfiliniz’;
— Hesap ekle
EXEC sysmail_add_account_sp
@account_name = ‘SQLMailAccount’,
@email_address = ‘sqlalert@sirketiniz.com’,
@display_name = ‘SQL Server Alert’,
@mailserver_name = ‘smtp.sirketiniz.com’, — Kendi SMTP sunucunuz
@port = 25;
— Profil ile hesabı ilişkilendir
EXEC sysmail_add_profileaccount_sp
@profile_name = ‘MailProfiliniz’,
@account_name = ‘SQLMailAccount’,
@sequence_number = 1;
— Profili SQL Server’a tanıt
EXEC sysmail_add_principalprofile_sp
@profile_name = ‘MailProfiliniz’,
@principal_name = ‘public’,
@is_default = 1;
GO
🔔 Alternatif: Sadece Log’a Yazan Basit Versiyon
E-posta ayarlarıyla uğraşmak istemiyorsanız, sadece SQL Server log’una uyarı yazan job step’i:
IF EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys WHERE encryption_state = 3)
BEGIN
DECLARE @Message NVARCHAR(500);
SET @Message = ‘UYARI: TDE aktif veritabanları var! Sorgu: SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3’;
EXEC xp_logevent 50001, @Message, ‘ERROR’;
END
💡 İlerisi İçin Hayati Tavsiyeler
Bu acı tecrübeyi bir daha yaşamamak için:
| Tavsiye | Açıklama
|———————————|——————————————————————————————————
| Certificate’ları Yedekleyin | TDE açtığınız anda sertifikayı ve özel anahtarı güvenli bir dosyaya yedekleyin (`BACKUP CERTIFICATE`)
| Master DB’yi Yedekleyin | Sistem veritabanlarını (özellikle `master`) düzenli yedekleyin. `master` yedeği sertifikayı da içerir
| Parolaları Saklayın | Certificate yedeği alırken kullandığınız parolayı güvenli bir şifre kasasında tutun
📌 Özet
Anahtar olmadan şifre açılmaz. Eğer sertifika dosyalarınız yoksa, TDE’li veritabanı yedeğinizin bir anlamı kalmamıştır.
Bu rehberde öğrendikleriniz:
✅ Hatanın ne anlama geldiğini
✅ Certificate backup/restore adımlarını
✅ Format sonrası kurtarmanın imkansız olduğunu
✅ Sysadmin’in TDE’yi nasıl aşabileceğini (güvenlik açığı)
✅ TDE’yi otomatik izleyen SQL Job kurmayı
Bu makale, bir DBA’nın acı tecrübesiyle ve son dönemde hacker gruplarının kullandığı yöntemler araştırılarak hazırlanmıştır. Umarız siz bu hataları yapmazsınız.
Yazar: Özgür GÜLER – White Eagles