en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
אוק28

Written by: ronen ariely
28/10/2020 18:07 RssIcon

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());
  }
 }