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.

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

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

  6. On the Introduction screen, select Next.

    encrypt ssms introduction
  7. On the Column Selection screen, select the column(s) to be encrypted, Encryption Type, and Encryption Key. Then select Next.

    For example:

    encrypt ssms column selection
  8. On the Column Assessment screen, select Next.

    encrypt ssms column assessment
  9. On the Master Key Configuration screen, select Next.

    encrypt ssms master key configuration
  10. On the In-Place Encryption Settings screen, select Next.

    encrypt ssms in place encryption settings
  11. On the Run Settings screen, un-check Generate PowerShell script to run later. Then select Next.

    encrypt ssms run settings
  12. On the Summary screen, 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 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.
  15. 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.

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

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

  3. On the Introduction screen, select Next.

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

  5. On the Column Assessment screen, select Next.

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

  7. On the In-Place Encryption Settings screen, select Next.

  8. On the Run Settings screen, un-check Generate PowerShell script to run later. Then select Next.

  9. On the Summary screen, 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 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 an encrypted column.
  12. Select Close.