Options

SQL Server Always Encrypted Column Encryption

cgountaniscgountanis Posts: 2 New member
edited July 31, 2023 5:49PM in SQL Source Control
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)
Tagged:
· Share on Twitter

Best Answer

  • Options
    Kurt_MKurt_M Posts: 178 Silver 1
    Answer ✓
    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.

    Kind regards,

    Kurt McCormick
    Product Support Engineer, Redgate

    Need help? Take a look at our Help Center

    · Share on Twitter

Answers

  • Options
    cgountaniscgountanis Posts: 2 New member
    edited July 31, 2023 6:06PM
    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.


    · Share on Twitter
Sign In or Register to comment.