رمزنگاری اطلاعات در SQL Server 2016 با استفاده از امکان Always Encrypted - به همراه مثال در ADO.Net و EntityFramework

علی بهرامی‌نژاد - - زمان تقریبی مطالعه: 6 دقیقه

SQL Server 2016 - Always Encrpted

رمزنگاری دیتا در بانک اطلاعاتی همواره از مشکلات بزرگ برنامه‌نویسان است؛ اینکه بتوانند علاوه بر رمزنگاری مناسب دیتا؛ روی دیتای رمزنگاری شده Query اجرا کنند، Index بسازند.

مدت زیادی نیست که نسخه نهایی SQL Server 2016 توسط مایکروسافت ارائه شده؛ یکی از قابلیت‌های منحصربفرد و هیجان انگیز آن Always Encrypted است که امکان رمزنگاری ستون‌های بانک اطلاعاتی فراهم میکند.

رمز نگاری بدین شکل هست که دیتا در دیتابیس و مسیر ارتباطی با برنامه به شکل رمزنگاری شده است و تنها در برنامه شماست که رمزگشایی دیتا انجام می‌شود.

اگر علاقه‌مند هستید که چطور میشه ستون‌هارو رمزنگاری کرد و چطور میشه در دات نت (ADO.Net و Entity Framework) از اون بهره برد حتماً ادامه مطلب را بخونید.

نیازمندی‌ها


اگر قبلاً SQL Server 2016 و Management Studio 2016 رو نصب نکردید، از طریق لینک‌های زیر دانلود و نصب کنید.

برای دانلود نسخه Developer حتماً باید در visualstudio.com/free ثبت نام کنید و عضو برنامه Visual Studio Dev Express شوید.

انواع رمزنگاری


قبل از اینکه نحوه استفاده از Always encrpted را بررسی کنیم، باید بدونیم که Always Encrypted به ما دو نوع روش رمزنگاری پیشنهاد میدهد:

  1. Deterministic encryption
  2. Randomized encryption

در Deterministic علاوه بر رمزنگاری داده‌ها امکان فیلترکردن داده‌ها با عملگر مساوی، جوین زدن جدول‌ها و دسته بندی فراهم شده است. در این روش رمزنگاری همیشه یک مقدار ثابت رمزنگاری شده برای یک رشته یکسان تولید میشود.

برای مثال به جدول زیر نگاه کنید:

Sample Value Sample Encrytped
John 0xfff777895
Ali 0xeaaf557412
John 0xfff777895

در 2 رکورد متفاوت برای رشته John یک عبارت یکسان تولید شده است


روش Randomized روشی امن‌تر از روش اول است چون رشته‌های رمزنگاری آن شانس حدس زدن بسیار پایینی دارند، اما نمی‌توان در این جدول‌ها را براساس مقدار رمزنگاری شده جوین زد، با عملگر مساوی مقادیر را پیدا یا دیتا را دسته بندی کرد. به این دلیل که در هر مرتبه از یک cipher text جدید جهت رمزنگاری استفاده میکند.

جدولهای نمونه


در نظر بگیرید دو جدول به نام‌های dbo.Person و dbo.PersonCard با ساختار زیر وجود دارد:

جدول dbo.Person

Column Type
Id int
FullName nvarchar(50)


جدول dbo.PersonCard

Column Type
PersonId int
CardNumber nvarchar(16)
BankName nvarchar(50)

در این جدول نام بانک و شماره کارت کاربر مهم تلقی شده و قصد رمزنگاری آنرا داریم


فعال سازی Always Encrypted


جهت رمزنگاری؛ کافیست در Management Studio برروی جدولی که می‌خواهیم ستون‌های آنرا رمزنگاری کنیم راست کلیک و گزینه Encrypt Columns را انتخاب کنیم. ( در اینجا جدول dbo.PersonCard )

Encrypt Columns

سپس در پنجره باز شده به مرحله دوم یعنی Column Selection می‌رویم تا ستون‌های مورد نظر جهت رمزنگاری انتخاب و نوع رمزنگاری آن‌ها را تعیین کنیم.

SQL Server 2016 - Always Encrypted - Column Selection

در پنجره بالا برای ستون‌های CardNumber و BankName جهت رمزنگاری نوع Deterministic را اتخاب کرده ایم.

همانطور که در شکل بالا پیداست، در یک ToolTip اعلام شده است که نوع Collation ستون از Persian_100_AS_CS به Persian_100_bin2 تبدیل خواهد شد. برای رمزنگاری Deterministic نوع Collation ستون باید از نوع Binary باشد.

در صورت رفتن به مرحله بعدی؛ می‌بایست پیکربند Master Key را انجام دهیم. اما سوال اصلی اینجاست که Master Key چیست؟ طبق تعریف مایکروسافت Column Master Keyها؛ کلیدهایی جهت محافظت از «کلیدهای رمزنگاری کننده ستون» هستند و این کلید‌ها در جایی امن مانند Windows Certificate Store باید ذخیره شوند.

SQL Server 2016 - Always Encrypted

در این تصویر با استفاده از Column Encryption Keys داده‌های حساس در دیتابیس رمزنگاری شده اند و Master Keyها جهت دسترسی به کلیدهای رمزنگاری کننده ستون‌ها هستند.


پیکربندی Master Key:

SQL Server 2016 - Configuration Master Key

در این مرحله اگر قبلاً Master Key تولید کرده باشیم می‌توانیم از قسمت Select column master key نسبت به انتخاب آن یا اگر قبلاً Master Key نساخته ایم با انتخاب Auto generate column master key نسبت به تولید Master Key اقدام کنیم. همچنین محل ذخیره سازی Master Key بسیار مهم است؛ همانطور که بالاتر نیز شرح داده شد Master Keyها می‌بایست در جایی امن و قابل اطمینان ذخیره شوند. در اینجا Windows Certification Store جهت ذخیره Master Keyها انتخاب شده است. پس از انتخاب master key source با رفتن به مرحله Summary عملیات ساخت Keyها را آغاز می‌کنیم.

در صورتی که خطایی در زمان تولید کلید‌ها رخ ندهد؛ نتیجه چیزی مانند تصویر زیر خواهد بود:

SQL Server 2016 - Encrypted in DB , Decrypted In App

حال اگر از جدول dbo.PersonCard یک SELECT ساده بگیریم؛ خواهیم دید که اطلاعات به چه شکل رمزنگاری شده اند.

SELECT * FROM dbo.PersonCard

SQL Server 2016 - Sample Encrypted Data

اطلاعات در ستون‌ها رمزنگاری شده اند و برخی داده‌های رمزنگاری شده با یکدیگر برابر هستند.

باید توجه داشت که اطلاعات بین مسیر ارتباطی با SQL Server و همچنین داخل SQL Server به شکل رمزنگاری شده هستند و فقط در برنامه است که داده‌ها رمزگشایی میشوند.

SQL Server 2016 - Encrypted in DB , Decrypted In App

مثال عملی - خواندن دیتای رمزنگاری شده


در Query زیر یک Aggregation و Group By برروی یکی از ستون‌های رمزنگاری شده انجام میدهیم.

SELECT BankName, COUNT(1) [CardQuantity] FROM dbo.PersonCard
GROUP BY BankName

SQL Server 2016 - Sample Encrypted Data Group by Query

قبلاً نیز اشاره کردیم که تنها دربرنامه می‌توان مقدار رمزنگاری شده را رمزگشایی کرد؛ حال سعی میکنیم این کار را در یک پروژه با دات نت انجام دهیم.

برای استفاده از Always Encrypted در دات نت بهتر است در پروژه خود از Netframework 4.6.x استفاده کنید.

وجود عبارت Column Encryption Setting=enabled در Connection String، سبب می‌شود تا ADO.Net از امکان Always Encrypted استفاده کند.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace AlwaysEncrypted
{
    class Program
    {
        static void Main(string[] args)
        {
            // Column Encryption Setting=enabled => Always Encrypted تنظیم فعال بودن امکان 
            var connectionString = @"Data Source=.\SQL2016; Integrated Security=SSPI;Initial Catalog=MyDB; Column Encryption Setting=enabled;Trusted_Connection=Yes;";

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT BankName, COUNT(1) [CardQuantity] FROM dbo.PersonCard GROUP BY BankName";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        Console.WriteLine($"{reader[0]}\t{reader[1]}");

                    reader.Close();
                }
            }

            Console.Read();
        }
    }
}

بدون اینکه کد اضافه ای نوشته شود دیتا رمزگشایی شد.

SQL Server 2016 - Sample Decrypted Data Group by Query

مثال عملی - درج دیتا


با استفاده از تکه کد زیر قصد داریم یک کارت بانکی جدید در جدول dbo.PersonCard اضافه کنیم. از قبل ستون‌های BankName و CardNumberرمزنگاری شده اند، اما به چه شکل میتوانیم در دات نت در آنها دیتا درج کنیم؟

// Column Encryption Setting=enabled => Always Encrypted تنظیم فعال بودن امکان 
var connectionString = @"Data Source=.\SQL2016; Integrated Security=SSPI;Initial Catalog=MyDB; Column Encryption Setting=enabled;Trusted_Connection=Yes;";

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    var cmd = connection.CreateCommand();
    cmd.CommandText = @"INSERT INTO [dbo].[PersonCard]
                        SELECT @PersonId, @CardNumber, @BankName;";



    cmd.Parameters.AddWithValue("@PersonId", 1);
    cmd.Parameters.AddWithValue("@BankName", "New Bank of Mine");
    cmd.Parameters.AddWithValue("@CardNumber", "80908945xxxxyyyy");


    cmd.ExecuteNonQuery();

    Console.WriteLine("Inserted!");
}

Console.Read();

کد بالا بدون مشکل اجرا میشود و رکورد در دیتابیس درج میشود؛ اگر با استفاده از SQL Server Profiler دستورات ارسالی به SQL Server پروفایل کنید خواهید دید که مقادیر اارسالی نیز به شکل رمزنگاری شده ارسال شده اند!

SQL Server Profiler - Insert in Always Encrypted

exec sp_executesql N'INSERT INTO [dbo].[PersonCard]
                                    SELECT @PersonId, @CardNumber, @BankName;',N'@PersonId int,@BankName nvarchar(16),@CardNumber nvarchar(16)',@PersonId=1,@BankName=0x01272FF7910740F799AC04B88B70A0C540CA03301928CCFEB027DDBD56589004159F7DD3C71D8B5D83BE0DFE1B52B9374B542F38F419B3F8C0A06F9FDD398DEBB6BA3B499D9D43017755F87F89720EDFC02B2227BCC15C5BCD03B87C9D3837EC5D,@CardNumber=0x01634D3FF45705BCA69969F7650B178B4B733F0148A7FE6562CF9DEABC8BF1BCAC3249FF28CE30AAE4D4CA8663EA9D3474E35711867CF35069B930272DC979BB761B92F319541DF9233F8983DA5ECE39607212E28C89316D35A02A480721ED04C7


مثال عملی - خواندن دیتا و درج دیتا با استفاده از EntityFramework


تکه کد زیر جهت اجرای دستورات خواندن، درج، بروز آوری، جستجو و دسته بندی در Entity Framework نوشته شده است.

// Column Encryption Setting=enabled => Always Encrypted تنظیم فعال بودن امکان 
var connectionString = @"Data Source=.\SQL2016; Integrated Security=SSPI;Initial Catalog=MyDB; Column Encryption Setting=enabled;Trusted_Connection=Yes;";

using (var context = new MyDBContext(connectionString))
{
    // Read them all
    Console.WriteLine("All Data");
    context.PersonCards.ToList().ForEach((card) =>
    {
        Console.WriteLine($"{card.PersonId}, {card.BankName}, {card.CardNumber}");
    });

    // Insert some
    var newCard = context.PersonCards.Add(new PersonCard
    {
        PersonId = 1,
        BankName = "Entity Bank of Me",
        CardNumber = "1234567891234531"
    });
    context.SaveChanges();
    Console.WriteLine("\r\nRecord has inserted");

    // A Where Condition
    // حتماً به متدهای لینک متغیر پاس دهید تا کوئری تولید شده پارامتری باشد
    // در غیر اینصورت ممکن است با خطا مواجه شوید!
    var aCard = context.PersonCards
                        .FirstOrDefault(card => card.CardNumber == newCard.CardNumber);
    if (aCard != null)
        Console.WriteLine($"\r\nCard found \t => \t {aCard.PersonId}, {aCard.BankName}, {aCard.CardNumber}");

    // Update some fields
    aCard.BankName = "EN Bank";
    context.SaveChanges();

    Console.WriteLine("\r\nRecord has updated.");

    // An aggregation query
    var result = (from c in context.PersonCards
                    group c by c.BankName into gc
                    select new { BankName = gc.Key, Quantity = gc.Count() })
                    .ToList();

    Console.WriteLine("\r\nA group by!\r\n");
    result.ForEach((r) =>
    {
        Console.WriteLine($"{r.BankName}, {r.Quantity}");
    });

}


Console.Read();

خروجی تکه کد بالا

SQL Server Profiler - EntityFramework Query Result


بزرگترین مزیت Always Encrypted عدم نیاز به تغییر در کدهای برنامه است و تنها با اضافه کردن یک تنظیم در Connection String می‌توان از این امکان جدید استفاده کرد.

دیدگاه‌ها