I have a temp table in Azure Synapse with the following code:
create TABLE #secrets
[c1] varbinary(8000) NULL
I have created a certificate and a key and trying to insert encrypted value in this table
OPEN SYMMETRIC KEY TestCertKey
DECRYPTION BY CERTIFICATE TestCert;
insert into #secrets
When i check the output of select statement i am getting the encrypted varbinary record:
select ENCRYPTBYKEY(KEY_GUID('TestCertKey'),'TestValue') - Output: 0x00172AC940B5E343952A5FFAB50E8137020000003564FB35C4DEF4BD8EBBD9598513836A8D6AB1EB2A1A76FC20D5BC4F934BC9EAF90C52477E966D66AAC8E0DC526B5AB3
But when insert statement is getting executed, NULL value is inserted instead of this varbinary value
The same statement is working fine in Azure SQL DB and other SQL DB's but not working in synapse.
Am i missing something here or something specific related to synapse has to be done?