OPEN SYMMETRIC KEY in Power BI to Decrypt Data in Synapse

[JeffMoltenberry]

I have data in synapse that is encrypted that I want to decrypt and make available in Power BI.  The logic below works in SSMS and it works in Power BI data preview. But when I load / apply changes it fails with "Incorrect syntax near 'OPEN'"

OPEN SYMMETRIC KEY {KEYNAME}
DECRYPTION BY CERTIFICATE {CERTNAME};

SELECT {COLNAME}
, CONVERT(VARCHAR(30),DecryptByKey({COLNAME})) AS Name
FROM [TABLE];

CLOSE SYMMETRIC KEY {KEYNAME}

   

[spindlej]

I know this post is fairly old, but the solution isn't really clear.  It says Power BI doesn't support "Always Encrypted" yet the OP isn't using AE.  

I'm in the same scenario and have found the only way to have this work is to use Import versus DirectQuery under the Data Connectivity mode.  Seems strange that it works one way, but not the other.  You can even see a preview of the data if you use DirectQuery, but as soon as you try to load, it throws the error.  To me it seems rather inconsistent.  

[JeffMoltenberry · Accepted solution]

Via support ticket. 

Power query doesn't support "Always encrypted column"
Ref: https://docs.microsoft.com/en-us/power-query/connectors/azuresqldatawarehouse#troubleshooting
https://docs.microsoft.com/en-us/power-query/connectors/azuresqldatawarehouse