Post reply

SQL 2016 Always Encrypted feature

  • February 23, 2018 at 2:27 am

    #327583

    Hi All,

    Can we setup the always encrypted feature using tsql?

    What I mean is, if i want to enable encryption on 2 or more columns of a table, then I had to go through the ssms wizard and finally it was giving option to generate a powershell script and not a tsql script. to get the options I had to right click the table -> Script table as ->Create table..

    But my requirement is that, suppose i have done the POC on a dev box, now I want to enable this feature on PROD database using ALTER table scripts , how to do that?  Can we configure encryption using ALTER table statements ? Is that possible?

    Thanks,

    Sam

  • February 24, 2018 at 11:17 am

    #1981038

    Anyone?

  • February 25, 2018 at 1:28 pm

    #1981052

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

    Something like this:

    ALTER TABLE Customers ADD 
        PromotionCode nvarchar(100)  
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, 
        ENCRYPTION_TYPE = RANDOMIZED, 
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256');

  • February 28, 2018 at 6:12 am

    #1981437

    vsamantha35 - Friday, February 23, 2018 2:27 AM

    Hi All,

    Can we setup the always encrypted feature using tsql?

    What I mean is, if i want to enable encryption on 2 or more columns of a table, then I had to go through the ssms wizard and finally it was giving option to generate a powershell script and not a tsql script. to get the options I had to right click the table -> Script table as ->Create table..

    But my requirement is that, suppose i have done the POC on a dev box, now I want to enable this feature on PROD database using ALTER table scripts , how to do that?  Can we configure encryption using ALTER table statements ? Is that possible?

    Thanks,

    Sam

    Do you intend to implement and manage this solely via one admin team or do you plan to use role separation?
    Do you understand the steps required to implement the encryption and the limitations when using the wizard\sql instance?

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

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

  • March 1, 2018 at 11:05 am

    #1981685

    Hi Perry,

    Gone through the documentation. Found we need to have latest ssms installed.
    As per my reading only onption is through ssms wizard and powershell and not tsql. Pl correct me if am wrong.
    I didnt  get question 1. As per my understand its only 1 team which is going to create and manage keys? What is role separation in your words? do you mean separate teams to manager keys and creation of keys?

    Thanks,
    Sam

  • March 5, 2018 at 8:28 am

    #1981958

    vsamantha35 - Thursday, March 1, 2018 11:05 AM

    What is role separation in your words? do you mean separate teams to manager keys and creation of keys?

    Thanks,
    Sam

    yes, exactly that

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

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

Viewing 6 posts - 1 through 5 (of 5 total)

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