Always Encrypted In SQL Server 2016

Overview Always Encrypted

It a new security feature that ensures sensitive data is encrypted in database and remain all time.
Means Encrypted Data at rest.

Advantage

Clear separation between who own the data and who manage the data.

Even DBAs, System admins and cloud admins can’t access data.

How its work?

• Client driver encrypt data.
• Required master key digital certificate on both db and application which contain private key.
• Digital certificate could be store into
• Local key store - default windows certificate store.
• HSM - Hardware security module third parties solution to manage dsc in network, like safenet.
• Azure vault - Microsoft’s cloud-based solution for certificate management
• Required Column Key

What is Master key and Column key?

Column key protect the data and master key protect the column encryption key.

Configuration

For simple configuration watch a Youtube Video.

C# code to Access Data

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



namespace ReadWriteAlwaysEncrypted
{
    class Program
    {
        static string connectionString = "Data Source=.; Initial Catalog=DB2016; Integrated Security=true; Column Encryption Setting=enabled";

        static void Main(string[] args)
        {

            //InsertData("Raja babu", "M", "12345678912347" , "89000", "emailraja@gmail.com", "8800280088");
            ReadData(1);
            Read();
        }


        static void InsertData(string name, string gender, string creditCard, string salary, string email, string phone)
        {

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = @"INSERT INTO dbo.PersonalInformation (Name, Gender,CreditCard, Salary, Email, Phone)VALUES( @Name, @Gender, @CreditCard, @Salary, @Email, @Phone);";

                    SqlParameter paramSSN = cmd.CreateParameter();
                    paramSSN.ParameterName = @"@Name";
                    paramSSN.DbType = DbType.String;
                    paramSSN.Direction = ParameterDirection.Input;
                    paramSSN.Value = name;
                    cmd.Parameters.Add(paramSSN);

                    SqlParameter paramGender = cmd.CreateParameter();
                    paramGender.ParameterName = @"@Gender";
                    paramGender.DbType = DbType.String;
                    paramGender.Direction = ParameterDirection.Input;
                    paramGender.Value = gender;
                    cmd.Parameters.Add(paramGender);

                    SqlParameter paramCreditCard = cmd.CreateParameter();
                    paramCreditCard.ParameterName = @"@CreditCard";
                    paramCreditCard.DbType = DbType.AnsiStringFixedLength;
                    paramCreditCard.Direction = ParameterDirection.Input;
                    paramCreditCard.Value = creditCard;
                    cmd.Parameters.Add(paramCreditCard);

                    SqlParameter paramSalary = cmd.CreateParameter();
                    paramSalary.ParameterName = @"@Salary";
                    paramSalary.SqlDbType = SqlDbType.Int;
                    paramSalary.Direction = ParameterDirection.Input;
                    paramSalary.Value = salary;
                    cmd.Parameters.Add(paramSalary);


                    SqlParameter paramEmail = cmd.CreateParameter();
                    paramEmail.ParameterName = @"@Email";
                    paramEmail.DbType = DbType.String;
                    paramEmail.Direction = ParameterDirection.Input;
                    paramEmail.Value = email;
                    cmd.Parameters.Add(paramEmail);


                    SqlParameter paramPhone = cmd.CreateParameter();
                    paramPhone.ParameterName = @"@Phone";
                    paramPhone.DbType = DbType.String;
                    paramPhone.Direction = ParameterDirection.Input;
                    paramPhone.Value =phone;
                    cmd.Parameters.Add(paramPhone);

                    cmd.ExecuteNonQuery();

                    WriteLine("Record Inserted.");
                }
            }
        }


        static void ReadData(int id)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = connection.CreateCommand())
                {
                    connection.Open();
                    cmd.CommandText = @"SELECT * FROM [dbo].[PersonalInformation] WHERE id=@id";
                    SqlParameter paramID = cmd.CreateParameter();
                    paramID.ParameterName = @"@id";
                    paramID.DbType = DbType.Int32;
                    paramID.Direction = ParameterDirection.Input;
                    paramID.Value = id;
                    cmd.Parameters.Add(paramID);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                WriteLine($"{reader[0]}, {reader[1]}, {reader[2]}, {reader[3]}, {reader[4]}, {reader[5]}");
                            }
                        }
                    }
                }
            }
        }
    }
}