SQL SERVER ENCRYPTION Master Key, Database Key, Certificate, Symmetric Key BACKUP/RESTORE İşlemleri

Tekrar merhaba değerli arkadaşlar.

Bu yazımızda SQL encryption için oluşturduğumuz cetificate ve key’ler için backup/restore işlemlerinden bahsedeceğiz. Önceki yazımızda (https://www.veritabani.org/sql_server_encryption/) Column Level Encryption yönteminin avantajlarından bahsederken yetki mekanızmasına sahip olduğunu ve Backup/Restore edilebilirliğinden bahsetmiştik.

Öncelikle Service Master Key ve Database Master Key backuplarını Password kullanarak alıyoruz, ardından Certificate backup almak için hem password hem de certificate privatekey kullanmamız gerekiyor. Restore ederken ise Master Key’ ler file’dan restore ediliyor, Certificate ile SymmetricKey restore edilmiyor ilk oluşturulduğu parametler kullanılarak Create ediliyor.

Örneklerle uygulamaya geçelim;

Önceki yazımızda oluşturduğumuz encrypt yaptığımız tabloyu farklı bir veri tabanına taşıyalım ve orada encrypt halde olan veriyi görmeye çalışalım. Tabloyu verileriyle birlikte taşımak için Export/Import veya Generate Script kullanabiliriz. VERITABANIORG veri tabanındaki TEST_MT tablosunu verileriyle birlikte MTTESTDB veri tabanına aktaralım. Generate Script kullandım;

use MTTESTDB
go
CREATE TABLE [dbo].[TEST_MT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KullaniciAd] [varchar](30) NULL,
[KullaniciSifre] [varchar](30) NULL,
[KullaniciSifre_HASHBYTES] [varbinary](32) NULL,
[KullaniciSifre_Encrypt] [varbinary](8000) NULL,
[KullaniciSifre_Encrypt2] [varbinary](8000) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TEST_MT] ON 
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (1, N'Teoman', N'bc220209', 0x0045C5BBE16C5CA3CC46EF692773949802000000B4EC92EAEEC7AA129351B54654E45CADF9C9280ADDAD6400DD755AEF2E4E5799166A14948AAE90381F895B01BFF2C300)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (2, N'Metehan', N'bc209174', 0x0045C5BBE16C5CA3CC46EF6927739498020000001872807DC22F5184F2696C503752930D768C512670B0E050F29D9B9E34189BC0471974B470E84FD716C6E360608ED914)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (3, N'Balamir', N'ce375469', 0x0045C5BBE16C5CA3CC46EF692773949802000000D75D8739794F96EF9658316A3BBCDA24A3CD25360EDA2C212E32465504F64639F9ABF7074BC060B5939CEC09A1A86E90)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (4, N'Aksuvar', N'ce440710', 0x0045C5BBE16C5CA3CC46EF69277394980200000040F1D9023867E2A60BB256B840B14B221FBFD0A3F12BFC606EA67684E932B2DB44DE6A6C83572F69067CB56FECAC16CC)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (5, N'Bumin', N'ce552745', 0x0045C5BBE16C5CA3CC46EF692773949802000000D5E2C9F3D4B301E7914EAF500250FF0DCAF20149FC46BA60193CF7A345E5C3BD68487818D56FB4A7F9D5F9E7741EECB4)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (6, N'Bilge', N'ce744847', 0x0045C5BBE16C5CA3CC46EF6927739498020000002E419070964BCB00F3EC4D65230C88584D4F2EFF590B0981912DA2577267CB48476398ACF7612E7FDD5E5F58333CFFF0)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (7, N'Kadir', N'ce8401212', 0x0045C5BBE16C5CA3CC46EF69277394980200000021285AB72DE5CEF52BAB00FC86C7BFEE34EA2D068AF8C50F8E300F6091F42150B4EF45A0622AF61E2302100B28A40DCC)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (8, N'Alptekin', N'ce9621183', 0x0045C5BBE16C5CA3CC46EF692773949802000000ECD69E1F283B6E8311D12502F0B8CB596D7A98C60573B0063AAF4FD04F26E1FB5CF9D66C1CB9FB4E0D7C1351DFC6007A)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (9, N'Tugrul', N'ce10401157', 0x0045C5BBE16C5CA3CC46EF69277394980200000020CC53D723D88830333DC4A25F01B1C28C931788DCAD9F685FAB2858DE2BFB9A67725F92231F47EBF5FF47F87E20834A)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (10, N'Timur', N'ce13681501', 0x0045C5BBE16C5CA3CC46EF692773949802000000E662A244B4D47B219E04A012EC6443C26FD57AF9C4360926CDC2C45CF4D43A8F175D5EFA9376F0CBE13CBF3423621191)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (11, N'Atilla', N'ce395453', 0x0045C5BBE16C5CA3CC46EF69277394980200000016B2638080ED57C3D51D7181ACB9AEDE1B5A2A1EDA3441718F4F63342FC977BBD11308AE44D0D2F7A627D8E8C1380C39)
GO
INSERT [dbo].[TEST_MT] ([ID], [KullaniciAd], [KullaniciSifre], [KullaniciSifre_Encrypt2]) VALUES (12, N'Fatih', N'145314531453', 0x0045C5BBE16C5CA3CC46EF692773949802000000D7E4B832F4B275E864AF24EB2AF6D260A32AC0F744079B3F55F2F5D5C377A6E314FD375C61265CE42CB4C70C066B4C17)

Yeni veri tabanındaki tablomuza bakalım;

MTTESTDB veri tabanında herhangi bir certificate ve key olup olmadığını kontrol edelim, aşağıdaki görselde anlaşlacağı üzere herhangi bir certificate veya key bulunmuyor.

Certificate ve key’ler olmadığı için Open yapamayacağız, bu sebeple tablodaki encrypt alanı çözemeyeceğiz ve aşağıdaki görselde görüldüğü üzere NULL değerler olarak karşımıza çıkacaktır.

Şimdi VERITABANIORG veri tabanında bulunan Certificate ve Key’ lerin Backup’larını alalım ve MTTESTDB veri tabanında oluşturalım. Ardından tablomuzdaki encrypt’i çözelim ;

BACKUP VE RESTORE

--BACKUP MASTER KEY
use VERITABANIORG
go
OPEN master key decryption by password='Parola.,MTuran!'
backup master key to file='D:\BACKUP\EncryptionBackup\VERITABANIORG_MasterKey' encryption by password='Parola.,MTuran!'

--BACKUP CERTIFICATE KEY (TAVSİYE EDİLEN BUDUR)
use VERITABANIORG
go
backup certificate Certificate_MTuran
to file='D:\BACKUP\EncryptionBackup\VERITABANIORG_Certificate.crt'
with private key (file='D:\BACKUP\EncryptionBackup\VERITABANIORG_Cert_PriKey.pvk', encryption by password='Parola.,MTuran!')

Aldığımız bu backup’ların dosyalarını görelim;

Şimdi aldığımız backuplar ile MTTESTDB veri tabanına restore yapalım;

--RESTORE MASTER KEY
use MTTESTDB
go
--OPEN master key decryption by password='Parola.,MTuran!'
restore master key FROM file='D:\BACKUP\EncryptionBackup\VERITABANIORG_MasterKey' 
decryption by password='Parola.,MTuran!'
encryption by password='Parola.,MTuran!'




-- CERTIFICATE KEY (CERTIFICATE RESTORE YAPILAMIYOR BU YÜZDEN BACKUP KULLANARAK TEKRAR CREAT EDİYORUZ !!!!!!!!!!!!!) (önce master key open yapılmalı)
OPEN master key decryption by password='Parola.,MTuran!'


--Create CERTIFICATE KEY (TAVSİYE EDİLEN BUDUR ÇALIŞIYOR)
CREATE certificate Certificate_MTuran
from file='D:\BACKUP\EncryptionBackup\VERITABANIORG_Certificate.crt'
with private key (file='D:\BACKUP\EncryptionBackup\VERITABANIORG_Cert_PriKey.pvk', decryption by password='Parola.,MTuran!')

--Symmetric Key Oluştur (Certificate den sonra)
CREATE SYMMETRIC KEY Symmetric_MTuran WITH
ALGORITHM = AES_256,
IDENTITY_VALUE='Symmetric_MTuran',
KEY_SOURCE='E78BBC00-139C-4370-9FC5-0A4ED1451451' --MasterKey'in key_guid alanını kullandım
ENCRYPTION BY CERTIFICATE Certificate_MTuran;

MTTESTDB veri tabanında restore ile create ettiğimiz certificate ve key’leri görelim;

Şimdi encrypt alanı çözmeye çalışalım;

OPEN master key decryption by password='Parola.,MTuran!'
OPEN SYMMETRIC KEY Symmetric_MTuran DECRYPTION BY CERTIFICATE Certificate_MTuran
go
--Decrypt
select 
ID, 
KullaniciAd, 
KullaniciSifre, 
KullaniciSifre_Encrypt2 ,
convert (varchar(max),DECRYPTBYKEY(KullaniciSifre_Encrypt2)) as KullaniciSifre_Decrypt 
from MTTESTDB..TEST_MT

Aşağıdaki görselde görüldüğü üzere TEST_MT tablomuzdaki encrypted olan alanı decrypt edebildik;

NOT:  certificate veya symmetric key ‘ i silmek istediğimizde aşağıdaki komutu kullanabiliriz. Veya daha basit sağ tık delete yapabiliriz 😊

DROP SYMMETRIC KEY Symmetric_MTuran
drop certificate Certificate_MTuran

Okuduğunuz için teşekkür ederim görüşmek üzere..