אוק28
Written by:
ronen ariely
28/10/2020 18:07 
Always Encrypted - demo in a nutshell (using PowerShell and C# as clients). This is pure demo code from start to end
SQL Server: Create the database -> create the table with encryption -> create SP
DROP DATABASE IF EXISTS [RonenArielyAlwaysEncipted]
GO
CREATE DATABASE [RonenArielyAlwaysEncipted] COLLATE Latin1_General_BIN2
GO
Use [RonenArielyAlwaysEncipted]
GO
------------------------------------------------
/* create the master key, Genenrate the certificate, and create encryption key*/
-- Ariely_SQL_CMK
-- Ariely_SQL_CEK
------------------------------------------------
DROP table if exists RonenArielyDemo
GO
CREATE TABLE RonenArielyDemo(
ID INT NOT NULL,
FirstName nvarchar(25) NOT NULL,
LastName nvarchar(25) NOT NULL,
BirthDate date
ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = Ariely_SQL_CEK
) NULL
PRIMARY KEY CLUSTERED (ID)
)
GO
CREATE OR ALTER PROCEDURE dbo.AddSP
@ID INT, @FirstName nvarchar(25), @LastName nvarchar(25), @BirthDate date
AS
INSERT INTO dbo.RonenArielyDemo (ID, FirstName, LastName, BirthDate)
VALUES (@ID , @FirstName, @LastName, @BirthDate);
GO
PowerShell: Insert row
$SqlConn1= New-Object System.Data.SqlClient.SqlConnection
$SqlConn1.ConnectionString = "Server=Machine_Name\Instance_Name;Database=RonenArielyAlwaysEncipted;Integrated Security=SSPI; Column Encryption Setting=enabled;"
$SqlConn1.Open()
$SqlCmd1= New-Object System.Data.SqlClient.SqlCommand
$sqlcmd1.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd1.Connection = $SqlConn1
$SqlCmd1.CommandText = "dbo.AddSP"
# Notice that we must use parameters (which is also more secure)
$SqlCmd1.Parameters.AddWithValue("@ID", 1)
$SqlCmd1.Parameters.AddWithValue("@FirstName", 'Ronen')
$SqlCmd1.Parameters.AddWithValue("@LastName", 'Ariely')
$BirthDate= New-Object -TypeName System.Data.SqlClient.SqlParameter
$BirthDate.ParameterName = "@BirthDate"
$BirthDate.SqlDbType = [System.Data.SqlDbType]::Date
$BirthDate.Direction = [System.Data.ParameterDirection]::Input
$BirthDate.Value = '2020-02-27'
$SqlCmd1.Parameters.Add($BirthDate);
$SqlCmd1.ExecuteNonQuery();
$SqlConn1.Close()
Using C Sharp: Insert row
String connectionString = @"Data Source=Machine_Name\Instance_Name; Integrated Security=true; Database = RonenArielyAlwaysEncipted;";
SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(connectionString);
connStringBuilder.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;
connectionString = connStringBuilder.ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
using (SqlCommand cmd = new SqlCommand("AddPatientSP", connection)){
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter ID = new SqlParameter("@ID", System.Data.SqlDbType.Int);
ID.Value = 1;
cmd.Parameters.Add(ID);
SqlParameter FirstName = new SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 50);
FirstName.Value = "Ronen";
cmd.Parameters.Add(FirstName);
SqlParameter LastName = new SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 50);
LastName.Value = "Ariely";
cmd.Parameters.Add(LastName);
SqlParameter BirthDate = new SqlParameter("@BirthDate", System.Data.SqlDbType.Date);
BirthDate.Value = "2020-02-27";
cmd.Parameters.Add(BirthDate);
try{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch (Exception ex){
Console.WriteLine(ex.Message.ToString());
}
}