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 instance using a Connection String in which Column Encryption Setting=Enabled (the default setting).

  4. In the Object Explorer, expand Databases > TestDatabase > Tables > dbo.TestTable.

  5. Right-select dbo.TestTable and select Always Encrypted Wizard…​.

  6. In the Introduction window, select Next.

    encrypt ssms introduction

  7. In the Column Selection window, select one or more columns to encrypt, select the Encryption Type, and Encryption Key, and then select Next.

    For example:

    encrypt ssms column selection

  8. In the Column Assessment window, select Next.

    encrypt ssms column assessment

  9. In the Master Key Configuration window, select Next.

    encrypt ssms master key configuration

  10. In the In-Place Encryption Settings window, select Next.

    encrypt ssms in place encryption settings

  11. In the Run Settings window, select Next.

    encrypt ssms run settings

  12. In the Summary window, verify the configuration choices, then select Finish.

    encrypt ssms summary

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

  14. Check that Passed appears in the Details column of the Results window.

    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.

  15. Select Close.

View an encrypted column

  1. Connect to the SQL server instance with Column Encryption Setting=Disabled to view the encrypted data stored on the server.

    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 instance with Column Encryption Setting=Enabled.

  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, expand Databases > TestDatabase > Tables > dbo.TestTable.

  2. Right-select dbo.TestTable and select Always Encrypted Wizard…​.

  3. In the Introduction window, select Next.

  4. In the Column Selection window, for Encryption Type select Plaintext, then select Next.

  5. In the Column Assessment window, select Next.

  6. In the Master Key Configuration window, select Next.

  7. In the In-Place Encryption Settings window, select Next.

  8. In the Run Settings window, select Next.

  9. In the Summary window, verify the configuration choices, then select Finish.

    decrypt 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 window.

    decrypt ssms results
    The column has been decrypted in the SQL server. To view the plaintext data stored on the SQL Server instance, reconnect to the server with Column Encryption Setting=Disabled. For instructions, see View an encrypted column.

  12. Select Close.