SQL Server Always Encrypted Part 2

In part one, I demonstrated how to create a table with always encrypted columns. Now let's create a stored procedure to insert a row into it, another stored procedure to query a row from it, and a C# .NET console application to call these stored procedures. You will see that even if you get the sysadmin privilege, you cannot simply run query statement or call stored procedures to access those encrypted data.

Let's create the column master key and column encryption key using SSMS GUI:

In the user database, expand Security > Always Encrypted Keys > right-click Column Master Key > New > type the Name > click Generate Certificate.

Then right-click Column Encryption Key > New > type the Name > select the master key.

Let's create the table:

CREATE TABLE TestEncryptTable (
    id int IDENTITY(1, 1) PRIMARY KEY,
    encryptedLookupCol varchar(11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
        ENCRYPTION_TYPE = DETERMINISTIC,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = TestColumnEncryptKey) NOT NULL,
    encryptedValueOnlyCol varchar(11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
        ENCRYPTION_TYPE = RANDOMIZED,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = TestColumnEncryptKey) NOT NULL
);

Here are the stored procedures that my application will call to add and get row from the always encrypted table:

USE TestDB
GO
CREATE OR ALTER PROC InsertEncryptRow (
    @encryptedLookupCol varchar(11),
    @encryptedValueOnlyCol varchar(11)
) AS
BEGIN
    INSERT TestEncryptTable VALUES (
        @encryptedLookupCol, @encryptedValueOnlyCol);
END
GO
CREATE OR ALTER PROC GetEncryptRow (
    @id int
) AS
BEGIN
    SELECT * FROM TestEncryptedTable WHERE id = @id;
END

In the C# application, the database connection string must specify a new option for enabling Always Encrypted. This is the 'Column Encryption Setting = Enabled''option.

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestAlwaysEncrypted
{
    public class Class1
    {
        public static string CS = "Data Source=.;Initial Catalog=TestDB;Column Encryption Setting=Enabled;Integrated Security=True";
        static void Main(string[] args)
        {
            string a1 = args[0];
            string a2 = args[1];
            SqlConnection conn = new SqlConnection(CS);
            using (conn)
            {
                conn.Open();
                string cmd = "InsertEncryptRow";
                SqlCommand sqlCmd = new SqlCommand(cmd, conn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@encryptedLookupCol", SqlDbType.VarChar);
                sqlCmd.Parameters["@encryptedLookupCol"].Value = a1;
                sqlCmd.Parameters.Add("@encryptedValueOnlyCol", SqlDbType.VarChar);
                sqlCmd.Parameters["@encryptedValueOnlyCol"].Value = a2;
                sqlCmd.ExecuteNonQuery();
            }
        }
    }
}

Execute the .NET project a few times, you can see the table has some new rows filled in, but you cannot see the encrypted data even you are the sysadmin of your SQL Server:

In order to see the data in SSMS, you can specify the Column Encryption setting when connected to the SQL instance through the 'Additional Connection Parameters' option:

Run the select query again, you can see the data now.

You can also verify that the data transmitted from the client application to your SQL Server is really encrypted, by using SQL Profiler to trace the stored procedure executed:

The .NET library automatically calls a SQL Server built-in stored procedure, sp_describe_parameter_encryption, so it knows which parameters needed to be encrypted.