Distribute certificates to group of users for decrypting Always Encrypted columns

Auntiejack 201 Reputation points
2021-01-14T06:59:46.413+00:00

Hi,

The problem: How to auto distribute a certificate enabling decryption of Always Encrypted SQL Server columns to a specific AD group of users within a domain.

From previous assistance here, it seems:
1- GPO can't push a certificate to a users Personal Folder in the Certificate Store.

2 - Install Enterprise CA? This seems to only create and distribute new certificates on request, not distribute existing certificates.

3 - Using GPO to loading certificates to Local Computer? This would require a formidable manual process of running MMC and adding read permission for each user account in the MMC Security dialog box on each computer ewww.

4 - Workaround? Run a script to copy and load certificates from a shared folder on login. That will require additional testing, debugging etc and adds at least one other potential point of failure.

5 - Push Certificates to Trusted Users via GPO? As far as I can tell, this doesn't mean that only Trusted Users receive them - everybody receives them. So it even if you could repoint the SQL Server MK path to 'Trusted Users', it wouldn't restrict the decryption certificate to the AD group.

People must surely be pushing these certificates out to users in the real world - how on earth is it done?

Thanks!

Jack

Windows
Windows
A family of Microsoft operating systems that run across personal computers, tablets, laptops, phones, internet of things devices, self-contained mixed reality headsets, large collaboration screens, and other devices.
4,857 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,911 questions
{count} vote

1 answer

Sort by: Most helpful
  1. Daisy Zhou 19,276 Reputation points Microsoft Vendor
    2021-01-15T02:20:30.98+00:00

    Hello @Auntiejack ,

    Thank you for posting here.

    Based on the description, I understand you have existing certificates, and we want to auto distribute a certificate to user group.

    Based on my knowledge, we can add or publish certificate to user Properties manually as below.
    56884-st1.png

    Based on my resear and search, it seems there is no way to publish certificates to user group manually or automatically.
    We can not see which attribute to store certifcaite for user group.

    56965-st2.png

    Thank you for your understanding.

    Best Regards,
    Daisy Zhou

    0 comments No comments