Encrypt or decrypt a column with SSMS

To encrypt or decrypt a column with SSMS:

Encrypt a column

  1. Log in to the client with the <domain>\dbuser account.

  2. Launch Microsoft SQL Server Management Studio.

  3. Connect to the database on the remote SQL server, enabling Always Encrypted, see [generate-mycmk-mycek-ssms].

  4. In the Object Explorer, right-click the TestDatabase database and select Tasks > Encrypt Columns…​.

  5. On the Introduction screen, select Next.

    encrypt ssms introduction

  6. On the Column Selection screen, select the column Name, Encryption Type, and Encryption Key. Then select Next.

    encrypt ssms column selection

  7. On the Master Key Configuration screen, select Next.

    encrypt ssms master key configuration

  8. On the Run Settings screen, select Proceed to finish now. Then select Next.

    encrypt ssms run settings

  9. On the Summary screen, verify the configuration choices. Then select Finish.

    encrypt ssms summary

  10. Present the OCS, select the HSM, and enter the passphrase.

  11. Check that Passed appears in the Details column of the Results screen.

    encrypt ssms results
    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.

  12. Select Close.

View an encrypted column

Reconnect to the SQL server with Enable Always Encrypted disabled to view the encrypted data stored in the SQL server.

  1. Connect to the SQL server but with the Enable Always Encrypted unchecked.

    view ssms reconnect disable ae

  2. 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.

    view ssms password column encrypted

  3. Reconnect to the SQL server, but with the Enable Always Encrypted checked.

  4. Present the OCS, select the HSM, and enter the passphrase.

  5. 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.

    view ssms password column presented as clear text

Remove column encryption

  1. In the Object Explorer, right-click the TestDatabase database, and select Tasks > Encrypt Columns…​.

    decrypt ssms encrypt column

  2. On the Introduction screen, select Next.

  3. On the Column Selection screen, for Encryption Type select Plaintext. Then select Next.

    decrypt ssms plaint text

  4. On the Master Key Configuration screen, select Next.

  5. On the Run Settings screen, select Proceed to finish now. Then select Next.

  6. On the Summary screen, verify the configuration choices. Then select Finish.

  7. Present the OCS, select the HSM, and enter the passphrase.

  8. Check that Passed appears in the Details column of the Results screen.

    decrypt ssms results
    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-encrypted-column].

  9. Select Close.