question

FrederickSamson-7857 avatar image
0 Votes"
FrederickSamson-7857 asked PRADEEPCHEEKATLA-MSFT commented

DecryptByKeyAutoAsymKey and/or DecryptByKeyAutoCert functions for Azure Synapse SQL Dedicated Pool

Hi,

Any chance that DecryptByKeyAutoAsymKey and/or DecryptByKeyAutoCert functions get added to Azure Synaspe SQL Dedicated Pool anytime soon?

It would unlock the ability to use Column-level encryption from Power BI in Direct Query mode.

With DecryptByKey function, one has to call OPEN SYMETRIC KEY prior to use DECRYPTBYKEY and it cannot be done from a View or from a Table-valued function.

It can be called in a stored procedure, but Power BI Direct Query mode doesn't support them.

Best solution would be to add DecryptByKeyAutoAsymKey and/or DecryptByKeyAutoCert to Azure Synapse. If a user doesn't have access to the key, it doesn't fail, it simply returns null which is what we want (tested in SQL Server 2019).

Thanks!

Frederick

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @FrederickSamson-7857,

Thanks for the question and using MS Q&A platform.

As I already said on your previous thread: DECRYPTBYKEYAUTOASYMKEY in Azure Synapse SQL Dedicated Pool.

Unfortunately, DECRYPTBYKEYAUTOASYMKEY is not supported in Azure Synapse SQL Dedicated Pool.

To decrypt a column in a view (or otherwise without opening the symmetric key first) you must use DecryptByKeyAutoCert , DecryptByKeyAutoAsymKey, or DecryptByPassphrase none of which is available in Synapse SQL Pool and it only supported in SQL Server.

198174-image.png

Appreciate if you could share the feedback on our Azure Synapse feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Hope this will help. Please let us know if any further queries.

  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


image.png (16.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FrederickSamson-7857 avatar image FrederickSamson-7857 ·

Hi @PRADEEPCHEEKATLA-MSFT

Apologies, it seems I didn't see your previous answer. Sorry for the repeat.

I just shared my idea, as per your suggestion:
https://feedback.azure.com/d365community/idea/fcaa5cce-0cca-ec11-a81b-0022484ee92d

Thanks,

Frederick

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image PRADEEPCHEEKATLA-MSFT FrederickSamson-7857 ·

Hello @FrederickSamson-7857,

Thanks for sharing the feedback.

1 Vote 1 ·