0

I have two Always Encrypted enabled databases using Azure Key Vault for the keys. One is in Azure and the other one is local. When I try to use Data Comparison it crashes Visual Studio and VS restarts. It does not crash when the database did not have any AE enabled tables. I have tried it with and without “Column Encryption Setting=enabled”. I use Data Comparison to pull our databases down from Azure and test from my local database. I’m using the April 2016 version.

Thanks in advance for any advice!

The Event Viewer reports:

Log Name:      Application
Source:        .NET Runtime
Date:          5/5/2016 5:56:26 PM
Event ID:      1026
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      DESKTOP-64U74F2
Description:
Application: devenv.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.InvalidCastException
   at System.Data.SqlClient.SqlBuffer.get_SqlString()
   at System.Data.SqlClient.SqlDataReader.GetSqlString(Int32)
   at Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.SqlTableReader.get_CurrentRow()
   at Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.ComparisonSession.CompareTable(Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.ITableReader, Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.ITableReader, Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.TableComparisonSetting, Int64, Int64 ByRef, Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.SessionSettings)
   at Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.ComparisonSession.CompareDatabases(Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.Database, Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.Database, Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.TableComparisonSettings, Microsoft.Data.Tools.Schema.Utilities.Sql.Compare.Data.SessionSettings)
   at Microsoft.VisualStudio.Data.Tools.DataCompare.UI.Editors.DataCompareDataPopulationJob.Run()
   at Microsoft.Data.Tools.Schema.Utilities.Sql.Common.Threading2.JobManager+JobBase.<Microsoft.Data.Tools.Schema.Utilities.Sql.Common.Threading2.JobManager.IManageableJob.RunImpl>b__22_0(System.Object)
   at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)
   at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)
   at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
   at Microsoft.Data.Tools.Schema.Utilities.Sql.Common.Threading2.JobManager+JobBase.Microsoft.Data.Tools.Schema.Utilities.Sql.Common.Threading2.JobManager.IManageableJob.RunImpl(System.Object)
   at System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(System.Object)
   at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)
   at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)
   at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()
   at System.Threading.ThreadPoolWorkQueue.Dispatch()
   at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()
Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
Eric Henson
  • 3
  • 1
  • 1
    Hi Eric, I'm sorry to hear you ran into this issue. We're actively working on SSDT support for AlwaysEncrypted but this is clearly a bug that needs addressing. I'll forward on to our team but for now, does unchecking the table before doing a comparison allow other tables to be compared? [Disclosure - I work on the SSDT team] – Kevin Cunnane May 09 '16 at 10:25
  • You are correct in that if I do not check the encrypted columns the compare will run to completion. I did a test of comparing just one table with just one encrypted column and it failed as expected. – Eric Henson May 14 '16 at 13:16
  • Is there a method by which I can export my Azure development SQL database that is Always Encrypted enabled to my local database server so I can have a fast local access to my data and still use AE on the local as well? – Eric Henson May 14 '16 at 13:43
  • Export from SSMS should work and allow restoring locally. When exporting we export and re-import the data in its encrypted form. I'd suggest trying it out and if you have issues file a Connect bug (I wouldn't expect any unless you have Azure-only syntax, which is rare). Since the keys are client-side in any case, you should be able to get access to your data just as it would be in the cloud. – Kevin Cunnane May 16 '16 at 16:26

1 Answers1

0

A workaround is to uncheck the encrypted column, which should allow the comparison to complete without this crash happening. We (the SSDT team) are working on a more elegant solution.

Steven Green
  • 3,387
  • 14
  • 17