SQL Server’da Always Encryption ile Kolon Şifreleme

Merhaba,

Bugün sizlere database column encryption konusundan bahsediyor olacağım.

KVKK,GDPR gibi kavramlar son dönemde hayatımıza yoğun şekilde giren kavramlar ve bizler veritabanlarımızda kredi kartı numarası, telefon numarası, tc kimlik numarası gibi kişisel veri içeren verileri saklıyoruz.

Peki, bu tarz verileri veritabanında şifreleyerek nasıl en pratik şekilde saklayabiliriz? Bu makalede bunu anlatıyor olacağım.

Şimdi basit bir veritabanı oluşturalım.

Şimdi de basit bir personel veritabanı oluşturalım. İçinde Adsoyad, TC No ve Tel No alanları olacak.

Tablomuzun adı PERSON. İsteyen için buraya create scriptini de bırakıyoruz.

CREATE TABLE [dbo].[PERSON](

       [ID] [int] IDENTITY(1,1) NOT NULL,

       [NAMESURNAME] [varchar](50) NULL,

       [CITIZENNUMBER] [varchar](15) NULL,

       [TELNR] [varchar](15) NULL)

Şimdi bu tabloya iki kayıt ekleyelim.

Insert Into Person (NAMESURNAME,CITIZENNUMBER,TELNR)

VALUES (‘ÖMER ÇOLAKOĞLU’,’12345678904′,’05321234567′)

Insert Into Person (NAMESURNAME,CITIZENNUMBER,TELNR)

VALUES (‘HAKAN UZUNER’,’98765432102′,’05327654321′)

Öncelikle kullandığımız verinin fake veri olduğunu söylemek isterim. 😊

Şimdi tablomuzdan sorgu çekelim.

SELECT * FROM PERSON

Evet. Kayıtlarımız açık şekilde görülmekte.

Şimdi burada kayıtlarımızı always encrypted özelliği ile şifreleyeceğiz.

Bunun için tablo üzerinde sağ tık encrypt columns diyoruz.

Burada TELNR ve CITIZENNUMBER alanlarını seçiyoruz.  Encryption Type olarak iki seçeneğimiz var. Bunlardan biri Deterministic, diğeri Randomized. Şimdilik bunun detayına girmiyorum. Deterministic sıralı, Randomized rastgele şekilde şifreleme yapan bir yapıya sahip. Deterministic performans olarak sıralı bir şifreleme yaptığı için daha iyi bir arama performansı sunuyor. Şimdilik konuyu dağıtmamak adına bu kadar bilmek yeterli.

Sağ tarafta bir de Encryption Key var. Bu sistem tarafından üretilen bir key. İsmi CEK_Auto1.

Her iki kolon için de deterministic seçip devam ediyoruz.

Şimdi de bir masterkey oluşturuyoruz.

Next diyerek devam ediyoruz.

İşlem tamamlandı.

Şimdi tekrardan tablomuzdan sorgu çektiğimizde tablonun şifrelendiğini görebiliyoruz.

Peki bu şifreli alanı görebilmek için ne yapmak gerekiyor?

Bağlantı yaparken Column Encryption Setting özelliğini enable yaparak bağlanmamız gerekiyor. Tabi bunun için bir de sertifikaya sahip olmamız gerekiyor.

Şimdi db server üzerinde SSMS ile tekrar bağlantı yapalım ve bu kez Column Encryption Setting özelliğini enable yaparak olarak belirleyelim.

Bunun için SSMS’de yeni bir bağlantı oluşturuyoruz

Options’a tıklıyoruz.

Additional Connection Parameters sekmesinde Column Encryption Setting=Enable;

yazıyoruz. Ve connect’e tıklıyoruz.

Sonra bu açtığımız bağlantı üzerinden New Query diyoruz. Artık şifreli alanları açık şekilde görmeyi umuyoruz.

Görüldüğü gibi artık alanları bu şekilde görebiliyoruz.

Şimdiye kadar yaptığımız her şey sunucu üzerindeydi. Şimdi bir client üzerinden bağlanalım. Sunucumuzun adı SQLEGITIM ve ben şimdi başka bir bilgisayardan SQLEGITIM makinesine bağlanıyorum.

Görüldüğü gibi şifreli şekilde geldi.

Şimdi bir öncekinde yaptığımız gibi connection string’ additional parametre ekleyerek Column Encryption Setting=Enabled; özeliği ile bağlanalım.

Bu kez hata aldık. Sistem encryption key’i bulamadığını söylüyor.

Şimdi sunucuda oluşturduğumuz encryption key i kendi makinemize export-import yapacağız.

Bunun için sunucuda başlat çalıştır ekranına “Cert” yazıyoruz ve Manage Computer certificates menüsüne tıklıyoruz.

Burada Personal’ın altında Certificates sekmesine tıkladığımızda column encryption yaparken oluşturduğumuz sertifikayı görebiliyoruz.

Bu sertifikayı dışarı almak için sağ tık All Tasks>Export diyoruz.

Next diyerek sonraki adıma geçiyoruz.

Yes Export the private key seçeneğini seçiyoruz.

Burada sertifikaya bir şifre belirliyoruz. Ben Password1 olarak belirledim.

Masaüstüne Certificate.pfx olarak kaydediyorum.

Export tamamlandı.

Şimdi bu dosyayı client bilgisayara kopyalıyoruz.

Şimdi client bilgisayarda bu dosyayı import edeceğiz. Bunun için başlat’a cert yazalım.

Kişisel>Sertifikalar>Tüm Görevler>Al diyoruz.

Bilgisayarımıza kopyaladığımız sertifika dosyasını gösteriyoruz.

Sertifikayı dışarı export ederken belirlediğimiz şifreyi giriyoruz.

Ve sertifika import etme işlemi tamamlandı. Artık client bilgisayarımızda da sertifikamız var. Şimdi önce management studio’dan sonra da bir client uygulamasından deneyelim.

Client uygulamasının source kodlarını da buraya bırakıyorum.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace AlwaysEncryptedClient

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            try {

           // String ConnString = “Data Source=SQLEGITIM;Initial Catalog=TEST;User ID=SA;Password=Password1;Column Encryption Setting=Enabled”;

            SqlConnection conn = new SqlConnection(ConnString.Text);

        //  String selectCommand = “Select * from test”;

            String selectCommand = SqlCmd.Text;

            SqlCommand cmd = new SqlCommand(selectCommand, conn);

            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();

            sda.Fill(dt);

            dataGridView1.DataSource = dt;

            }

            catch (SqlException ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

    }

}

Sonuç

Bu makalemizde kritik verilerin Column Encryption ile veritabanında nasıl şifreli tutulacağından bahsettik ve uygulamasını gerçekleştirdik. Bir sonraki makalede de bu verileri içeriye nasıl insert edeceğimizi anlatıyor olacağım. Umarım beğenmişsinizdir.

Sonraki makalede görüşmek dileğiyle.