Encrypt or decrypt a column with SSMS
Encrypt a column
-
Log in to the client with the <domain>\dbuser account.
-
Launch Microsoft SQL Server Management Studio.
-
Connect to the database on the remote SQL server, enabling Always Encrypted.
-
In the Object Explorer, expand Databases > TestDatabase > Tables > dbo.TestTable.
-
Right-select dbo.TestTable and select Always Encrypted Wizard….
-
On the Introduction screen, select Next.
-
On the Column Selection screen, select the column(s) to be encrypted, Encryption Type, and Encryption Key. Then select Next.
For example:
-
On the Column Assessment screen, select Next.
-
On the Master Key Configuration screen, select Next.
-
On the In-Place Encryption Settings screen, select Next.
-
On the Run Settings screen, un-check Generate PowerShell script to run later. Then select Next.
-
On the Summary screen, verify the configuration choices. Then select Finish.
-
Present the OCS, select the HSM, and enter the passphrase.
-
Check that Passed appears in the Details column of the Results screen.
The column is encrypted in the SQL server, but it shows as clear text on the Microsoft SQL Server Management Studio GUI on the client. This is because Always Encrypted is performing the decryption at the client site. -
Select Close.
View an encrypted column
Connect to the SQL server with Enable Always Encrypted disabled to view the encrypted data stored in the SQL server.
-
Connect to the SQL server but with the Enable Always Encrypted unchecked.
-
Right-click dbo.Table and select Select Top 1000 Rows. The column that was chosen for encryption now appears as ciphertext, that is, as an encrypted value.
-
Reconnect to the SQL server, but with the Enable Always Encrypted checked.
-
Present the OCS, select the HSM, and enter the passphrase.
-
Right-click dbo.Table and select Select Top 1000 Rows. The column that was chosen for encryption is now being decrypted by Always Encrypted with the key protected by the nShield HSM.
Remove column encryption
-
In the Object Explorer, expand Databases > TestDatabase > Tables > dbo.TestTable.
-
Right-select dbo.TestTable and select Always Encrypted Wizard….
-
On the Introduction screen, select Next.
-
On the Column Selection screen, for Encryption Type select Plaintext. Then select Next.
-
On the Column Assessment screen, select Next.
-
On the Master Key Configuration screen, select Next.
-
On the In-Place Encryption Settings screen, select Next.
-
On the Run Settings screen, un-check Generate PowerShell script to run later. Then select Next.
-
On the Summary screen, verify the configuration choices. Then select Finish.
-
Present the OCS, select the HSM, and enter the passphrase.
-
Check that Passed appears in the Details column of the Results screen.
The column has been decrypted in the SQL server. To view the plain text data stored SQL server, reconnect to the server with Always Encrypted disabled, see View an encrypted column. -
Select Close.