ReadyRoll support for Always Encrypted

adev

Hi,

Our environment:
SQL 2016 On-Prem + Azure SQL
ReadyRoll 1.13.23

After implementing Always Encrypted with Azure KeyVault as the key store, we notice ReadyRoll can no longer compare the encrypted columns and gives the following error:

Error during compare. Could not compare static data: Specified cast is not valid.Inner: Specified cast is not valid., Inner Stack: at System.Data.SqlClient.SqlBuffer.get_SqlString()
at System.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__DisplayClass55_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, Boolean binaryNewDataTypes)
at RedGate.SQLDataCompare.Engine.ComparisonSession.CompareDatabases(IDatabase db1, IDatabase db2, TableMappings mappings, SessionSettings sessionSettings, Boolean doNotBeDefeated)
at ReadyRoll.Comparison.SqlCompare.SqlCompareDataComparison.CompareDatabases(IDatabase source, IDatabase target, ITableMappings mappings)
at ReadyRoll.DataSyncComparisonHelpers.CompareData(IDataComparison data, IDatabase sandboxData, IDatabase shadowData, ITableMappings mappings, ProgressReporterWrapper progressReporterWrapper, DatabaseProjectSettings settings, TaskLoggingHelper logger)
at ReadyRoll.Services.RefreshService.<>c__DisplayClass1_0.<Refresh>b__0(), Stack: at ReadyRoll.Services.RefreshService.Refresh(IDatabaseProject databaseProject, ProgressReporter progressHandler, IProgress`1 progress, Boolean doDataCompare, TaskLoggingHelper logger, String sourceDatabaseName, FileInfo sourceSnap)


It seems that ReadyRoll currently doesn't support Always Encrypted. Can someone please confirm if this is the case?
If so is there a workaround and any plans to add the support in the near future?

Thanks
Walter

dnlnln

Hi Walter,

Sorry that you've run into an issue working with an always encrypted static data table. This is due our data synchronization library (based on SQL Data Compare) not supporting always encrypted tables, and unfortunately there aren't any current plans to implement this. However I've raised this internally and your interest has been logged under SDC-2513.

In terms of a workaround, it may be worth trying the offline static data method (as opposed to the online static data functionality that is built into ReadyRoll).This would involve writing a MERGE statement that contains a VALUE clause with the encrypted data. Please note that the sp_generate_merge open source utility mentioned in that article won't be able to successfully generate the VALUES clause, due to a current limitation in the handling of encrypted columns, so you would need to add that yourself.

I hope this helps.

Best Regards,

Daniel