Always Encrypted in SQL 2016

Always Encrypted feature in SQL Server 2016 allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server. 

An Always Encrypted enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the SQL Server client application. 

The driver encrypts the data in sensitive columns before passing the data to SQL Server, and automatically rewrites queries so that the semantics to the application are preserved. 

Similarly, the driver transparently decrypts data stored in encrypted database columns that are contained in query results.

Step 1: Create New Database

CREATE DATABASE AlwaysEncryptedDemo

Step 2: Configure a Column Master Key

Create a local, self-signed certificate on the development machine, which will act as a column master key (CMK). The CMK will be used to protect column encryption keys (CEK), which encrypts the sensitive data.

Step 3: Configure a Column Encryption Key

 

Step 4: Create a Table using Always Encrypted

CREATE TABLE [dbo].[Client]

(

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

       [SocialSecurityNo] nvarchar(30)

       COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = Deterministic, ALGORITHM='AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY=Salah_CEK_01),

       [FirstName] nvarchar(50),

       [LastName] nvarchar(50)

)

 

CREATE PROCEDURE dbo.spAddClient

(

       @SocialSecurityNo nvarchar(30)

       ,@FirstName nvarchar(50)

       ,@LastName nvarchar(50)

)

AS

BEGIN

       INSERT INTO [dbo].[Client]

       (

              [SocialSecurityNo]

              ,[FirstName]

              ,[LastName]

       )

       VALUES

       (

              @SocialSecurityNo

              ,@FirstName

              ,@LastName

       )

END

Step 5: Create an Application using Always Encrypted

private void btnSave_Click(object sender, EventArgs e)

        {

            try

            {

                using (SqlConnection con = new SqlConnection())

                {

                    con.ConnectionString = ConfigurationManager.ConnectionStrings["AEDB"].ToString();

                    con.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.spAddClient", con))

                    {

                        cmd.CommandType = CommandType.StoredProcedure;

                        SqlParameter fn = new SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 50);

                        fn.Value = txtFirstName.Text;

                        SqlParameter ln = new SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 50);

                        ln.Value = txtLastName.Text;

                        SqlParameter ssn = new SqlParameter("@SocialSecurityNo", System.Data.SqlDbType.NVarChar, 30);

                        ssn.Value = txtSocialSecurityNo.Text;

                        cmd.Parameters.Add(fn);

                        cmd.Parameters.Add(ln);

                        cmd.Parameters.Add(ssn);

                        cmd.ExecuteNonQuery();

                        MessageBox.Show("Client added.");

                        txtFirstName.Clear(); txtLastName.Clear(); txtSocialSecurityNo.Clear();

                    }

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

                 }

           

              private void btnGetData_Click(object sender, EventArgs e)

        {

            try

            {

                using (SqlConnection con = new SqlConnection())

                {

                    DataSet ds = new DataSet();

                    con.ConnectionString = ConfigurationManager.ConnectionStrings["AEDB"].ToString();

                    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [dbo].[Client]", con);

                    da.Fill(ds);

                    dgrid.DataSource = ds.Tables[0];

                }

            }

            catch(Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

Step 6: Try Querying data using SSMS

SELECT * FROM [dbo].[Client]