locked
Always Encrypted RRS feed

  • Question

  • Hi,
    As everyone we are aware of the limitations on Always Encrypted concept like below.
    a. Look for unsupported query patterns in ETL and identify alternatives.
    eg:- 1. BULK INSERT INTO SELECT FROM TABLE.
    2. Queries with filters like FirstName LIKE 'Bob%'
    b. Passing above filter parameters into Stored procedures.
    c. Does SSMS work with AzureKeyVault if we choose to use it for key storage or should it be stored locally?
    and how do we register SSMS to access the key so we can write queries as usual.
    d. Impact list for changing OLEDB to ADO.NET connection in our ETLs.

    Any thoughts can helpful...

    Friday, August 14, 2020 7:01 AM

All replies

  • When it comes to the second point, this have been addressed in SQL 2019 where there is a new feature Always Encrypted with Enclaves. An enclave is a proctect area in the CPU, which can receive the encryption keys from the client but still be out of reach.

    Enclave support can be both hardware-based and software-based. I've only tried the latter, and the setup is a little complex.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 14, 2020 9:42 PM
  • Hi Kartheek Vasantha,

    >> Does SSMS work with AzureKeyVault if we choose to use it for key storage or should it be stored locally? and how do we register SSMS to access the key so we can write queries as usual.

    Please refer to Configure Always Encrypted by using Azure Key Vault for more details.

    >> Impact list for changing OLEDB to ADO.NET connection in our ETLs.

    Please refer to the following articles which might help:

    ADO.NET vs. OLE DB vs. ODBC

    What should I choose between ADO.Net and OLE in SSIS?

    Best Regards,

    Amelia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 8:19 AM