SQL Server 2014 Yenilikleri - 1 (Hybrid Cloud)

CTP2 versiyonu yayında olan SQL Server 2014 ile birlikte çeşitli konularda birçok yenilik geldi. Microsoft geçtiğimiz günlerde yenilikleri duyurma hızını arttırabilmek amacıyla US’e davet ettiği MCT’lerle Train-the-Trainer eğitimlerini de düzenledi. Bu yenilikler son halini almamış olsa da artık yetenek olarak olgunlaştı ve üzerinde tartışılabilir, paylaşılabilir hale geldi diyebiliriz. Ürün henüz piyasaya sürülmemiş olsa da yenilikleri takip etmek isteyeceğinizi düşündüm. İleride tamamı üzerinde yazılarımın olacağı birkaç başlıkta bunları inceleyeceğiz. Bu bölümde ise Hybrid Cloud başlığına yer veriyor olacağım.

Son zamanlarda bulut(Cloud) ile ilgili çalışmalar ciddi manada hız kazandı ve birçok firma bu konuda çözümler üretmeye karar verdi. Firmalar müşterilerinin çeşitli alt başlıklardaki teknoloji ihtiyaçlarını kendi veri merkezlerinde(Datacenter) çözümleyip hizmet olarak sunmakta. Bu durumun getirileri genel olarak şöyle; kullandığın kadar ödeyebilme, donanım yatırımıyla ilgilenme ihtiyacının tamamen ortadan kalkması(güncelleme, bakım, yedekleme, uzman vs.), sistemin sürekli ayakta kalabilmesi için ve yüksek erişilebilirlik için ekstra çaba sarf etmeye gerek kalmaması(donanım, yazılım, uzman vs.).

Microsoft’un bu kapsamdaki Windows Azure olarak bilinen çözümü son güncellemelerle oldukça yetenekli hale geldi. Windows Azure’u test etmek isterseniz http://www.windowsazure.com/en-us/  sitesi üzerinden bir deneme hesabı açabilirsiniz.

Windows Azure’un uygulama, altyapı ve veri konusundaki hizmetleri dünya üzerine dağılmış çeşitli datacenterlar sayesinde sanal makinelerden web sitelerine, karmaşık ve uzun işlemleri hızla yapan süper bilgisayarlardan kolay ve sürekli erişilebilen veritabanlarına, sınırsız storageden mobil servicelere kadar birçok ihtiyaca cevap verebilmektedir. 

Windows Azure hakkında eğitici dokümanların sayısı giderek artmakta ve daha da açıklayıcı hale gelmekte. Bu sebeple direk SQL Server tarafını ilgilendiren kısma, yani Data Services (Veri Hizmetleri) kısmına geçebiliriz. Bu durumda şöyle bir manzara ile karşılaşıyoruz:

Bu görünümdeki henüz gelişmekte olan Big Data ve NoSQL ile ilgili HDInsight, Tables çözümlerini başka bir yazımızda incelemek üzere geride bırakalım istiyorum.

Relational kısımdaki SQL Database başlığı SQL Azure’u işaret etmektedir. Hem bir web arayüzü(Silverlight) ile hem de SSMS ile bağlanılıp yönetilebilen SQL Azure hizmeti ilk haline göre yetenek olarak biraz daha gelişti. Fakat asıl yenilikler Blob Storage ve SQL Server in a VM hizmetleri olduğu için bu yazımızda odaklanacağımız kısımlar da bunlar olacak.

Şimdi gelelim Hybrid Cloud başlığı altındaki yeniliklere;

1-      1-Blob Storage :

a.       Yedekleme Çözümü 

Bir storage hizmetidir. Basitçe bir disk kiralamış oluyoruz. Her hangi bir veriyi; resim, müzik, video, backup, database dosyaları vs. klasörleyerek tutabiliyoruz. Konsept biraz farklı olduğu için Azuredaki bir Storage üzerinde klasörler, Container lar ile temsil edilmektedir.

https://manage.windowsazure.com/ üzerinden Windows Azure’a bağlandıktan sonra soldaki STORAGE menüsü yardımıyla bulutdepo adında bir alanı birkaç dakika içerisinde oluşturabiliyoruz. Oluşturma esnasında storagein replicate olup olmayacağı ve dünya üzerinde hangi datacenter grubunda konumlanacağını belirtebiliyoruz.

Kısa sürede oluşan Storage üzerinde monitörleme yapabilir, yazma okuma ve replikasyon gibi ayarları değiştirebilirsiniz. Storage içerisinde policyler yardımıyla erişim seviyesini, süresini vs. yönetebileceğiniz CONTAINER’lar oluşturarak yüklemek istediğiniz nesneleri klasörleyebilirsiniz. Ben de örneğimde bulutdepo storagei altında http://bulutdepo.blob.core.windows.net/yedekler URLi ile erişebileceğim yedekler isimli bir Container oluşturdum.

Containerlara silverlight arayüzünden bir şey upload edilemiyor fakat download edilebiliyor. Eğer bu storagei bir skydrive mantığında kullanmak isterseniz veya storagee erişim sınırlamaları getirmek için kolayca policyler tanımlamak isterseniz Azure Storage Explorer uygulamasını şuradan http://azurestorageexplorer.codeplex.com/ indirip kurmanız işinizi kolaylaştırır. Aksi halde birçok ihtiyaca binaen geliştirilen yaklaşık 5mblık bu küçük uygulamanın yaptıklarını kodlarla yapmanız gerekecek.

Azure Storage Explorer’ı açtıktan sonra Add Account butonu ile Storage Accountuzu ekleyip solda containerları listeleyebilir, dosya upload-download edebilir bunları ön izleyebilirsiniz.

Ayrıca bu uygulama yardımıyla storagee kısıtlı erişim sağlanabilmesi için policyler(Shared Access Policies) oluşturabilir yazma, okuma ve silme haklarını verip-engelleyebilir hatta bunun için bir tarih aralığı belirleyebilirsiniz.

Bu durumda birazdan geleceğimiz kısımla ilgili olarak Credential oluştururken veya storagee bağlanılmak istenen herhangi bir durumda Access Key veya Storage Account Key alanında yukarıdaki resimde görünen Generate Signature ile üretilmiş URLdeki soru işaretinin sağında kalan kısım kullanılmalıdır.

Peki, storagelere bağlanan daha entegre bir araç yok mu?

Var. SSMS storage bağlanabiliyor ve containerlar içerisindeki dosyaları listeleyebiliyor. Ancak CTP2 versiyonundayız ve şimdilik burada dosyalar için upload-download veya ön izleme gibi bir özellik yok sadece silme ve listeleme özelliği var.

Peki, Azure Storage hangi amaçlar için kullanılabilir?

En başta Skydrive mantığında dosya deposu olarak kullanabiliriz. Veritabanı bakış açısıyla olaya yaklaşırsak. SQL Server için disk ne ise Azure Storagede o diyebiliriz. Yani üzerine backuplar alabilir, data ve log dosyalarını tutabiliriz.

Azure Storage DR(Disaster Recovery) çerçevesinde güvenli, yedekli, uzak ve sürekli erişilebilir kopyalar gereksinimine oldukça mantıklı bir cevap olarak duruyor. Buraya yedeklemenin rahatça yapılabilmesi için klasik backup seçeneklerine ek olarak URL Backup opsiyonu da geldi. URL backup opsiyonunu tercih ettiğinizde ise dilerseniz Encryption Backup seçeneğini kullanabilirsiniz. Bu sayade backuplarınızı AES 128, AES 192, AES 256 ve Triple DES algoritmaları ile şifreleyerek bulutta güven altında tutabilirsiniz.

Azure Storage üzerine backup nasıl alınır?

Öncelikle bir Credential oluşturmamız gerekiyor. Credential SQL Server’ın dışındaki bir kaynağa erişmesi için kullandığı bir güvenlik nesnesidir.

Azure Storage için Credential oluşturmanın birkaç yolu var. Bu işi backup öncesinde yapmak istiyorsak SQL script veya klasik olarak SSMS’daki Object Explorer penceresinden Security/Credentials/New Credential … seçeneğini kullanarak ara yüz yardımıyla yapabiliriz. Backup için gereken ayarları yaptığımız kısımda oluşturmak istiyorsak şu linkten https://manage.windowsazure.com/publishsettings/Index?client=vs&SchemaVersion=1.0 indireceğimiz publishsettings uzantılı dosyayı kullanabiliriz. Bu dosyayı Get-AzurePublishSettingsFile PowerShell komutu ile de çağırıp indirebiliriz.

Script ve arayüz ile şu şekilde credential oluşturulabilir.

(Not: Credential oluştururken Credentail Name kısmına https ile başlayacak şekilde container urlinin verilmesi policy ile erişim gerektiren durumlarda zorunludur.)

Credential oluşturduktan sonra şu şekilde script veya arayüz yardımıyla backuplar alınarak Azure Storagee gönderilebilir.

İstenirse backup alınırken arayüzdeki SQL Credential karşısında bulunan Create… butonu kullanılarak indirilen publishsetting dosyası yardımıyla da credential bilgisi üretilebilir.

Yeni URL backup özelliği ile backup alma işleminin otomatize edilebilmesi ve yönetilebilmesi için msdb veritabanı altında bulabileceğiniz smart_admin ile başlayan yeni fonksiyon ve procedureler işlerinizi oldukça kolaylaştıracaktır.

Eğer Azure Storagei DR site olarak kabul ettiyseniz ve SQL Server 2014 öncesi backuplarınızı da burada tutmak isterseniz az önce bahsi geçen Azure Storage Explorer uygulamasındaki upload seçeneğini kullanabileceğiniz gibi http://www.microsoft.com/en-us/download/details.aspx?id=40740 linkinden indirebileceğiniz bu iş için yapılmış Windows Azure Tools isimli aracı da kullanabilirsiniz. Basitçe neredeki backupları nereye alacağını belirten bir kural ekleyerek alınan backupların yakalanıp Azure Storagee upload edilmesini sağlayabilirsiniz.

Peki, restore işlemini nasıl gerçekleştireceğiz?

Aşağıdaki gibi script veya arayüz yardımıyla restore işlemini gerçekleştirebilirsiniz.

b.       Veritabanı Lokasyonu

Azure Storagei backup/restore lokasyonu dışında data ve log dosyalarını tutabileceğimiz bir alan olarak da kullanabiliriz. Bu durumda SQL Server servisi bulut dışında çalışırken data ve log dosyaları bulutta yüksek erişim konusunda daha güvenli bir yerde durabilir. Data ve Log dosyalarının bulutta durmasının bilgi güvenliği bakımından riskli olduğunu düşünüyorsanız veritabanınızı TDE(Transparent Data Encryption) ile şifreleyip anahtarın sizde şifreli dosyaların bulutta durmasını sağlayabilirsiniz.

Bulutta data ve log dosyası create etmek için şu şekilde;

Var olan dosyaları servera attach etmek için şu şekilde;

Kodlama yapabilirsiniz veya klasik ara yüzleri kullanabilirsiniz. Tabi ki bunu yapabilmek için öncelikle policy tabanlı erişimi sağlayacak bir Credential oluşturmanız gerekir.

Policy oluşturmak için Azure Storage Explorer aracındaki Security butonunu kullanabiliriz. Öncelikle Shared Access Policies tabında bir policy tanımlayıp sonra Shared Access Signatures tabında bir Signature üretmemiz gerekir.

Hemen sonrasında buradaki bilgileri kullanarak yukarıdaki script ile bir Credential oluşturabiliriz. Credentialı başarıyla oluşturduğumuzda data ve log dosyalarımızı bahsettiğimiz arayüz veya scriptleri kullanarak bulutta tutabiliriz.

Hybrid Cloud başlığı altında inceleyeceğimiz bir diğer yenilik ise buluttaki bir sanal makine içerisinde konumlayabileceğimiz SQL Server kurulumu ile çalışabilme özelliğidir.

1-      2-SQL Server in a Windows Azure VM:

Windows Azure hizmetlerinden birisi de dakikalar içerisinde bir sanal makine kurulumunu edinebilmeniz ve bu makineye Remote Desktop ile erişip yönetebilmeniz ile ilgili.

CENTOS’tan ORACLE, SUSE’dan MS SQL Server’a farklı VM kurulumlarını bünyesinde barındıran Windows Azure erişilebilirlik, donanım, bakım gibi konuları üstlenmiş ve garanti altına almıştır.

Ancak sonuç itibari ile oluşan şey bir sanal makinedir. Bu noktada bizim ilgimizi çeken kısım içerisine kurduğumuz SQL Server ile yapabileceklerimiz oluyor.

a.       a-Cloud DR

VM içerisindeki bu SQL Server’ı On-Primeses(bulutta olmayan) serverlarımızla bir arada High Availability kapsamına alabiliriz. Yani read-write komutlarını gönderildiği primary sunucu localde, replica bulut üzerinde bulunabilir veya tam tersi kurulum gerçekleştirilebilir.

Bu bağlamda SQL Server 2014’ün AlwaysOn konusundaki yeniliklerinden birisi de bulutta bir replica oluşturabilmektir.

a.       b-Database deploye to SQL Server in a VM

Eğer lokaldeki bir veritabanını artık bulutta barındırdığınız bir VM içerisindeki SQL Server’a deploy etmek istiyorsanız SSMS üzerinde entegre bir çözüm de mevcut. Aşağıdaki gibi Task/Deploy Database to a Windows Azure VM… seçeneği ile deploy işlemini gerçekleştirebilirsiniz.

Bunun için Windows Azure üzerinde port açmalı ve VM’in Cloud Adapter servisinin açık olduğundan emin olmalısınız. Test etmek isteyenler CTP2 versiyonunun bu konuyu içeren hotfixini uygulamaları gerekecek. Hotfixi şu linkten temin edebilirsiniz http://support.microsoft.com/kb/2907705/en-us.

Cloud ile ilgili olarak Big Data konusu haricinde veritabanı bakış acısı ile neredeyse gelen tüm yeniliklere değinmiş olduk.

Bulut konusunda verdiğim eğitimlerdeki katılımcıların ortak fikri ileride bu teknolojinin çok daha yaygın olacağı yönünde olsa da günümüzde bazı çekinceleri olduğunu gizlemiyorlar. Bu çekincelerden en çok duyduklarım internet alt yapısı ve yurt dışına veri çıkarma konusundaki kanunlar olmuştur. Belki de bu çekinceler size hitap etmiyordur ve bulut tam aradığınız ucuz bir çözümdür.

Big Data konusunda tekrar dönmek kaydıyla şimdilik bulut ile ilgili olarak bu kadarı yeterli. Diğer SQL Server 2014 yenilikleri başlıklarına da yakın zamanda bir göz atmış olacağız.

Faydalı olması dileğiyle,

Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 4

Şifreleme konusunun devamı olan Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 4  isimli yazımı sqlserveronculeri.com adresinden de inceleyebilirsiniz.
Bu yazımla kolon bazlı şifrelemeyi sıfırdan bir örnekle somutlaştırmış olduk. 
Faydalı olması dileğiyle...

Encryption esasları ve Column-Level Encryption kapsamındaki mekanizmaları önceki yazılarımızda incelemiştik. Önceki 3 makaleyi okuyarak konu hakkında daha fazla bilgi edinmek isteyebilirsiniz. Bu bölümde sıfırdan bir örnek ile bu mekanizmaları karma bir şekilde kullanarak şifreleme teorisini pratiğe döküyor olacağız.

Bir önceki yazımı şu linkten ulaşabilirsiniz.

Daha önce de benzerini paylaşmış olduğum aşağıdaki resme bakarak şifreleme için gerekli kombinasyonları rahatça oluşturabiliriz.

Msdn’den aldığım bu resme bakarak sadece password ile verileri şifreleyebildiğim gibi bir certificate veya password ile korunan bir asymmetric key yardımı ile encrypt ettiğimiz symmetric keyi kullanarak da şifreleme yapılabileceğini söyleyebilirim. Buraya bakarak çeşitli kombinasyonları oluşturmak size kalmış.

Biz örneğimizde verileri şifrelemek için certificate ile korunacak bir symmetric key kullanacağız.

Öncelikle EncryptDB isimli bir test veri tabanı ile Musteri isimli bir tablo oluşturalım ve birkaç kayıt girelim.

--test veritabanı oluşturalım

CREATE DATABASE EncyptDB

GO

USE EncyptDB

GO

--Önemli bilgilerin bulunduğu bir tablo oluşturalım

CREATE TABLE Musteri

(

     Id int identity(1,1),

     Ad nvarchar(50),

     KartNum nvarchar(100)

)

GO

--Bir kaç test kaydı

INSERT INTO Musteri VALUES ('Ali','213-123-123-123'),

                                  ('Veli','675-456-345-234')

GO

SELECT * FROM Musteri

Hemen ardından şifreleme için gerekli alt yapıyı oluşturacağız. Bunun için her veri tabanında yalnızca bir tane oluşturulabilen Database Master Key(DMK) create etmeliyiz. DMK ile ilgili bilgileri Şifreleme(Encryption) Esasları – Bölüm 1 isimli yazımda bulabilirsiniz.

--Database Master Key(DMK) oluşturalım

CREATE MASTER KEY

ENCRYPTION BY PASSWORD='pass@word1'

GO

Sonrasında certificate ve bu sertifika ile korunacak symmetric key oluşturuyoruz. Ceriticate ve symmetric key oluşturma ile ilgili bilgileri ise Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 3 isimli yazımdan edinebilirsiniz.

--symmetric key korumak için certificate oluşturalım

--bu sertifikanın korunması için DMK kullanılır.

CREATE CERTIFICATE enc_cert

     WITH SUBJECT='Sifreleme Test'

GO

--encryption için gerekli symmetrik key oluşturalım

--bu keyi yukarıdaki certificate ile koruyalım

CREATE SYMMETRIC KEY enc_sym_key

     WITH ALGORITHM=DES

     ENCRYPTION BY CERTIFICATE enc_cert

Şifreleme için gerekli altyapı hazır durumda. Artık şifrelenmiş metni tabloda tutmak için varbinary bir kolona ihtiyacımız olacak.

--Şifrelenmiş verileri tutmak için yeni kolon ekleyelim

ALTER TABLE Musteri

ADD EncKartNum varbinary(max)

Ve işte şifrelemeye başlıyoruz.

Symmetric key kullandığımız için encryption ve decryption işlemlerinden önce mutlaka key açılmalıdır. Aksi halde fonksiyonlarımızdan sürekli NULL döner.

OPEN SYMMETRIC KEY enc_sym_key

     DECRYPTION BY CERTIFICATE enc_cert

Şifrelenmemiş metnin bulunduğu KartNum kolonundaki veriyi EncryptByKey fonksiyonu ile şifreleyip yeni olşturduğumuz EncKartNum isimli varbinary kolona basalım.

UPDATE Musteri

     SET EncKartNum=EncryptByKey(

                                          Key_GUID('enc_sym_key'),

                                          KartNum);

--bakalım

SELECT * FROM Musteri

Artık verinin şifrelenmiş hali de tablomuzda tutulmakta.

Açık olan symmetric key session düştüğünde kapanır. Öncesinde kapatmak isterseniz şu komutu kullanabilirsiniz.

--açık symmetrik keyi kapatmak için

CLOSE SYMMETRIC KEY enc_sym_key;

Artık temiz metnin bulunduğu KartNum isimli kolona ihtiyacımız yok. Kaldırmamız şifrelemeyi anlamlı hale getirecektir.

--temiz metnin bulunduğu KartNum kolonunu kaldırmak için

ALTER TABLE Musteri

DROP COLUMN KartNum

--bakalım

SELECT * FROM Musteri

Şifreleme işlemini başarıyla tamamladık. Artık temiz metin resimdeki gibi görünecek.

Peki şifreli metni okumak için neler yapmalıyız?

Öncelikle symmetric key kullandığımız için decryption işleminden önce mutlaka keyi açmalıyız.

--Şifreli metni çözmek için

--öncelikle symmetric key açılır

OPEN SYMMETRIC KEY enc_sym_key

     DECRYPTION BY CERTIFICATE enc_cert

Varbinary tipindeki EncKartNum isimli kolonu DecryptByKey fonksiyonu ile çözüyoruz ve nvcarchar tipine convert ediyoruz.

SELECT

     Id,

     Ad,

     CONVERT(NVARCHAR, DecryptByKey(EncKartNum)) as 'Kart Numarası'

FROM Musteri

Böylece şifrelenmiş veriden temiz metni elde etmiş olduk.

Açtığımız symmetric keyi session kapanmadan önce kapatmak istersek şu ifadeyi kullanıyoruz.

--açık symmetrik keyi kapatmak için

CLOSE SYMMETRIC KEY enc_sym_key;

Bu tarz bir şifreleme örnekten de anlaşılacağı gibi şifrelenen alan adedine paralel olarak development iş yükünü arttıracaktır. Ancak ihtiyaca göre az sayıda alanının şifrelenmesi, tüm veri tabanını şifreleyen TDE’ye göre daha az kaynak tüketeceğinden bu yöntemin tercih edilmesi mantıklı olacaktır.

Sizler de Column-Level Encryption mekanizmalarıyla farklı kombinasyonları oluşturarak belli bir metinsel alana odaklanıp yüksek seviyede koruma sağlayabilirsiniz.

Bir sonraki yazımda tüm veri tabanın şifrelenmesi için kullanılan Transparent Data Encryption(TDE) yöntemini inceliyor olacağız. Bu yöntem sayesinde veri tabanının başka serverlara taşınması halinde normal yollarla okunamayacağı garanti altına alınabilmektedir.

Faydalı olması dileğiyle,

Keyifli günler

Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 3

Şifreleme konusunun devamı olan Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 3  isimli yazımı sqlserveronculeri.com adresinden de inceleyebilirsiniz.

Bir önceki Kolon-SatırBazlı Şifreleme (Column Level Encrypyion) - Bölüm 2 isimli yazımda kolon veya satır bazlı şifreleme (Column-Level Encryption) kapsamında kullanılan 4 mekanizmadan ilki olan T-SQL Functions yöntemini incelemiştik. Bu yazımızda aynı kapsamdaki diğer mekanizmalara Asymmetric Keys, Symmetric Keys ve Certificates yöntemlerine odaklanıyor olacağız.

2-      Asymmetric Keys

Asymetric Keys public ve private olarak adlandırılan iki key içermektedir. Public key network üzerinden paylaşılabilirken private key kimse ile paylaşılmaz. Public key alıcılara gönderilmeden önce verileri şifreler. Private key ise bu mesajın şifresini çözer. Asymmetric Keys, birazdan değineceğimiz Symetric Keys güvenliği için de kullanılabilmektedir.

Aşağıdaki resimde Party #1 ile ifade edilen kişi public keyi kullanarak encrypt ettiği veriyi Party  #2 ile ifade edilen kişi sahip olduğu private keyi kullanarak decrypt etmektedir.

Asymmetric key ile symmetric keyin karşılaştırılmasının yapıldığı şu makaleye bir göz atmak isteyebilirsiniz.

http://www.mssqltips.com/sqlservertip/1886/sql-server-encryption-symmetric-vs-asymmetric-keys/

Asymmetric keys aşağıdaki ifade ile oluşturulabilir. CREATE işlemi için 3 çeşit şifreleme algoritması kullanılabilmektedir(RSA_512, RSA_1024, RSA_2048). Algoritma yerine FROM ifadesi ile keylerin import edilebileceği bir kaynak belirtilebilir(File,Executable,Assembly). Bu konudaki detaylı bilgiye http://technet.microsoft.com/en-us/library/ms174430.aspx linkinden ulaşabilirsiniz.

CREATE ASYMMETRIC KEY asymmAnahtarim

WITH ALGORITHM=RSA_1024

ENCRYPTION BY PASSWORD='pass@word1'

İfadedeki son ENCRYPTION BY PASSWORD satırı private keyin password ile encrypt edilmesi için kullanılmaktadır. Eğer belirtilmezse varsayılan olarak Database Master Key kullanılmaktadır. Burada belirtilen şifre maksimum 128 karakterdir ve SQL’in kurulu olduğu makinedeki Windows Password Policy gereksinimlerine uygun olmalıdır(büyük,küçük harfler,karakterler vs.).

Oluşturduğumuz bu asymmetric keyi üzerinde çalıştığımız veri tabanındaki Security/Asymmetric Keys bölümünde görebiliriz.

Oluşturduğumuz bu asymmetric key sayesinde ENCRYPTBYASYMKEY fonksiyonu ile verileri şifreleyebilir DECRYPTBYASYMKEY fonksiyonu ile şifreli metni çözebiliriz. Bu fonksiyonlar encytp etmek için 2, decrypt etmek için 3 parametre ile çalışmaktadır. Encrypt işleminde ilk parametreye asymmetric keyin idsi ikinci parametreye de encrypt edilecek temiz metin verilmektedir. Decrypt işleminde ise son parametreye asymmetric key oluşturulurken belirtilen password verilmektedir.

Fonksiyonda kullanılan parametreler kısaca açıklayalım:

Key_ID :

int tipinden asymmetric keyin idsidir. ASYMKEY_ID() fonksiyonu ile bu id elde edilebilir.

Plain_Text:

               Encrypt edilecek veridir. Bu veri sadece nvarchar, char, varchar, binary, varbinary veya nchar tiplerinden birisi olabilir.

Ciphertext:

Asymmetric key ile encrypt edilmiş veridir.

Asym_Key_Password:

               Asymmetric key oluşturulurken kullanılan password dur.

Bir önceki bölümde yaptığımız gibi burada da değişkenler üzerinden her iki fonksiyonu da kullanarak metni encrypt ve decrypt edelim

3-      Symmetric Keys

SQL Server 2012 üzerinde en hızlı encryption metotlarından birisidir. Verilerin encrypt edilmesi ve decrypt edilmesi için tek bir key kullanılır. Çalışma şeklini aşağıdaki gibi resmedebiliriz.

Yukarıda da belirttiğim gibi asymmetric key ile symmetric keyin karşılaştırılmasının yapıldığı şu makaleye bir göz atmak isteyebilirsiniz.

http://www.mssqltips.com/sqlservertip/1886/sql-server-encryption-symmetric-vs-asymmetric-keys/

Symmetric key mekanizmasının da korunması gerekmektedir. Bunun için Password, Certificate, Asymmetric Key veya başka bir Symmetric Key kullanılabilir. Symmetric keyin encrypt edilmesi için kullanılabilecek ifadeleri yorum satırı olarak aşağıda belirttim.

Aytıntılı bilgilere msdn’den erişebilmek için şu linki kullanabilirsiniz.

http://technet.microsoft.com/en-us/library/ms188357.aspx

CREATE SYMMETRIC KEY symAnahtarim

WITH ALGORITHM = TRIPLE_DES

--ENCRYPTION BY ASYMMETRIC KEY asymmAnahtarim

--ENCRYPTION BY SYMMETRIC KEY symAnahtarim0 ---(önce aktif edilmeli)

--ENCRYPTION BY CERTIFICATE sertifikam

ENCRYPTION BY PASSWORD='pass@word1'

Symmetric key oluştururken çeşitli encryption algoritmaları kullanılabilir. Symmetric keyler kullanılmadan önce mutlaka OPEN SYMMETRIC KEY ifadesi ile aktif edilmelidir. Session sonlandığında bu symmetric key kapanacaktır. İstenirse CLOSE SYMMETRIC KEY ifadesi kullanılarak session bitmeden de symmetric key kapatılabilir.

Verilerin encrypt edilmesi için kullanılan ENCRYPTBYKEY fonksiyonu minimum 2 parametre decrypt edilmesi için kullanılan DECRYPTBYKEY fonksiyonu minimum 1 parametre ile çalıştırılabilmektedir.

Parametreleri aşağıdaki gibi özetleyebiliriz:

Key_guid:

               Symmetric keyin uniqueidentifier tipinden GUID çıktısı. KEY_GUID() fonksiyonu ile elde edilebilir.

Clear_text:

               Encrypt edilecek veridir. Bu veri sadece nvarchar, char, varchar, binary, varbinary veya nchar tiplerinden birisi olabilir.

Ciphertext:

Symmetric key ile encrypt edilmiş veridir.

Benzer bir çalışma ile bu yöntemi örneklendirelim.

4-      Certificates

Certificates dijital olarak imzalanmış güvenlik nesneleridir. İnsanların, cihazların veya organizasyonların tanınması için haklarında çeşitli bilgiler ve private key ile ilişkilendirilecek public key içerir. Bu sayede endpointlere güvenli bağlantı(secure connection) ile erişim sağlanabilmektedir. Database Mirroring’te, veri ve connection şifrelemede veya paket gibi nesnelerin imza altına alınmasında kullanılabilir.

Certificate aşağıdaki ifade ile oluşturulabilir.

CREATE CERTIFICATE Sertifikam

ENCRYPTION BY PASSWORD ='pass@word1'

WITH

     SUBJECT='Sertifika konusu',

          START_DATE='01/01/2013',

          EXPIRY_DATE='01/01/2020'

Burada kullanılan ENCRYPTION BY PASSWORD ifadesi private keyi encrypt etmek için kullanılmaktadır. Belirtilmezse varsayılan olarak Database Master Key geçerli olmaktadır. Burada belirtilen şifre SQL’in kurulu olduğu makinedeki Windows Password Policy gereksinimlerine uygun olmalıdır(büyük,küçük harfler,karakterler vs.).  İfadeden de anlaşılacağı üzere bir tarih aralığı belirtilerek sertifikanın geçerlilik süresi belirlenebilir. Ayrıntılı bilgi için msdn’e göz atmak isteyebilirsiniz.

http://msdn.microsoft.com/en-us/library/ms187798.aspx

Oluşturduğumuz bu Certificate üzerinde çalıştığımız veri tabanının Security/Certificates bölümünde yer almaktadır.

Veri şifrelemek için kullanabileceğimiz ENCRYPTBYCERT fonksiyonu 2 parametre DECRYPTBYCERT fonksiyonu ise 3 parametre almaktadır. Bu parametreleri şu şekilde açıklayabiliriz:

Certificate_id:

               Sertifikaya ait int tipinden bir iddir. Bu değer Cert_ID() fonksiyonu ile elde edilebilir.

Clear_text:

               Encrypt edilecek veri. Bu veri sadece nvarchar, char, varchar, binary, varbinary veya nchar tiplerinden birisi olabilir.

Cipher_text:

               Certificates ile encrypt edilmiş verdir.

Cert_password:

               Sertifika oluşturulurken belirtilen password.

Öncekilere benzer bir çalışma ile bu yöntemi şöyle örneklendirebiliriz.

Asymmetric key ve Symmetric keyi SQL Server dışında tutmaya yarayan ve sadece Enterprise editionda kullanılabilir olan Extensible Key Management(EKM) modülünü bilinçli olarak kapsam dışında bıraktım. SSMS üzerinden "Server/Facet/Sql Server Config" yoluyla aktif edilen EKM, encryption key üretmek ve tutmak için kullanılmaktadır.

Yukarıda oluşturduğumuz nesneler ilgili veri tabanı üzerinde çalıştırılan DROP komutu ile serverdan kaldırılabilir.

DROP SYMMETRIC KEY ad

     DROP ASYMMETRIC KEY ad

     DROP CERTIFICATE ad

Bu bölümde Column-Level Encryption kapsamındaki 4 mekanizmadan geriye kalan 3’ünün neler olduğuna ve nasıl kullanıldığına bir göz attık.

Bir sonraki bölümde bu mekanizmaları karma bir şekilde kullanarak, bir tablodaki metin bazlı kolonları şifreleyip çözeceğiz. Böylece sıfırdan alarak gerçek hayatta pratiği olan bir örnek yapmış olacağız.

Faydalı olması dileğiyle,

Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 2

Bir önceki 1-Şifreleme(Encryption)Esasları – Bölüm 1 isimli bölümde şifreleme seviyeleri, çeşitleri, şifreleme mekanızmasını tetikleyecek nesneleri ve bu nesneleri koruyacak Master Key’leri konu etmiştik. Bu bölümde ise SQL Server 2012 içerisinde 2 çeşit şifreleme yönteminden birisi olan Kolon veya Satır Bazlı Şifrelemeye (Column-Level Encryption) odaklanacağız.

Column-Level Encryption yöntemiyle kolon bazlı verileri şifreleyebilir bunların hem memoryde hem de diskte şifreli olarak tutulmasını sağlayabilirsiniz. Böylece veriler temel okuma taleplerinde şifrelenmiş olarak görünecek. Yazmak istendiğinde ise normal yollarla giriş yapılamayacak.

Veriler üzerinde çalışabilmek için şifreleme mekanizmasını tetiklemede kullandığımız nesnelere her okuma ve yazma işleminde kesinlikle ihtiyaç vardır.

Eğer şifrelemek istediğiniz alanlar çok fazla değilse ve bu alanlar SQL ifadelerinizde çok fazla geçmiyorsa şifreleme metodu olarak Column-Level Encryption’ı tercih etmek isteyebilirsiniz.

Her yazma/okuma işlemi, mekanızmanın çalışması için kullanılan password,certificate,symmetric key,asymmetric key nesnelerine ihtiyaç duymakta. Veri sadece nesneye özel fonksiyonlar yardımıyla şifrelenip çözülebilir. Bu çalışma şekli kolon sayısı çoğaldıkça paralel olarak programlama yükünün de artmasına sebep olacaktır.

Encrytion işlemi herhalukarda hem TDE(Transparent Data Encryption) hem de Column-Level Encryption yöntemlerinde serverın yükünü arttıracaktır. Eğer gizlemeniz gereken alanlar varsa size iki sorum olacak:

Ne kadar gizlemek istiyorsunuz?

Veritabanının ne kadarını gizlemek istiyorsunuz?

Eğer veritabanında küçük bir bölüm çok önemliyse ve temel bir SELECT komutu ile bile görünmesini istemiyorsanız. O zaman birazdan üzerinde konuşacağımız Column-Level Encryption metodunu tercih etmeniz daha doğru olacaktır.

Metotlar arasındaki ayrıntılı karşılaştırmayı ilerleyen bölümlerde yapıyor olacagız. Şimdi Column-Level Encrytion yöntemine odaklanalım ve örneklerimizi bu bölümde değişkenler üzerinden verelim. Örneklerimde aynı mantıkta scriptler kullanıp aralarındaki farklılıklara odaklanmanızı amaçlıyorum.

Şifrelemede kullanılan nesneler Password,Certificate,Symmetric Key, Asymmetric Key dir. Bu nesneler yardımıyla bir çok şekilde şifreleme yapılabilir. Bu durumu msdn deki şu resim güzelce açıklamakta. Bu resimdeki kesiksiz oklar genel şifreleme kombinasyonunu göstermekte. Dışarıdan içeri doğru hiyerarşi gözetilerek farklı kombinasyonlarla da ilerlenebilir.

Bu resme bakarak örneğin şunu söyleyebilirim;

Verilerin şifrelenmesi için “Symmetric Key” kullanılır. Bu Symmetric Key’in korunması için bir “Asymmetric Key” kullanılabilir. Bu Asymmetric Key’in korunması için ise bir “Password” belirtilebilir. Buda ne böyle diyebilirsiniz.  Bu kombinasyonlardan sadece bir tanesiydi. İsterseniz sadece bir “Password” yardımıyla da şifreleme mekanizmasını çalıştırmanız mümkün.

Şimdi tek tek bu nesneler önceki bölümde açıkladığımız mekanizmalar içerisinde nasıl kullanılıyor bir göz atalım. Daha sonra bunları yukarıdaki kombinasyonları oluşturmak için karma bir şekilde kullanacağız.

1-      T-SQL Fonksiyonu

Arkaplanda verileri 128 bitlik bir anahtar yardımıyla koruyan TRIPLE DES algoritması kullanılır. Bu yöntemle  metni şifrelemek için EncryptByPassPhrase fonksiyonu, şifreli metni çözmek için DecryptByPassPhrase fonksiyonu kullanılır. Her iki fonksiyon da en az 2 en fazla 4 parametre ile çalışmaktadır. Örneğin EncryptByPassPhrase fonksiyonuna encrption için kullanılacak bir şifre ve gizlenecek metin verilerek encription sağlanabilir. Şifrelenmiş metin ile birlikte ek bir bilgi daha tutmak isterseniz diğer iki parametreyi de doldurmanız gerekecek. Tabiki metni çözmek istediğinizde belirttiğiniz şifreye ve bu ek bilgiye ihtiyacınız olacak. Aksi taktirde metni çözmek istediğinizde sonuç NULL dönecektir.

Fonksiyonlarda kullanılan parametreleri kısaca açıklayıp bir örnek üzerinde test edelim.

@passphrase :

                      Encryption için kullanılacak herhangi bir şifredir.Fonksiyon şifrenin karmaşıklığını kontrol etmez. Veritipi olarak nvarchar,char,binary,varbinary veya nchar kullanılabilir. Sistem bu şifreden arkaplanda bir symmetric key elde eder.

@cleartext :

                      Gizlemek istediğimiz temiz metin. @passphrase parametresinde kullanılan veri tipleri aynen burada da geçerli.

@chippertext:

                      Encrption sonucunda elde edilmiş varbinary tipindeki değerdir. Veri tipinden dolayı büyüklüğü en fazla 8,000 byte olabilir.

@add_authenticator:

                      Temiz metin ile birlikte ek bir bilgi daha tutmak istersek bu parametreye int tipinde 1 değerini atamalıyız. Örnek olarak müşterilerin isimlerini TC kimlik numaraları ile birlikte gizleyebiliriz. Böylece metni çözmek için her iki bilgiyi de zorunlu kılmış oluruz.

@authenticator:

                      Metin ile birlikte gizlemek istediğimiz veridir. Bu durumda yukarıdaki örneğe göre TC kimlik numarasına tekabül ediyor.

Şimdi her iki fonksiyonu da bir örnek üzerinde görelim

DECLARE @veri varchar(max),

          @sifreli varbinary(max),

          @cozulmus varchar(max)

---

SET @veri='Temiz metin'

SET @sifreli = 

EncryptByPassPhrase('pass@word1',         --@passphrase

                          @veri,                    --@cleartext

                          1,                        --@add_authenticator

                          'ekBilgi')           --@authenticator

SET @cozulmus=

DecryptByPassPhrase(

                          'pass@word1',        --@passphrase

                          @sifreli,            --@cleartext

                          1,                        --@add_authenticator

                          'ekBilgi'            --@authenticator

                          )

SELECT

     @veri as 'Girilen Veri',

     @sifreli as 'Sifrelenmis Hali',

     @cozulmus as 'Cozulmus Hali'

Bu örnekte fonksiyonların işleyişine odaklanalım diye değişkenler üzerinden ilerledik. Gerçek hayat örneğinde tablodaki gizlemek istediğimiz kolonları bu fonksiyonlar yardımıyla şifreleyebilir ve şifreli sonucu varbinary bir kolona basabiliriz. Böyle bir örneği mekanızmaların tümünü inceledikten sonra yapıyor olacağız.

Kolon veya satır bazlı şifreleme (Column-Level Encryption) kapsamında kullanılan 4 mekanıznın ilki olan T-SQL Functions yöntemini incemiş olduk. Yazıyı çok fazla uzatmamak için Certificates, Asymmetric Keys, Symmetric Keys isimli diğer 3 yöntemi de bir sonraki bölüme bırakıyorum.

Faydalı olması dileğiyle.