locked
always encrypted= Enabled client certificate RRS feed

  • Question

  • Hi all,

    A little bit confused about how to use always encrypted=True to the client PC

    We have a desktop application that used SQL Express 2017. The sql express is hosted in another server somewhere else outside of local lan - in another country. 

    I have follow all the necessary steps to configure always encrypted to one of my columns in the table.

    Now when i try to retrieve data with the option always enrypted=Enabled  from the server (not remotely but locally from the server PC) i can successfully  see the results decrypted. If i set it to Disabled then are encrypted when retrieved 

    PROBLEM

    Now when i release a setup file (.exe) and install the application to the client PC and try run the sample then it fails, obviously because the certificate created by SQL is not installed on that client machine.

    The only way to get this to work is to export the certificate from the server and import it to the client PC manual way.

    I have some questions now.

    1. is this the only way? the manual way?
    2. Each customer using our software has his own database under same instance, same schema all databases.(each customer connects to his own database) That means i need to enter to the client pc one by one and import the certificate manually?  
    3. We are using Setup and Deployment (Visual Studio Installer)  (is there any way to install the certificate automatically?)
    4. Does the certificate expires? ( do i need to keep update every time that expire to update it?) either manually or automatically ?

    stelios ----------


    • Edited by stelios84 Tuesday, June 12, 2018 12:33 PM
    Tuesday, June 12, 2018 12:32 PM

Answers

  • Hi stelios,

    Thanks for your question.

    >>>is this the only way? the manual way?
    As far as I know, you could export and import certificates manually, or you might try to use PowerShell to do that . Please refer to below step by step documents about export and import certificates manually:
    https://support.microsoft.com/en-us/help/823503/how-to-import-and-export-certificates-so-that-you-can-use-s-mime-in-ou

    https://blogs.msdn.microsoft.com/sonam_rastogi_blogs/2014/08/18/request-export-and-import-certificate-using-powershell/

    >>>Each customer using our software has his own database under same instance, same schema all databases.(each customer connects to his own database) That means i need to enter to the client pc one by one and import the certificate manually?
    I agree with you. Since each customer has his own database under the same instance, you might need to create certificate one by one for each customer.

    >>>We are using Setup and Deployment (Visual Studio Installer)  (is there any way to install the certificate automatically?)
    Currently, I do not know any way to install the certificate automatically. As this issue is more related to certificate, you might want to post this question in security forum for better support.

    >>>Does the certificate expires? ( do i need to keep update every time that expire to update it?) either manually or automatically ?
    Yes, it will expires. Thus, you might need to update it every time when that expire. Please see blow screenshot about expiration date:

    Since most of the questions are related to certification, I would highly recommend you post your question to security forum for better service, thanks for your support and understanding.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 13, 2018 8:46 AM

All replies

  • Hi stelios,

    Thanks for your question.

    >>>is this the only way? the manual way?
    As far as I know, you could export and import certificates manually, or you might try to use PowerShell to do that . Please refer to below step by step documents about export and import certificates manually:
    https://support.microsoft.com/en-us/help/823503/how-to-import-and-export-certificates-so-that-you-can-use-s-mime-in-ou

    https://blogs.msdn.microsoft.com/sonam_rastogi_blogs/2014/08/18/request-export-and-import-certificate-using-powershell/

    >>>Each customer using our software has his own database under same instance, same schema all databases.(each customer connects to his own database) That means i need to enter to the client pc one by one and import the certificate manually?
    I agree with you. Since each customer has his own database under the same instance, you might need to create certificate one by one for each customer.

    >>>We are using Setup and Deployment (Visual Studio Installer)  (is there any way to install the certificate automatically?)
    Currently, I do not know any way to install the certificate automatically. As this issue is more related to certificate, you might want to post this question in security forum for better support.

    >>>Does the certificate expires? ( do i need to keep update every time that expire to update it?) either manually or automatically ?
    Yes, it will expires. Thus, you might need to update it every time when that expire. Please see blow screenshot about expiration date:

    Since most of the questions are related to certification, I would highly recommend you post your question to security forum for better service, thanks for your support and understanding.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 13, 2018 8:46 AM
  • Hi Willson

    Thanks for your answer.

    Guess this approach doesn't work to my case as it adds too much extra work for maintenance.

    I will keep this question open for a few days before marked as answer just anyone want to add any suggestion.

    Thanks

    Stelios


    stelios ----------

    Wednesday, June 13, 2018 10:03 AM