June 9, 2021 at 11:07 am
Hello Everyone
I've been testing Always encrypted on 2 test machines. I'm trying to get a feel on how easy they are to work with.
My objective is to encrypt personal info of individual's but still be able to query that info.
On server 1, I created Master Encryption Key, Column Encryption Key & Encryption Certificate.
I then scripted the Master & Column Keys out. Also exported the Certificate from server 1 . Then loaded them all onto server 2.
I tested by encrypting the same value (Deterministic) on both servers and the encrypted values came back identical.
GREAT!!!
Since both values between the servers are the same, i thought i could simply join the tables together using a common join via a linked server object.
Query is.
SELECT
COUNT(0)
FROM
[Encryption_Test].[dbo].dbo.check mmc --(<<-- server1)
JOIN [<<server2>>].[Encryption_Test].[dbo].check mc ON mmc.EncryptedID = mc.EncryptedID
Result.
Msg 402, Level 16, State 2, Line 19
The data types varbinary(1000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = '<<server1>>') and varbinary are incompatible in the equal to operator.
I've tried the same join from server 2 back to server 1. Same issue. Why varbinary? varchar wasnt working either. What i don't understand is the 2 columns being joined are varbinary, yet they incompatible??
Its very likely a lack of understanding on my part.. Some guidance will be highly appreciated.
Thank you.
June 9, 2021 at 4:15 pm
I don't believe the linked server can access the key. Architecturally, this works by the decryption happening in your driver, on your machine, not on the server. The linked server, therefore, can't decrypt the data and request matches from another server.
According to the docs, https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15, this does not work with linked servers or any distributed queries.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply