I'm having following error:
Operandentypkollision: nvarchar(max) ist inkompatibel mit nvarchar(max) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name =
Environment:
I've created a table "TestTable" with two columns on the remoteServer, test_id (pk, auto_increment) and test_data (nvarchar(max)). I've enabled sql always encrypted via wizard and testet the encryption, everythings works fine.
Now I've copied the MDF from the remoteServer to a local client with LocalDB installed and attached the MDF. I've copied the encryption-cert to the local machine personal current user store and testet the access, everything works fine as well.
I've added following connectionstrings to my vb winforms .net 4.7.2 application app.config:
<connectionStrings>
<add name="local" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\path\to.mdf;Column Encryption Setting=enabled;Initial Catalog=MyCatalog;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="remote" connectionString="Data Source=myserver.some.where\PRODDB;Initial Catalog=MyCatalog;Column Encryption Setting=enabled;Persist Security Info=True;User ID=xxxx;Password=xxxx"
providerName="System.Data.SqlClient" />
</connectionStrings>I've bound both connections to a grid and successful the same results. From my point of view are both connections strings correct and de decryption is working aswell.
The Problem:
I want to use microsoft sync framework to keep those two databases in sync.
I use following code:
Public Class dbSync
Private operations As New ConnectionProtection(Application.ExecutablePath)
Public Sub doSync()
operations.DecryptFile()
Dim localStr As String = ConfigurationManager.ConnectionStrings("localServer").ConnectionString
Dim OnlineStr As String = ConfigurationManager.ConnectionStrings("remoteServer").ConnectionString
sync("TestTable", localStr, OnlineStr)
operations.EncryptFile()
End Sub
Private Sub Init(ByVal table As String, ByVal localStr As String, ByVal OnlineStr As String)
Try
Using servCon As SqlConnection = New SqlConnection(OnlineStr)
Using localCon As SqlConnection = New SqlConnection(localStr)
Dim scopeDesc As DbSyncScopeDescription = New DbSyncScopeDescription(table)
Dim tableDesc As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(table, servCon)
scopeDesc.Tables.Add(tableDesc)
Dim servProv As SqlSyncScopeProvisioning = New SqlSyncScopeProvisioning(servCon, scopeDesc)
servProv.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting)
servProv.Apply()
Dim localProv As SqlSyncScopeProvisioning = New SqlSyncScopeProvisioning(localCon, scopeDesc)
localProv.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting)
localProv.Apply()
End Using
End Using
Catch ex As Exception
End Try
End Sub
Private Sub sync(ByVal scope As String, ByVal localStr As String, ByVal OnlineStr As String)
Init(scope, localStr, OnlineStr)
Using servCon As SqlConnection = New SqlConnection(OnlineStr)
Using localCon As SqlConnection = New SqlConnection(localStr)
Dim agent As SyncOrchestrator = New SyncOrchestrator
agent.LocalProvider = New SqlSyncProvider(scope, localCon)
agent.RemoteProvider = New SqlSyncProvider(scope, servCon)
agent.Direction = SyncDirectionOrder.DownloadAndUpload
Dim syncRelRemote As RelationalSyncProvider = TryCast(agent.RemoteProvider, RelationalSyncProvider)
AddHandler syncRelRemote.SyncProgress, AddressOf dbProvider_SyncProgress
Dim syncRelLocalFailed As RelationalSyncProvider = TryCast(agent.LocalProvider, RelationalSyncProvider)
AddHandler syncRelLocalFailed.ApplyChangeFailed, AddressOf dbProvider_SyncProcessFailed
Dim syncRelRemoteFailed As RelationalSyncProvider = TryCast(agent.LocalProvider, RelationalSyncProvider)
AddHandler syncRelRemoteFailed.ApplyChangeFailed, AddressOf dbProvider_SyncProcessFailed
agent.Synchronize()
End Using
End Using
CleanUp(scope, localStr, OnlineStr)
End Sub
Private Shared Sub dbProvider_SyncProgress(ByVal sender As Object, ByVal e As DbSyncProgressEventArgs)
End Sub
Private Shared Sub dbProvider_SyncProcessFailed(ByVal sender As Object, ByVal e As DbApplyChangeFailedEventArgs)
End Sub
Public Enum DbConflictType
ErrorsOccured = 0
LocalUpdateRemoteUpdate = 1
LocalUpdateRemoteDelete = 2
LocalDeleteRemoteUpdate = 3
LocalInsertRemoteInsert = 4
LocalDeleteRemoteDelete = 5
End Enum
Private Shared Sub CleanUp(ByVal scope As String, ByVal localStr As String, ByVal OnlineStr As String)
Using servCon As SqlConnection = New SqlConnection(OnlineStr)
Using localCon As SqlConnection = New SqlConnection(localStr)
Dim serverDep As SqlSyncScopeDeprovisioning = New SqlSyncScopeDeprovisioning(servCon)
Dim localDep As SqlSyncScopeDeprovisioning = New SqlSyncScopeDeprovisioning(localCon)
serverDep.DeprovisionScope(scope)
serverDep.DeprovisionStore()
localDep.DeprovisionScope(scope)
localDep.DeprovisionStore()
End Using
End Using
End Sub
End ClassThe errors happens in line:
servProv.Apply()
while trying to provision.
When I try everything without using always encrypted, the syncronization works perfectly, the trackingtables are created, everything is fine.
What makes me wondering is that when I watch the var tableDesc
Dim tableDesc As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(table, servCon)is that the attribute tableDesc.columns(1).Type is nvarchar, regardless if I change servCon with localCon. So the type seems to be getting correctly, but while applying the provisioning i get the error.
I have the feeling that I need to adjust tableDesc in some way, but couldn't find what.
I hope I managed to describe my problem properly, this is my first stackOverflow-post (yeah :-) )
Given that Sync Framework is completely out of support and not updated in the last 10 years- no you cannot. And welcome.