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] […]

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..

 

Benzer Yazılar

SQL SERVER RIGHT-LEFT PARTITION

SQL Server 5 gün önce

SQL Server’da partitioning, büyük veritabanı tablolarını daha yönetilebilir ve performanslı hale getirmek amacıyla kullanılan bir tekniktir. Bu teknik, tablonun verilerini fiziksel olarak değil, mantıksal olarak parçalara ayırır. Veriler, belirli bir partition function ve partition scheme kullanılarak farklı bölümlere yönlendirilir. Partitioning, özellikle büyük veri kümeleriyle çalışan veri tabanlarında sorgu performansını artırır ve veri yönetimini kolaylaştırır. Partition Function ve Partition Scheme nedir? Partition Function: Verilerin hangi kriterlere göre bölüneceğini belirler. Örneğin, bir tarih aralığına göre verileri ayırmak. Partition Scheme: Verilerin hangi filegroup’larda depolanacağını belirler.   Örnek olarak Range LEFT ve Range RIGHT olmak üzere iki ayrı tabloda partition nasıl yapılır sizlere göstereceğim. İlk olarak Range LEFT olan partition yapısından başlayacağım. Öncelikle Veri tabanıma yeni filegroup ve file ekliyorum Şimdi sıra FUNCTION ve SCHEME oluşturmakta ben tablomu yıllık olarak partition yaptım sizler ihtiyaçlarınız doğrultusunda aylık,günlük vs yapabilirsiniz. LEFT partition dediğimiz olay vermiş olduğunuz tarih aralığına eşit bir veri geldiğinde bu veriyi solundaki partition […]

Veritabanı Recovery Pending Durumu ve Düzeltme Seçenekleri

SQL Server 2 hafta önce

İçindekilerVeritabanı Recovery Pending Durumu Nedir?Veritabanı Neden Recover Pending Duruma Düşer?Recovery Pending Durumu Nasıl Çözülür?SonuçKaynaklarVeritabanı Recovery Pending Durumu Nedir? SQL Server’da veritabanları bazı nedenlere bağlı olarak “Recovery Pending” (Kurtarma Bekleme)  moduna geçebilir. Veritabanın düzgün bir şekilde kapatılmaması, eksik veya bozuk log dosyaları, disk depolama sorunları, sistemde yaşanan anormal şekilde çökmeler veya MS SQL Server’daki hatalar bu duruma sebep olabilir. Recovery durumu, aslında veritabanını tekrar kullanılabilir hale getirmek için bir kurtarma işlemi yürüttüğünü ifade eder ve üç aşamadan oluşur; Analysis (Analiz): Transaction log incelemesi yapılması ve işlemlerin tamamlanma (Commit) durumunun kontrol edilmesi, Redo (Yeniden İşleme): Tamamlanmış (Commit) ancak henüz diske yazılamamış olan işlemlerin yeniden işlenmesi, Undo (Geri Alma): Başlamış (Begin) ancak tamamlanmamış (Commit) işlemlerin  geri alınmasıdır.   Veritabanı Neden Recover Pending Duruma Düşer? SQL Server Restart Süreci SQL Server servisi restart edildiğinde üzerinde bulunan tüm veritabanları tutarlılığın sağlanması için otomatik olarak recovery moduna girer ve redo/undo işlemleri sürecince devam eder. Ani Sistem […]

SQL Server DMV ve DMF – 6

SQL Server 2 hafta önce

Bu yazımızda DMV ve DMF Serimizin 6.sına devam edeceğiz. Bir önceki seride Memory’ye ilişkin DMV ve DMF’leri ele almıştık. Bu yazıda Memory konusunda devam edeceğiz. SQL server’da Memory kavramı en önemli kavramlardan biridir. Özellikle tüm transaction işlemlerinin önce Buffer sonra disk üzerinden devam ettiğini düşünürsek buffer’ın oynadığı kritik rolü daha iyi anlayabiliriz. Bu yazıda Memory’nin durumunu ve monitör edilmesine bakacağız. Özellikle Performans sorunlarında memory konusunda sorun yaşandığı durumda nasıl okumak gerektiği önemli rol oynamaktadır. Hangi database’de, hangi tablo’da sorun yaşandığına ilişkin bilgilere bu paylaşım sonrasında görebileceğiz. SQL Server’ın Memory kullanım durumunu incelediğimde; select physical_memory_in_use_kb/1048576.0 AS ‘physical_memory_in_use (GB)’, locked_page_allocations_kb/1048576.0 AS ‘locked_page_allocations (GB)’, virtual_address_space_committed_kb/1048576.0 AS ‘virtual_address_space_committed (GB)’, available_commit_limit_kb/1048576.0 AS ‘available_commit_limit (GB)’, page_fault_count as ‘page_fault_count’ from  sys.dm_os_process_memory; Görsel – 1   Physical_memory_in_use: Kullanımda olan Fiziksel Memory miktarını gösterir. locked_page_allocations: Memory’de lock’lanmış olan Page’lerin miktarını belirtir. virtual_address_space_contained: SQL Server VAS(Virtual Adress Space) için ayrılan miktarı belirtir. available_commit_limit: SQL Server tarafından kullanılabilecek Memory Miktarını gösterir. […]

1 Yorum

  • Ömer AKKÖK 20 Aralık 2023

    Merhaba,
    Konunun ne olduğunu en başta daya açık bir şekilde belirtebilirseniz daha anlaşılır olacaktır. encrypt alanın cryptosunu çözüp farklı bir columnda açma gibi mesela. Faydalı bir yazı olmuş elinize sağlık.

    Saygılarımla.

Yorum Yaz

Rastgele