Post reply

Column Encryption/Exporting Windows Certificate

  • February 26, 2025 at 2:42 pm

    #4546549

    So random question about Windows Certificates --

    I administer and develop a SQL Server DB App -- Back End is on an Azure Server/Front End is MS Access, about 35 End Users.

    We employ column encryption on a handful of fields, and re encrypt every 3 months or so.  When this encryption is done by me, the certificate is created and I am able to export said certificate to distribute to the end users. However, when one of our other developers tries to do the same thing, he, for some reason, is not able to export the private key on this certificate.

    Our systems folks assure me that our hardware/permission setups are identical -- I am wondering if there is some kind of setting on SSMS that I am missing that would cause this difference?

    Thanks in advance for your help and input.

  • February 27, 2025 at 3:10 pm

    #4547321

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • March 4, 2025 at 12:24 pm

    #4550902

    EsquireDeveloper wrote:

    So random question about Windows Certificates --

    I administer and develop a SQL Server DB App -- Back End is on an Azure Server/Front End is MS Access, about 35 End Users.

    We employ column encryption on a handful of fields, and re encrypt every 3 months or so.  When this encryption is done by me, the certificate is created and I am able to export said certificate to distribute to the end users. However, when one of our other developers tries to do the same thing, he, for some reason, is not able to export the private key on this certificate.

    Our systems folks assure me that our hardware/permission setups are identical -- I am wondering if there is some kind of setting on SSMS that I am missing that would cause this difference?

    Thanks in advance for your help and input.

    can you supply a little more context please.

    How you're creating the cert and where?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 4, 2025 at 2:05 pm

    #4550987

    Hi thanks for your reply --

    We are creating the certificate using SSMS's GUI.  I right click on the DB, Select Tasks>Encrypt Columns.

    After selecting the columns and type of encryption, I am given the option of a Windows Certificate or an Azure Certificate.  We create a windows certificate.  This certificate appears in the Microsoft Management Console as a Personal Certificate (to see it I type 'mmc' in a windows search bar, and navigate to see my personal certificates).

    I right-click on that newly created certificate, select Tasks>Export and am able to Export the private key -- which creates a certificate that can be installed on other users' machines.

    We are finding that I am able to do this, but a colleague of mine with the exact same set up and permissions is not -- and I'm wondering if there is a setting in SSMS that I am missing.

    Thanks again.

  • March 4, 2025 at 2:56 pm

    #4550993

    ok, using AlwaysEncrypted.

    Firstly do you understand exactly how this feature works and the degrees of separation that can be used, see my blog post at this link

    Is your colleague encrypting columns himself and then trying to export that cert?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 4, 2025 at 3:29 pm

    #4551024

    There isn't a setting in SSMS I'm aware of.

    Is this what you and your colleague are doing: https://www.mssqltips.com/sqlservertip/4814/exporting-and-importing-sql-server-always-encrypted-certificates-for-client-access/

    As Perry noted, make sure you and your colleague understand how this works well. A mistake here can make for a really bad day

  • March 4, 2025 at 3:40 pm

    #4551047

    Steve Jones - SSC Editor wrote:

    There isn't a setting in SSMS I'm aware of.

    No, its pretty much all at the external level to sql server

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 4, 2025 at 4:16 pm

    #4551050

    Thanks so much for all your replies -- I'll be sure to look through those links and to ensure we are doing everything properly.

  • March 12, 2025 at 7:14 am

    #4557679

    Thank you so much for sharing the info.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply