SQL Server TDE: Certificate Loss, Master Key & Emergency Database Recovery
Emergency recovery for encrypted databases failing to open (Error 15581, 33111, RECOVERY_PENDING) due to missing TDE certificates or Master Keys: private key restoration and Azure Key Vault EKM protocols.
Operational Safety & Risk Notice
This runbook contains operational intervention steps; several are write-impacting (KILL, terminate, DROP, RESIZE). Verify in a staging environment first, and apply sequentially in production with verification.
1. Symptom & Signals
- Restore veya sunucu taşıma/rebuild sonrası veritabanı `RECOVERY_PENDING` durumunda kalır ve açılamaz.
- SQL Server ERRORLOG'da kritik hata kayıtları:
- `Msg 15581, Level 16, State 1: Please create a master key in the database or open the master key in the session before performing this operation.`
- `Msg 33111, Level 16, State 3: Cannot find server certificate with thumbprint '0x...'.`
- `sys.dm_database_encryption_keys` üzerinde `encryption_state = 1` veya certificate bağlantısı kopuk.
2. Business & Technical Impact
P1 - Kritik Kesinti. Veritabanı online olamaz, tüm kullanıcı ve uygulama erişimi durur. TDE sertifikası ve private key yedeği yoksa şifrelenmiş veri kalıcı olarak kayıptır (irreversible data loss).
3. Diagnosis & Root-Cause Analysis
EXEC sp_readerrorlog 0, 1, 'certificate';SELECT db.name, db.is_encrypted, dek.encryption_state, dek.percent_complete,
dek.encryptor_thumbprint, c.name AS cert_name
FROM sys.databases db
JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id
LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
WHERE db.is_encrypted = 1;SELECT name, pvt_key_encryption_type_desc, thumbprint, expiry_date
FROM sys.certificates
WHERE name LIKE 'TDE%' OR thumbprint IS NOT NULL;SELECT name, is_master_key_encrypted_by_server
FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##';- **Senaryo A (En Yaygın):** Yeni sunucuya restore edilmiş, TDE sertifikası/private key taşınmamış (Error 33111).
- **Senaryo B:** Master Key bozulmuş veya sunucu yeniden kurulmuş (`Error 15581`).
- **Senaryo C:** Sertifika süresi dolmuş veya Azure Key Vault (AKV) EKM bağlantısı kopmuş.
4. Remediation & Action Plan
### ALTIN KURAL: SERTİFİKA YEDEĞİ YOKSA GERİ DÖNÜŞ YOKTUR!
- Asla mevcut sertifikayı silmeyin (`DROP CERTIFICATE`).
- Bu prosedür yalnızca elinizde sertifika (`.cer`) ve private key (`.pvk`) yedeği varsa çalışır.
---
### Müdahale A: Başka Sunucudan Restore Sonrası TDE DB Açılmıyor (En Yaygın)
Elinizde olması gereken dosyalar: `TDECert_Backup.cer`, `TDECert_Backup.pvk` ve private key şifresi.
-- 1. Yeni sunucuda Database Master Key oluşturun (yoksa)
USE master;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'GucluSifre123!_MasterKey_2026';
BACKUP MASTER KEY TO FILE = 'D:\TDE_Backup\MasterKey.key'
ENCRYPTION BY PASSWORD = 'GucluSifre123!_Backup_2026';
END
-- 2. Sertifikayı private key ile birlikte restore edin
CREATE CERTIFICATE TDECert_MyDB
FROM FILE = 'D:\TDE_Backup\TDECert_MyDB.cer'
WITH PRIVATE KEY (
FILE = 'D:\TDE_Backup\TDECert_MyDB.pvk',
DECRYPTION BY PASSWORD = 'GucluSifre123!_CertPrivKey_2026'
);
-- 3. Thumbprint doğrulaması yapın
SELECT name, thumbprint FROM sys.certificates WHERE name = 'TDECert_MyDB';
-- 4. Veritabanını kurtarın veya restore işlemini tamamlayın
RESTORE DATABASE MyDB_TDE FROM DISK = 'D:\Backups\MyDB_TDE.bak' WITH RECOVERY;
-- veya ALTER DATABASE MyDB_TDE SET ONLINE;---
### Müdahale B: Sertifika Süresi Doldu / Güvenli Yenileme
TDE sertifikası expire olsa bile mevcut veriler okunur, ancak veritabanı şifreleme anahtarı (DEK) yeniden şifrelenemez.
-- 1. Yeni sertifika oluşturun
USE master;
CREATE CERTIFICATE TDECert_MyDB_New
WITH SUBJECT = 'TDE Cert Renewed 2026-2028',
EXPIRY_DATE = '2028-11-05';
-- 2. Derhal yedekleyin!
BACKUP CERTIFICATE TDECert_MyDB_New
TO FILE = 'D:\TDE_Backup\TDECert_MyDB_New.cer'
WITH PRIVATE KEY (
FILE = 'D:\TDE_Backup\TDECert_MyDB_New.pvk',
ENCRYPTION BY PASSWORD = 'GucluSifre123!_New_2026'
);
-- 3. Veritabanı şifreleme anahtarını yeni sertifikaya geçirin
USE MyDB_TDE;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert_MyDB_New;
-- 4. Eski sertifikayı HEMEN SİLMEYİN! Eski transaction log yedekleri eski sertifikaya ihtiyaç duyar.
-- En az 90 gün sonra: DROP CERTIFICATE TDECert_MyDB_Old;---
### Müdahale C: Azure Key Vault / EKM ile Modern TDE
-- Key Vault bağlantısını kontrol edin
SELECT * FROM sys.dm_azure_key_vault_keys;
-- Asymmetric key'i yeniden oluşturun
CREATE ASYMMETRIC KEY TDE_AK_MyDB
FROM PROVIDER AzureKeyVault_EKM
WITH PROVIDER_KEY_NAME = 'TDEKey-MyDB', CREATION_DISPOSITION = OPEN_EXISTING;5. Verification & Health Checks
- `SELECT name, state_desc, is_encrypted FROM sys.databases WHERE name = 'MyDB_TDE';` -> `ONLINE`, `is_encrypted = 1`.
- `SELECT DB_NAME(database_id), encryption_state_desc FROM sys.dm_database_encryption_keys;` -> `encryption_state = 3 (Encrypted)`.
- Uygulama oturum açma ve okuma/yazma sorguları başarıyla çalışıyor.
6. Rollback & Contingency Plan
- Master key şifresi değiştirildiyse `RESTORE MASTER KEY` ile önceki anahtara dönün.
- Sertifika yenilemede sorun yaşanırsa `ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE TDECert_MyDB_Old;` ile eski geçerli sertifikaya dönün.
- **Önleme Checklist — dbaguide Standardı:**
- [ ] Her TDE sertifikası için 3 kopya yedek: 1x On-prem güvenli paylaşım, 1x Azure/HashiCorp Key Vault, 1x Çevrimdışı USB/Kasa.
- [ ] `BACKUP CERTIFICATE` rutin SQL Agent job'ı aktif mi?
- [ ] Sertifika expiry alarmı: `DATEDIFF(day, GETDATE(), expiry_date) < 90` gün için otomatik alert kurulu mu?
- [ ] Her çeyrekte izole test ortamına TDE restore tatbikatı yapılıyor mu?
Engineering Triad: Diagnostic Scripts
Diagnostic scripts recommended during this runbook triage to pinpoint the root cause:
SQL Server Last Backup Status and RPO Check
Shows the latest full, differential and log backup per database. Quickly finds databases without recent backups or log backups.
List Sysadmin and Risky Logins in SQL Server
Finds logins in the sysadmin role, SQL logins without password policy, and accounts with blank passwords. A baseline check for security audits.