SQL Server Always Encrypted Column Encryption

cgountanis

I enabled a single column as encrypted and everything from SSMS to NET code works fine but I get error from RedGate Source Control when opening SSMS > Commit and on load. Guessing you guys need to add some support for Always Encrypted?

Specified cast is not valid.

   at Microsoft.Data.SqlClient.SqlBuffer.get_SqlString()

   at Microsoft.Data.SqlClient.SqlDataReader.GetSqlString(Int32 i)

   at RedGate.SQLDataCompare.Engine.DCDataReader.GetSqlString(Int32 i)

   at RedGate.SQLDataCompare.Engine.TableReaderHelper.ProcessRowDataEx(IDCDataReader reader, Int32& sqlIndex, Int32 fieldIndex, TypeData td, ComparisonOptions comparisonOptions, SQLVersion version, Boolean binaryNewDataTypes, FieldPairs allPairs, Boolean runOnTwo)

   at RedGate.SQLDataCompare.Engine.TableReaderHelper.ProcessRowData(IDCDataReader reader, TypeData td, ComparisonOptions comparisonOptions, SQLVersion version, Boolean binaryNewDataTypes, FieldPairs allPairs, Boolean runOnTwo, Object[] values)

   at RedGate.SQLDataCompare.Engine.SqlTableReader.get_CurrentRow()

   at RedGate.SQLDataCompare.Engine.ComparisonSession.CachedRowWithMetaData..ctor(ITableReader reader)

   at RedGate.SQLDataCompare.Engine.ComparisonSession.DataRowProducer(ITableReader tableReader, CachedRowCollection producedRows, CancellationTokenSource abortOnFailureTokenSource, ProgressPercentageTracker progress)

   at RedGate.SQLDataCompare.Engine.ComparisonSession.<>c__DisplayClass50_0.<CompareTable>b__0()

   at System.Threading.Tasks.Task.InnerInvoke()

   at System.Threading.Tasks.Task.Execute()

   at RedGate.SQLDataCompare.Engine.ComparisonSession.CompareTable(ITableReader reader1, ITableReader reader2, TableMapping mapping, ProgressPercentageTracker progress)

   at RedGate.SQLDataCompare.Engine.ComparisonSession.ComparePairOfTables(SessionSettings sessionSettings, TableMapping mapping, ProgressPercentageTracker progress, ITableReader& r1, ITableReader& r2)

   at RedGate.SQLDataCompare.Engine.ComparisonSession.CompareMapping(SessionSettings sessionSettings, TableMapping mapping, ProgressPercentageTracker progress)

   at RedGate.SQLDataCompare.Engine.ComparisonSession.CompareDatabases(IDatabase db1, IDatabase db2, TableMappings mappings, SessionSettings sessionSettings, Boolean doNotBeDefeated)

   at RedGate.SQLSourceControl.Engine.CompareEngineImpl.DataCompare.DataCompareComparisonSession.CompareDatabases(ICompareDatabase db1, ICompareDatabase db2, IDataCompareSchemaMappings mappings)

   at RedGate.SQLSourceControl.Engine.Diff.SqlDataCompare.<>c__DisplayClass0_0.<CompareDatabasesWithCancel>b__0()

   at RedGate.SQLSourceControl.Engine.Utils.Task.SharedUtilsUtils.DoActionWithCancel(ICancellableOperationStatus status, ICancellable cancellable, Action action)

   at RedGate.SQLSourceControl.Engine.Diff.SqlDataCompare.CompareDatabasesWithCancel(ICancellableOperationStatus status, ICompareDatabase db1, ICompareDatabase db2, IDataCompareSchemaMappings mappings)

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.LiveToWorkingBaseComparisonFactory.GetRegistered(ICompareDatabase live, ICompareDatabase workingBase, IDataCompareSchemaMappings schemaMappings, ICancellableOperationStatus status)

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.DataCompareSessionWrapper.GetDataCompareLiveToWorkingBase(ICancellableOperationStatus status, DatabaseObjectIdDictionary`1 dataIndexDifferencesHeadToWorkingBase)

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.DataCompareSessionWrapper.CompareData(IEnumerable`1 tableObjectIdsBeingDropped, ICancellableOperationStatus status)

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.DataDifferencesGetter.GetDataDifferences()

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.ThreeWayDiffSession.GetDataDifferences(ThreeWorkspaceRegistrar registrar, DatabaseObjectIdDictionary`1 indexedDifferencesLiveToWorkingBase)

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.ThreeWayDiffSession.BuildCommon()

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.DiffBuilder.GetChangeSetResolvingNoOps[T](String source, IBoundDatabase boundDatabase, IChangeSetFactory`1 changeSetFactory, ICancellableOperationStatus status, SourceControlOperation sourceControlOperation)

   at RedGate.SQLSourceControl.Engine.Diff.DiffBuildSessions.DiffBuilder.BuildToCommitChangeSet(IBoundDatabase boundDatabase, ICancellableOperationStatus status)

   at RedGate.SQLSourceControl.Engine.Diff.BoundDatabase.<GetCancellableCommitableChanges>b__24_0(ICancellableOperationStatus status)

   at RedGate.SQLSourceControl.Engine.Utils.Task.MutexedCancellableOperation`1.<>c__DisplayClass7_0.<Invoke>b__0()

   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperationBase.PerformAction(Action action)

   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperationBase.InvokeWithTracker(Action action)

   at RedGate.SQLSourceControl.Engine.Utils.Task.MutexedCancellableOperation`1.Invoke()

   at RedGate.SQLSourceControl.Engine.Utils.Task.CancellableOperationTypeConverter`2.Invoke()

   at RedGate.SQLSourceControl.CommonUI.Controls.CancellableControl`2.<>c__DisplayClass30_0.<InvokeOperation>b__1()

   at RedGate.SQLSourceControl.Engine.Utils.ErrorReporting.ErrorReporterBase.Do(Action toDo, Predicate`1 isFatalPredicate, Boolean rethrow)

Kurt_M

Hi @cgountanis

I'd have to agree with your comments. The only thing I can add to this, which may/may not be useful to you, is you could potentially work around this using pre-deployment scripts in your SOC project. You would be able to have a build trigger on the source control changes, add a step to add the encryption key, and then add in whatever other changes you would require afterwards.

cgountanis

This is only an issue if you have it combined with static data and you have option "check for changes to static data" enabled. Which I guess, makes sense if one of the static checked tables has an encrypted column because you cannot share static data with SQL Server encrypted values between developers with different certificates/keys. If there is a work around this would most likely be insecure due to the data/scripts having secure data in plaintext. Am I on the right track here? Best guess, you should not have any tables with encrypted data selected as static data.