March 1, 2018 at 11:47 am
Hi All,
We are doing a POC on SQL Server 2016 new security feature Always encrypted.
Basic need is encryption over wire. We were able to encrypt tables using this feature. Able to SELECT, INSERT using parameterized sql/stored procedures. Now, we want to do some basic performance testing , to see the time it takes before encryption and after encryption. We are testing for 1000 INSERTS operation. To implement this, in a stored proc with a user defined table data type.
Question
==========
it was throwing error saying below and found in MSDN documentation that this type of encryption is not supported for user defined data types. Now , we are looking for any workarounds or any other good way to implement the encryption cross the wire and database table data should also get encrypted. The problem is we cannot insert each row using variables. Otherwise will be performance issues. So, basically looking for workaround to do a Bulk copy method using c#.net for always encrypted tables.
Stored proc definition. but when i try to create the sp itself throws the below errors. This code works fine for non-encrypted data.
--- table structure before encryption
CREATE TABLE [dbo].[Profile](
[Id] [int] NOT NULL,
[LionLogin] [varchar](max) NOT NULL,
[FirstName] [varchar](max) NULL,
[LastName] [varchar](max) NULL,
[Title] [varchar](max) NULL,
[PersonType] [varchar](max) NULL,
[AssignmentStatus] [varchar](max) NULL,
[StartDate] [datetime] NULL,
[HomeOffice] [varchar](max) NULL,
[JobTitle] [varchar](max) NULL,
[BusinessUnit] [varchar](max) NULL,
[Discipline] [varchar](max) NULL,
[SupervisorName] [varchar](max) NULL,
[PersonCompCommunicator] [varchar](max) NULL,
[PersonCompApprover] [varchar](max) NULL,
[DateTimeStamp] [datetime] NULL,
[BirthDate] [datetime] NULL,
[Gender] [varchar](max) NULL,
[PreferredName] [varchar](max) NULL,
[EndDate] [datetime] NULL,
[ProfilePicture] [varchar](max) NULL,
[BrandFunction] [varchar](max) NULL,
[Status] [varchar](max) NULL,
[PersonStaffingPartner] [varchar](max) NULL,
[Department] [varchar](max) NULL
)
GO
-- table structure after encryption
CREATE TABLE [dbo].[HRISProfile](
[Id] [int] NOT NULL,
[LionLogin] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[FirstName] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[LastName] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Title] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[PersonType] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[AssignmnetStatus] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[StartDate] [datetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[HomeOffice] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[JobTitle] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BusinessUnit] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Discipline] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[SupervisorName] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[PersonCompCommunicator] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[PersonCompApprover] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[DateTimeStamp] [datetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BirthDate] [datetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Gender] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[PreferredName] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[EndDate] [datetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[ProfilePicture] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BrandFunction] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Status] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[PersonStaffingPartner] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Department] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
)
---- user defined data type
CREATE TYPE [dbo].[ProfileType] AS TABLE(
[CodeId] [int] NOT NULL,
[LionLogin] [varchar](max) NOT NULL,
[FirstName] [varchar](max) NULL,
[LastName] [varchar](max) NULL,
[Title] [varchar](max) NULL,
[PersonType] [varchar](max) NULL,
[AssignmentStatus] [varchar](max) NULL,
[StartDate] [datetime] NULL,
[HomeOffice] [varchar](max) NULL,
[JobTitle] [varchar](max) NULL,
[BusinessUnit] [varchar](max) NULL,
[Discipline] [varchar](max) NULL,
[SupervisorName] [varchar](max) NULL,
[PersonCompCommunicator] [varchar](max) NULL,
[PersonCompApprover] [varchar](max) NULL,
[DateTimeStamp] [datetime] NULL,
[BirthDate] [datetime] NULL,
[Gender] [varchar](max) NULL,
[PreferredName] [varchar](max) NULL,
[EndDate] [datetime] NULL,
[ProfilePicture] [varchar](max) NULL,
[BrandFunction] [varchar](max) NULL,
[Status] [varchar](max) NULL,
[PersonStaffingPartner] [varchar](max) NULL,
[Department] [varchar](max) NULL
)
GO
--Tried creating a stored procedure which eventually throws below error
CREATE PROCEDURE [dbo].[usp_Insert_p1]
(
@myProfile [dbo].[ProfileType] READONLY
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[HRISProfile]
SELECT
h.@myProfile
,h.LionLogin
,h.FirstName
,h.LastName
,h.Title
,h.PersonType
,h.AssignmentStatus
,h.StartDate
,h.HomeOffice
,h.JobTitle
,h.BusinessUnit
,h.Discipline
,h.SupervisorName
,h.PersonCompCommunicator
,h.PersonCompApprover
,h.DateTimeStamp
,h.BirthDate
,h.Gender
,h.PreferredName
,h.EndDate
,h.ProfilePicture
,h.BrandFunction
,h.Status
,h.PersonStaffingPartner
,h.Department
FROM @myProfile h
END
--Error
/*
Msg 206, Level 16, State 2, Procedure Insert_p1, Line 6 [Batch Start Line 0]
Operand type clash: varchar(max) is incompatible with varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AE_Agents') collation_name = 'Latin1_General_BIN2'
*/
C# version is 4.6
C# sample code (small console based app to insert 1000 records)
===================================================================
Source code:
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace XploreConsoleAlwaysEncrypted
{
class Program
{
private static ClientCredential _clientCredential;
static void InitializeAzureKeyVaultProvider()
{
string clientId = "030393316-aed6-4114-9bc4-fc33970357";
string clientSecret = "l323344444452f33MTtBK05212333g2=";
_clientCredential = new ClientCredential(clientId, clientSecret);
SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);
Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
}
static void Main(string[] args)
{
InitializeAzureKeyVaultProvider();
try
{
DataTable dt = new DataTable();
dt.Columns.Add("CodeId", typeof(string));
dt.Columns.Add("LionLogin", typeof(string));
dt.Columns.Add("FirstName", typeof(string));
dt.Columns.Add("LastName", typeof(string));
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("PersonType", typeof(string));
dt.Columns.Add("AssignmentStatus", typeof(string));
dt.Columns.Add("StartDate", typeof(string));
dt.Columns.Add("HomeOffice", typeof(string));
dt.Columns.Add("JobTitle", typeof(string));
dt.Columns.Add("BusinessUnit", typeof(string));
dt.Columns.Add("Discipline", typeof(string));
dt.Columns.Add("SupervisorName", typeof(string));
dt.Columns.Add("PersonCompCommunicator", typeof(string));
dt.Columns.Add("PersonCompApprover", typeof(string));
dt.Columns.Add("DateTimeStamp", typeof(string));
dt.Columns.Add("BirthDate", typeof(string));
dt.Columns.Add("Gender", typeof(string));
dt.Columns.Add("PreferredName", typeof(string));
dt.Columns.Add("EndDate", typeof(string));
dt.Columns.Add("ProfilePicture", typeof(string));
dt.Columns.Add("BrandFunction", typeof(string));
dt.Columns.Add("Status", typeof(string));
dt.Columns.Add("PersonStaffingPartner", typeof(string));
dt.Columns.Add("Department", typeof(string));
//insert 1000 rows into a data table and then insert it using a stored proc
for (int i=1; i <= 1000; i++)
{
DataRow dr = dt.NewRow();
dr["CodeId"] = i;
dr["LionLogin"] = "junk"+i;
dr["FirstName"] = "junk" + i;
dr["LastName"] = "junk" + i;
dr["Title"] = "junk" + i;
dr["PersonType"] = "junk" + i;
dr["AssignmentStatus"] = "junk" + i;
dr["StartDate"] = "junk" + i;
dr["HomeOffice"] = "junk" + i;
dr["JobTitle"] = "junk" + i;
dr["BusinessUnit"] = "junk" + i;
dr["Discipline"] = "junk" + i;
dr["SupervisorName"] = "junk" + i;
dr["PersonCompCommunicator"] = "junk" + i;
dr["PersonCompApprover"] = "junk" + i;
dr["DateTimeStamp"] = "junk" + i;
dr["BirthDate"] = "junk" + i;
dr["Gender"] = "junk" + i;
dr["PreferredName"] = "junk" + i;
dr["EndDate"] = "junk" + i;
dr["ProfilePicture"] = "junk" + i;
dr["BrandFunction"] = "junk" + i;
dr["Status"] = "junk" + i;
dr["PersonStaffingPartner"] = "junk" + i;
dr["Department"] = "junk" + i;
dt.Rows.Add(dr);
}
Console.WriteLine(dt.Rows.Count);
SqlConnection conn3 = new SqlConnection("Data Source = tcp:srv1.database.windows.net,1433; Initial Catalog = AE_demo; Integrated Security = False; Column Encryption Setting = Enabled; User ID = admin; Password = Password;");
conn3.Open();
SqlCommand cmd = new SqlCommand("Insert_p1", conn3);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@myProfile", dt);
cmd.ExecuteNonQuery();
conn3.Close();
System.Console.WriteLine("*** INSERT operation Successful ****");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
System.Console.ReadKey();
}
public async static Task<string> GetToken(string authority, string resource, string scope)
{
var authContext = new AuthenticationContext(authority);
AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);
if (result == null)
throw new InvalidOperationException("Failed to obtain the access token");
return result.AccessToken;
}
}
}
Thanks,
Sam
March 5, 2018 at 8:19 am
why have you create every single column in the table as varchar(max)? :crazy:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2018 at 2:39 pm
[FirstName] [varchar](max) NULL
Looks like some guy has very long 4 Gb first name and another looser don't have a name at all. 😛
How you can insert Chinese name (Unicode) in your database? Billions of people won't be happy with your database design. 😎
March 5, 2018 at 10:49 pm
Hi Perry & Evgeny,
Its a POC. Prod will not be like that.
Thanks,
Sam
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply