About encryption in Azure MI.

Question

Hello,

I ran into an in-process problem with encrypted data between Azure MI and my MSSQL.

I created 'MASTER KEY', 'CERTIFICATE', 'SYMMETRIC KEY' in Azure MI.

And I opened the 'SYMMETRIC KEY' and saved the encrypted data to the table.

---

OPEN SYMMETRIC KEY NI...MK DECRYPTION BY CERTIFICATE NI......ERT;

---

Encrypted data stored in table

I created the same in MSSQL that I have.
MSSQL Version is 2016

And I copied the data encrypted in Azure MI to my MSSQL server as it is.

I decrypted the encrypted data in my MSSQL server.

But data returns 'NULL' value.

I was able to decrypt normally when decryption was executed in Azure MI.
I created 'MASTER KEY', 'CERTIFICATE', and 'SYMMETRIC KEY' identically to Azure MI and my MSSQL Server.

Doesn't 'MASTER KEY', 'CERTIFICATE', 'SYMMETRIC KEY' work the same in Azure MI and plain MSSQL?

I would be grateful if you could help me.
Thanks

Answer 1

Hi,@youngsik Ahn Thanks for posting your question in the Microsoft Q&A forum.

There is an incompatibility between SQL Azure and SQL Server 2016. The CLE0001 TSG should explain it.
The fix is to set an FS in Azure, recreate the symmetric key, and re-encrypt the data in a SQL 2016 compatible format.

Hope this will help. Please let us know if any further queries.

Regards
Geetha