none
Failed to enable Always Encrypted - Failed to configure the specified encryption settings RRS feed

  • Question

  • We're having some issues setting up Always Encrypted on an SQL Azure DB using Azure Key Vault.

    We've managed to set everything up, including the encryption keys on the azure instance, the issue is on the final step when it

    actually starts to encrypt the table, or tries to in this case.

    We've followed this page for how to set it up:
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault

    We didn't run the azure key vault creation through powershell, we did it through the portal, but did add the key permissions to my user, it managed to generate the server keys just fine.

    Unsure if it needs anything else to proceed.

    the table we're trying to encrypt is the following :

    create table encryption_test(
    	id uniqueidentifier primary key,
    	info nvarchar(256) not null
    )
    

    This is the log generated by the wizard (on SSMS v18.1)

    Jul 10 2019 16:30:43: Log opened. TraceLevel:Informational
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:Source database settings.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	Source server name: godtlevertdbtest.database.windows.net.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	Source database name: CMS.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:Create new master key.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	New master key name: CMK_Auto1.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	New master key in Azure Key Vault\\encryptionDBKeyVault.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:Create new encryption key.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	New encryption key: CEK_Auto1.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:Encrypt column info.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	  Table name: encryption_test.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	  Encryption key name: CEK_Auto1.
    
    Jul 10 2019 16:31:53 [Informational] WizardSummary: Message:	  Encryption type: Deterministic.
    
    Jul 10 2019 16:31:55 [Informational] WorkitemExecution: Message:Work item 'Generate new column master key CMK_Auto1 in Azure Key Vault encryptionDBKeyVault' started..
    
    Jul 10 2019 16:31:55 [Informational] TaskUpdates: Message:Task: 'Generate new column master key CMK_Auto1 in Azure Key Vault encryptionDBKeyVault' -- Status: 'Started' -- Details: 'Task 'Generate new column master key CMK_Auto1 in Azure Key Vault encryptionDBKeyVault' started ....'.
    
    Jul 10 2019 16:31:59 [Informational] TaskUpdates: Message:Task: 'Generate new column master key CMK_Auto1 in Azure Key Vault encryptionDBKeyVault' -- Status: 'Completed' -- Details: 'Task 'Generate new column master key CMK_Auto1 in Azure Key Vault encryptionDBKeyVault' completed'.
    
    Jul 10 2019 16:31:59 [Informational] WorkitemExecution: Message:Work item 'Generate new column master key CMK_Auto1 in Azure Key Vault encryptionDBKeyVault' stopped..
    
    Jul 10 2019 16:31:59 [Informational] WorkitemExecution: Message:Work item 'Generate new column master key CMK_Auto1 in Azure Key Vault encryptionDBKeyVault' completed successfully!.
    
    Jul 10 2019 16:31:59 [Informational] WorkitemExecution: Message:Work item 'Generate new column encryption key CEK_Auto1' started..
    
    Jul 10 2019 16:31:59 [Informational] TaskUpdates: Message:Task: 'Generate new column encryption key CEK_Auto1' -- Status: 'Started' -- Details: 'Task 'Generate new column encryption key CEK_Auto1' started ....'.
    
    Jul 10 2019 16:31:59 [Informational] TaskUpdates: Message:Task: 'Generate new column encryption key CEK_Auto1' -- Status: 'Completed' -- Details: 'Task 'Generate new column encryption key CEK_Auto1' completed'.
    
    Jul 10 2019 16:31:59 [Informational] WorkitemExecution: Message:Work item 'Generate new column encryption key CEK_Auto1' stopped..
    
    Jul 10 2019 16:31:59 [Informational] WorkitemExecution: Message:Work item 'Generate new column encryption key CEK_Auto1' completed successfully!.
    
    Jul 10 2019 16:31:59 [Informational] WorkitemExecution: Message:Work item 'Performing encryption operations' started..
    
    Jul 10 2019 16:31:59 [Informational] TaskUpdates: Message:Task: 'Performing encryption operations' -- Status: 'Started' -- Details: 'Task 'Performing encryption operations' started ....'.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Pending: Initializing deployment.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Pending: Analyzing deployment plan.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Pending: Updating database.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Pending: Creating deployment plan.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Pending: Verifying deployment plan.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Pending: Deploying package to database.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Running: Creating deployment plan.
    
    Jul 10 2019 16:32:34 [Informational] DacFxMigration: Message:DacFx state update: Running: Initializing deployment.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Initializing deployment.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Creating deployment plan.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Verifying deployment plan.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Deploying package to database.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Pending: Initializing deployment.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Pending: Analyzing deployment plan.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Pending: Updating database.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Pending: Creating deployment plan.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Pending: Verifying deployment plan.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Pending: Deploying package to database.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Running: Creating deployment plan.
    
    Jul 10 2019 16:32:53 [Informational] DacFxMigration: Message:DacFx state update: Running: Initializing deployment.
    
    Jul 10 2019 16:33:03 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Initializing deployment.
    
    Jul 10 2019 16:33:03 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Creating deployment plan.
    
    Jul 10 2019 16:33:03 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Verifying deployment plan.
    
    Jul 10 2019 16:33:03 [Informational] DacFxMigration: Message:DacFx state update: Faulted: Deploying package to database.
    
    Jul 10 2019 16:33:03 [Informational] TaskUpdates: Message:Task: 'Performing encryption operations' -- Status: 'Failed' -- Details: 'Task failed due to following error: Failed to configure the specified encryption settings.'.
    
    Jul 10 2019 16:33:03 [Informational] WorkitemExecution: Message:Work item 'Performing encryption operations' stopped..
    
    Jul 10 2019 16:33:03 [Error] WorkitemExecution: Message:Work item 'Performing encryption operations' did not complete. Details: Failed to configure the specified encryption settings..
    
    Jul 10 2019 16:33:03 [Error] WorkitemExecution: Message:Inner exception: Microsoft.SqlServer.Dac.DacServicesException 
     Details: An error occurred during deployment plan generation. Deployment cannot continue.
    Error SQL72018: Permission could not be imported but one or more of these objects exist in your source.
    .
    
    Jul 10 2019 16:33:03 [Error] WorkitemExecution: Message:Inner exception: Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException 
     Details: Errors occurred while modeling the target database.  Deployment can not continue..
    
    Jul 10 2019 16:33:03 [Informational] Log Closed: Message:Job processing completed.
    
    


    Any help is truly appreciated :) 

    Thank you,

    João Récio

    Wednesday, July 10, 2019 2:39 PM

All replies

  • Could you try doing via Powershell. I have a hunch this is happening due to SSMS 18.1 the error message 

    Details: An error occurred during deployment plan generation. Deployment cannot continue.
    Error SQL72018: Permission could not be imported but one or more of these objects exist in your source.
    .

    Meanwhile I will try to reproduce the issue.

    PS: DID you made sure you had admin permission also could you try running SSMS as admin and then try again.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, July 10, 2019 5:41 PM
  • Hi,

    We're using the admin user to perform the encryption.

    I just tried running SSMS 18.1 in admin mode and got the exact same result.

    Also tried the powershell script with similar results.

    I also tried SSMS 17.8 before.

    Are there any known limitations to Always encrypted? 

    Best Regards,

    João Récio

    Thursday, July 11, 2019 7:16 AM
  • 11/07/2019 09:35:29		INFO		MainThread		Logger initialized.
    11/07/2019 09:35:29		INFO		MainThread		Acquiring database model and preparing data migration.
    11/07/2019 09:36:11		ERROR		MainThread		System.AggregateException: Failed to configure the specified encryption settings. ---> Microsoft.SqlServer.Dac.DacServicesException: An error occurred during deployment plan generation. Deployment cannot continue.
    Error SQL72018: Permission could not be imported but one or more of these objects exist in your source.
     ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Errors occurred while modeling the target database.  Deployment can not continue.
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.ThrowIfErrors(String message, ErrorManager errors, Object category)
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.OnLoad(ErrorManager errors, DeploymentEngineContext context)
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.PrepareModels()
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator()
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(Action`1 msgHandler)
       at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
       at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
       at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
       at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
       at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
       at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
       at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
       at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.ApplyChanges(String connectionString, Database database, IDictionary`2 updatedColumns, Nullable`1 cancellationToken, EncryptionExecutionOptions options)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.ApplyChanges(String connectionString, Database database, IDictionary`2 updatedColumns, Nullable`1 cancellationToken, EncryptionExecutionOptions options)
       at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.SetColumnEncryptionSchema(String connectionString, Database database, IDictionary`2 targetSchema, Nullable`1 cancellationToken, EncryptionExecutionOptions options)
    ---> (Inner Exception #0) Microsoft.SqlServer.Dac.DacServicesException: An error occurred during deployment plan generation. Deployment cannot continue.
    Error SQL72018: Permission could not be imported but one or more of these objects exist in your source.
     ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Errors occurred while modeling the target database.  Deployment can not continue.
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.ThrowIfErrors(String message, ErrorManager errors, Object category)
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.OnLoad(ErrorManager errors, DeploymentEngineContext context)
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.PrepareModels()
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator()
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(Action`1 msgHandler)
       at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
       at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
       at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
       at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
       at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
       at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
       at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
       at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.ApplyChanges(String connectionString, Database database, IDictionary`2 updatedColumns, Nullable`1 cancellationToken, EncryptionExecutionOptions options)<---
    
    ---> (Inner Exception #1) Microsoft.SqlServer.Dac.DacServicesException: An error occurred during deployment plan generation. Deployment cannot continue.
    Error SQL72018: Permission could not be imported but one or more of these objects exist in your source.
     ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Errors occurred while modeling the target database.  Deployment can not continue.
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.ThrowIfErrors(String message, ErrorManager errors, Object category)
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.OnLoad(ErrorManager errors, DeploymentEngineContext context)
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.PrepareModels()
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator()
       at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(Action`1 msgHandler)
       at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
       at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
       at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
       at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
       at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
       at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
       at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
       at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.RollbackDacpac(String pathName, Database database, DacServices dacService, Boolean deploymentAttempted)
       at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.ApplyChanges(String connectionString, Database database, IDictionary`2 updatedColumns, Nullable`1 cancellationToken, EncryptionExecutionOptions options)<---
    
    11/07/2019 09:36:11		INFO		MainThread		Deploying the specified encryption settings completed in 0d:0h:0m:42s.
    

    Log file generated by Set-SqlColumnEncryption cmdlet

    Best Regards,

    João Récio

    Thursday, July 11, 2019 7:37 AM
  • Hi Joao,

    I am not able to reproduce the issue for me the encryption went fine. I had used similar table and inserted 5 rows . NOTE I did not encrypted column uniqueidentifier because there is NO point in encrypting random key.

    Could you try with only encrypting info column

    I did not created key vault as shown in article I simply created it from Azure portal and changed access policies I do not think it matters here.

    Attched is the log

    Jul 12 2019 00:17:39: Log opened. TraceLevel:Informational
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:Source database settings.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	Source server name: shashank.database.windows.net.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	Source database name: AlwaysEncryptedTest.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:Create new master key.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	New master key name: CMK_Auto2.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	New master key in Azure Key Vault\\AzKeyVault621.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:Create new encryption key.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	New encryption key: CEK_Auto1.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:Encrypt column info.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	  Table name: encryption_test.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	  Encryption key name: CEK_Auto1.
    
    Jul 12 2019 00:18:18 [Informational] WizardSummary: Message:	  Encryption type: Deterministic.
    
    Jul 12 2019 00:18:19 [Informational] WorkitemExecution: Message:Work item 'Generate new column master key CMK_Auto2 in Azure Key Vault AzKeyVault621' started..
    
    Jul 12 2019 00:18:19 [Informational] TaskUpdates: Message:Task: 'Generate new column master key CMK_Auto2 in Azure Key Vault AzKeyVault621' -- Status: 'Started' -- Details: 'Task 'Generate new column master key CMK_Auto2 in Azure Key Vault AzKeyVault621' started ....'.
    
    Jul 12 2019 00:18:21 [Informational] TaskUpdates: Message:Task: 'Generate new column master key CMK_Auto2 in Azure Key Vault AzKeyVault621' -- Status: 'Completed' -- Details: 'Task 'Generate new column master key CMK_Auto2 in Azure Key Vault AzKeyVault621' completed'.
    
    Jul 12 2019 00:18:21 [Informational] WorkitemExecution: Message:Work item 'Generate new column master key CMK_Auto2 in Azure Key Vault AzKeyVault621' stopped..
    
    Jul 12 2019 00:18:21 [Informational] WorkitemExecution: Message:Work item 'Generate new column master key CMK_Auto2 in Azure Key Vault AzKeyVault621' completed successfully!.
    
    Jul 12 2019 00:18:21 [Informational] WorkitemExecution: Message:Work item 'Generate new column encryption key CEK_Auto1' started..
    
    Jul 12 2019 00:18:21 [Informational] TaskUpdates: Message:Task: 'Generate new column encryption key CEK_Auto1' -- Status: 'Started' -- Details: 'Task 'Generate new column encryption key CEK_Auto1' started ....'.
    
    Jul 12 2019 00:18:24 [Informational] TaskUpdates: Message:Task: 'Generate new column encryption key CEK_Auto1' -- Status: 'Completed' -- Details: 'Task 'Generate new column encryption key CEK_Auto1' completed'.
    
    Jul 12 2019 00:18:24 [Informational] WorkitemExecution: Message:Work item 'Generate new column encryption key CEK_Auto1' stopped..
    
    Jul 12 2019 00:18:24 [Informational] WorkitemExecution: Message:Work item 'Generate new column encryption key CEK_Auto1' completed successfully!.
    
    Jul 12 2019 00:18:24 [Informational] WorkitemExecution: Message:Work item 'Performing encryption operations' started..
    
    Jul 12 2019 00:18:24 [Informational] TaskUpdates: Message:Task: 'Performing encryption operations' -- Status: 'Started' -- Details: 'Task 'Performing encryption operations' started ....'.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Pending: Initializing deployment.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Pending: Analyzing deployment plan.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Pending: Updating database.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Pending: Creating deployment plan.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Pending: Verifying deployment plan.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Pending: Deploying package to database.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Running: Creating deployment plan.
    
    Jul 12 2019 00:19:22 [Informational] DacFxMigration: Message:DacFx state update: Running: Initializing deployment.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Completed: Initializing deployment.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Completed: Creating deployment plan.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Running: Verifying deployment plan.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Running: Analyzing deployment plan.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Completed: Analyzing deployment plan.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Completed: Verifying deployment plan.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Running: Deploying package to database.
    
    Jul 12 2019 00:20:45 [Informational] DacFxMigration: Message:DacFx state update: Running: Updating database.
    
    Jul 12 2019 00:21:01 [Informational] DacFxMigration: Message:Data migration for table '[dbo].[encryption_test]' started..
    
    Jul 12 2019 00:21:03 [Informational] DacFxMigration: Message:DacFx state update: Running: Processing Export..
    
    Jul 12 2019 00:21:03 [Informational] DacFxMigration: Message:DacFx state update: Pending: Processing Table '[dbo].[encryption_test]'..
    
    Jul 12 2019 00:21:05 [Informational] DacFxMigration: Message:DacFx state update: Running: Processing Table '[dbo].[encryption_test]'..
    
    Jul 12 2019 00:21:07 [Informational] DacFxMigration: Message:Processing Table '[dbo].[encryption_test]'. 25.00 % done..
    
    Jul 12 2019 00:21:08 [Informational] DacFxMigration: Message:Processing Table '[dbo].[encryption_test]'. 100.00 % done..
    
    Jul 12 2019 00:21:08 [Informational] DacFxMigration: Message:DacFx state update: Completed: Processing Table '[dbo].[encryption_test]'..
    
    Jul 12 2019 00:21:08 [Informational] DacFxMigration: Message:DacFx state update: Completed: Processing Export..
    
    Jul 12 2019 00:21:14 [Informational] DacFxMigration: Message:DacFx state update: Completed: Updating database.
    
    Jul 12 2019 00:21:14 [Informational] DacFxMigration: Message:DacFx state update: Completed: Deploying package to database.
    
    Jul 12 2019 00:21:14 [Informational] TaskUpdates: Message:Task: 'Performing encryption operations' -- Status: 'Completed' -- Details: 'Task 'Performing encryption operations' completed'.
    
    Jul 12 2019 00:21:14 [Informational] WorkitemExecution: Message:Work item 'Performing encryption operations' stopped..
    
    Jul 12 2019 00:21:14 [Informational] WorkitemExecution: Message:Work item 'Performing encryption operations' completed successfully!.
    
    Jul 12 2019 00:21:14 [Informational] Log Closed: Message:Job processing completed.
    


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, July 11, 2019 7:02 PM
  • Hi,

    I'm also only encrypting the data column, I'm not encrypting the primary key/id.

    The issue still remains, I've tried multiple dbs, but they all have the same issue.

    I think this may be related to tables  (possibly external tables) / triggers that no longer work but are still in the db schema / model, possibly from using datasync.

    I also have another question related to this, the keys used on the encryption process seem to be stored on the DB itself, does this mean that if I create a DB with an encrypted table in Env 1 and then copy it to Env 2, assuming that Env 2 has access to the key vault everything should work out of the box?

    This is relevant to us as we usually have data dumps from PRD to STG for example, I'm concerned about the creation of dbs from copy not working with encrypted data, etc.

    Is it possible that this doesn't work because of the datasync not transitioning from PRD to STG therefore creating an invalid schema ?

    Best Regards,

    João Récio

    Friday, July 12, 2019 8:20 AM
  • Just validated it,

    If I don't have external tables and/or remainders of datasync then the encryption works fine.

    The datasync is not targeting the test table on the other dbs.

    • Proposed as answer by robhalabicki Tuesday, November 12, 2019 7:54 PM
    Friday, July 12, 2019 8:43 AM
  • Just validated it,

    If I don't have external tables and/or remainders of datasync then the encryption works fine.

    The datasync is not targeting the test table on the other dbs.

    You also mentioned triggers in your previous response so yes triggers might also pose issue during encryption. Regarding external tables yes it should give exception because it has no knowledge of certificate for other tables. For complete limitation read This

     also have another question related to this, the keys used on the encryption process seem to be stored on the DB itself, does this mean that if I create a DB with an encrypted table in Env 1 and then copy it to Env 2, assuming that Env 2 has access to the key vault everything should work out of the box?

    Not exactly quoting from link share above

    The Database Engine stores encryption configuration for each column in database metadata. Note, however, the Database Engine never stores or uses the keys of either type in plaintext. It only stores encrypted values of column encryption keys and the information about the location of column master keys, which are stored in external trusted key stores, such as Azure Key Vault, Windows Certificate Store on a client machine, or a hardware security module.

    Without certificates for other database it is not going to work


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, July 12, 2019 9:21 AM
  • Hi,

    The objects that I mentioned before (triggers, external tables etc) are not the target of the encryption and the table used has no dependencies on these objects

    I'm still unable to create the encryption on a fresh table with no dependencies on any database of my system, except for one that is not using Datasync.


    Monday, July 15, 2019 8:33 AM
  • Just validated it,

    If I don't have external tables and/or remainders of datasync then the encryption works fine.

    The datasync is not targeting the test table on the other dbs.

    I am having the same issue as you are.  If you have datasync running in the db you cannot use always encrypted.  Period.   This needs to be updated in the documentation (and really fixed).

    This is absolute lunacy

    Tuesday, November 12, 2019 7:54 PM
  • I want to follow up with this issue by pointing out the following documentation. 

    Can Data Sync sync encrypted tables and columns

    • If a database uses Always Encrypted, you can sync only the tables and columns that are not encrypted. You can't sync the encrypted columns, because Data Sync can't decrypt the data.
    • If a column uses Column-Level Encryption (CLE), you can sync the column, as long as the row size is less than the maximum size of 24 Mb. Data Sync treats the column encrypted by key (CLE) as normal binary data. To decrypt the data on other sync members, you need to have the same certificate.

    Are you saying that you are attempting to sync tables that are not encrypted nor have any encrypted columns, or that you want to encrypt tables/columns that are not included in a data sync configuration? The documentation is somewhat clear on this and want to see if there is additional clarity required, as I can bring this to the attention of the product group to address any confusion.

    Thank you,

    Mike

    Wednesday, November 13, 2019 10:10 PM
    Moderator
  • 100% correct,   I build a brand new table as per the documentation (dbo.patients) and try to encrypt the column.  It is not part of the sync group.    Trying to encrypt the column will fail.

    Creating a brand new DB and doing the steps works.

    Monday, November 18, 2019 12:55 PM