SQL Server Always Encrypted Large Object Column

[_Tom]

Hi, I've got an issue using Alteryx to read from a SQL Server table with an Always Encrypted (SQLAE) blob column. I searched the discussions and knowledge and couldn't find what I needed.

I'm investigating replacing a SyncSort ETL job with one that uses Alteryx, due to Linux/Windows issues regarding SQLAE. The machine I'm running on has the appropriate certificate installed for acccessing the data (I can verify by querying in SSMS), but when I run the workflow in Alteryx Designer, I see the error shown below.

It looks to me like Alteryx is looking for the cert in some sort of default location (the message doesn't tell me exactly where). If I can override this to point to the cert install location that is used by SQL Server...  

Note that I've tried various ODBC connections (Driver 13, driver 17, native client 11.0) and all either fail as below or give a result that basically says "ignoring blob data".

Here is a screen shot of the simple workflow showing the error:

The relevant portions of the ODBC connex:

Here's the table DDL:

CREATE TABLE [DBMaint].[AuditRawData_SwOut](
[RowId] [bigint] NOT NULL,
[RawData] [varchar](max) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_CssAudit_CssAuditDW], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[VersionID] [int] NOT NULL,
[Event_DOW] [tinyint] NOT NULL,
CONSTRAINT [PK_AuditRawData_SwOut] PRIMARY KEY CLUSTERED
(
[RowId] ASC,
[Event_DOW] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data1]
) ON [Data1] TEXTIMAGE_ON [Data1]

[_Tom · Accepted solution]

All, it turns out that our company has a policy in place that does not allow client Designer apps to access SQLAE data. I just found out about that, so this discussion can be closed as not required.

Thanks,

Tom