I'm trying to create an external table MyTable on server S2 that references the table MyTable from server S1. The table MyTable contains a column encrypted using Always Encrypted. I'd like the column to be unencrypted when querying MyTable from S2. Is it something that can be accomplished? I haven't found anything about it in the documentation (if someone knows where to find it, I'd be happy to just read the doc)... I've tried the naive approach bellow but obviously it doesn't work.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myEncryptionPassword';
CREATE DATABASE SCOPED CREDENTIAL MyCred
WITH IDENTITY = 'myAdminAccount',
SECRET = 'mySuperStrongPassword';
CREATE EXTERNAL DATA SOURCE RemoteServerRef
WITH
(
TYPE=RDBMS,
LOCATION='somewhere.on.the.inter.net',
DATABASE_NAME='myDatabase',
CREDENTIAL= MyCred
);
CREATE EXTERNAL TABLE [dbo].[MyTable]
(
[Id] [uniqueidentifier] NOT NULL,
[Field2] [int] NOT NULL,
[Field3] [datetime] NOT NULL,
[Field4] [decimal](18, 2) NULL,
[EncryptedField1] [nvarchar](128) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[EncryptedField2] [int] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
)
WITH
(DATA_SOURCE = RemoteServerRef)
Does anybody know how to do that? As a bonus question, can someone explain to me what the CREATE MASTER KEY ENCRYPTION BY PASSWORD is used for (and does it have anything to do with the first problem)?
Thanks for your help!