Sql-server – SQL Server Always Encrypted – Create an external table containing an encrypted column

always-encryptedazure-sql-databasesql server

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!

Best Answer

First:

Before creating a database scoped credential, the database must have a master key to protect the credential. For more information, see CREATE MASTER KEY (Transact-SQL).

CREATE DATABASE SCOPED CREDENTIAL

So the Database Master Key is used to encrypt the credential.

Second:

I'd like the [AlwaysEncrypted] column to be unencrypted when querying MyTable from Elastic Database Query.

That is not possible. AlwaysEncrypted is intended to have the data decrypted only on the client. Even in the case where SQL Server is the client, there's no way to store the encryption key inside SQL Server and use it in the database engine. There's no fundamental reason why this couldn't be implemented. But the main use case for AlwaysEncrypted is storing data in such a way that SQL Server (and its administrators) cannot access this data.

Related Question