0

We are trying to implement Always Encrypted on our Sql server 2016. The column that we want to encrypt is XML type which we know is not supported, thus we have created a varbinary column instead. We have managed to encrypt it. Now, we wanted to convert the decrypted data from the varbinary to XML type with this query: select top 10 convert(xml, datavalue2) FROM [TestDB].[dbo].[DataCalc]

For this conversion we are getting an error: Explicit conversion from data type varbinary(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '*******', column_encryption_key_database_name = 'TestDB') to xml is not allowed.

We have tried to do that with nVarchar and Varchar as well and got the same error.

Thanx

Yaniv
  • 11

1 Answers1

1

Encryption/decryption of Always Encrypted columns is done by by client applications via an always encrypted enabled driver. Since data are not decrypted on the server, you can't access the decrypted values needed for CAST/CONVERT in T-SQL. The conversion will need to bee done in the client application, where the decrypted values are available.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Thank you for your reply. One of our clients will be SSMS. We have set "Column Encryption Setting = Enabled" in the connection parameters, so we see the data decrypted, but cannot do any thing to convert it to other types. – Yaniv Jun 09 '20 at 13:17
  • @Yaniv, SSMS is a generic client that doesn't know the `varbinary` value contains xml. You will need a custom app or PowerShell script to convert the binary value to XML. – Dan Guzman May 26 '21 at 10:42