Requirement: SQL server 2016 or newer
SQL Server always encrypted is a way to encrypt the table column(s) to restrict the unauthenticated access of sensitive/secured/special data.
In order to encrypt any column using always encrypt, A database must have a column master key and a column encryption key.
Column Master key: At least one column master key is required before creating column encryption key as it protects the encryption key.
Column Encryption Key: This key is required to encrypt the column
Let’s create these keys first
Create Column Master Key
To create Column Master key, Connect the database and navigate to Database->Security->Always Encrypted Keys->Column Master key
Right click on Column Master key and select new column master key. It will open below window
Now provide a name. For key store, you may either choose current user or local machine. I am using local machine but if you are using current user then you have to click on generate certificate to generate self-signed certificate. click ok
Column master key is created.
Create Column Encryption Key
To create column Encryption Key navigate to Database->Security->Always Encrypted Keys->Column Encryption key then right click on Column Encryption key and select new column encryption key. It will open below window
Provide a name and select column master key from drop down list.
Column Encryption Key is created now.
Our database is ready to encrypt the sensitive column(s) after creating both keys. To encrypt the column, navigate to database table, Expand the table and then Expand the columns.
Now, Right click on column name and select encrypt column. It will open below window
Check the column, select the encryption type from Deterministic or Randomized and select the encryption key from drop down list and click next. On next page click next again
Difference between Deterministic or Randomized is documented in Microsoft docs here
In run setting, you may generate PowerShell script to run later or proceed to finish. Select proceed to finish and click next then finish. The column will be encrypted after successful completion.
Let’s check the column value in the table.
As you can see, the phone numbers are encrypted. To see the un-encrypted phone number, you have to include below string in connection string or connect the database server again with additional connection parameter.
Column Encryption Setting = enabled;
Let me reconnect the database server with additional connection parameter.
Run the query again and it will open a window to enable the always parameterization for T-SQL.
Click on Enable and you can see the actual phone numbers in result now.
YouTube : Sql depository