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.
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.
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,
Encryption_State_Desc
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.
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
- 1. adım
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Parola01';
GO
- 2. adım
CREATE CERTIFICATE sertifika0
WITH SUBJECT = 'test sertifikam';
- 3. adım
BACKUP CERTIFICATE sertifika0 TO FILE = 'C:\SERTIFIKAA\sertifika'
WITH PRIVATE KEY ( FILE = 'C:\SERTIFIKAA\sertifikaPrivateKey' ,
ENCRYPTION BY PASSWORD = 'Elazig24+' );
- 4. adım
USE SIFRELI
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE sertifika;
GO
- 5. adım
Select DB_NAME(database_id) As Database_Name,--Encryption_State,
Encryption_State_Desc
FROM sys.dm_database_encryption_keys
GO
SELECT name, is_encrypted
FROM sys.databases
Go
EK BİLGİ
Tempdb veritabanından şifrelemeyi de kaldırmak için SQL Server hizmetini yeniden başlatabilirsiniz. SQL Server hizmetini yeniden başlattım ve ardından Şifrelemesi durumunu yeniden kontrol ettiğimde temp db yi 1 olduğunu görmüş oldum en sonda temp db tek kalınca bu değer gözüküyor.
Encryption state’in 1 olması bekleniyor veritabanı boyutuna göre bekleme süresi değişir.
Always on sistemlerde primaryde yapılan değişiklikler secondary sunucusundada yapılıyor. Tabi ikinci sunucudada sertifika ve master key varsa.
AlwaysON üzerinde bulunan veri tabanları encrypt edildiği zaman, aynı sertifikanın bulunması halinde secondary sunucu da otomatik olarak encrypt edilmiş olur.
Primaryde encrytionları kapattıktan sonra secondary’de de aynı işlemlerimi yapmam lazım.
Veritabanı encyrption işlemi aktif hale getirilir. Yukarıdaki yazımızda da yaptık yoksa 1 değeri dönüyor.
ALTER DATABASE SIFRELI SET ENCRYPTION ON;
Bu noktada hem mdf-ldf hem de yedekler parola korumalı hale geldi.
MASTER KEY YEDEKLEME İSLEMİ(bence gerek yok)
USE master
BACKUP MASTER KEY TO FILE ='C:\SERTIFIKAA\sertifika_MasterKey'
ENCRYPTION BY PASSWORD = 'XXXX'Başka bir makalede görüşmek üzere.
“De ki: Ey Rabbim! İlmimi artır.” Taha-114
