Always encryption errors

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  March 26, 2018 at 7:10 am

  #410359

  Hi All,

  Need some help on configuring Always encryption keys using powershell. I am unable to encrypt data.

  I opened powershell ISE and execute below script. We are performing deployment testing for always encrypted feature for Azure sql db.

  This script basically creates encryption keys i.e CMK and CEK for Always encryption on Azure SQL db.
  CMK is created on azure keyvault. Basically followed below script.
  https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-keys-using-powershell

  I used ensured the live account has permissions to create the key on Azure key vault.
  So, the script executes as expected. it creates keys and I am able to see those in SSMS when I expand the
  db - > Security - > Always encryption keys.
  CMK name = > CMK_Auto1
  CEK name = > CEK_Auto1
  Now the problem, I am unable to encrypt the table in ssms.

  
  I have connected to ssms with options "Column Encryption Setting=Enabled".
  SSMS version is 17.4.
  
  
  

  
  next, i create a table and I want the ssn column should be encrypted.

  create table emp
  (empno int,
  ename varchar(100),
  ssn varchar(100)
  )

  insert into emp
  select 101,'manu','111-111-111'
  union all
  select 102,'anu','222-222-222'

  Now, right clicked the emp table -> encrypt columns -> choosen "ssn" , determintic encryption type, CEK_Auto1 as column encryption key
  and click next, for CMK configuration wizard -> it says "No additional information is necessary because you are using existing keys."
  and click "Next" and "Finish" to proceed with encryption.

  It throws me below error : Can anyone suggest what could be the problem. I am thinking if the CEK is is properly mapped to CEK or not becuase the summary text before encryption is started doesnt say anything about CMK. correct me if am wrong.

  TITLE: Microsoft SQL Server Management Studio
  ------------------------------

  Failed to decrypt a column encryption key using key store provider: 'AZURE_KEY_VAULT'. Verify the properties of the column encryption key and its column master key in your database. The last 10 bytes of the encrypted column encryption key are: '02-6B-03-A8-A7-3B-06-FB-2B-88'.
  One or more errors occurred.

  ------------------------------
  BUTTONS:

  OK
  ------------------------------

  Thanks,

  Sam