CODEWITHSUNDEEP
sql-serverencryptiondata-conversionalways-encrypted
Yaniv
Yaniv

Reputation: 11

Unable to convert varbinary encrypted column with Always encrypted to any type

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

Upvotes: 0

Views: 1332

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

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.

Upvotes: 1

Related Questions