Always Encrypted Wizard

APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Use the Always Encrypted Wizard to help protect sensitive data stored in a SQL Server database. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server. As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access). For a full description of the feature, see Always Encrypted (Database Engine).

To Open the Always Encrypted Wizard

  1. Connect to your SQL Server with the Object Explorer component of SQL Server Management Studio.

  2. Right-click your database, point to Tasks, and then click Encrypt Columns.

Column Selection Page

  • Locate a table and column, and then select an encryption type (deterministic or randomized) and an encryption key for selected columns. To decrypt an column that is currently encrypted, select Plaintext. To rotate a column encryption key, select different encryption key and the wizard will decrypt the column and re-encrypt the column with the new key. (Encrypting temporal and In-Memory tables is supported by SQL Server but cannot be configured by this wizard.)

Master Key Configuration Page

  • Create a new column master key in the Windows Certificate Store or Azure Key Vault. For more information, see the links below under Key Storage.

  • If you chose an auto-generated column encryption key in the Column Selection page, you must configure a column master key that the generated column encryption key will be encrypted with. If you already have a column master key defined in your database, you can select it. (To use an existing column master key, the user must have permission to access the key.) Or, you can generate a column master key in a selected key store (Windows Certificate Store or Azure Key Vault) and define the key in the database.

Key Storage

Always Encrypted Terms

  • Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

  • Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.

  • Column master keys are protecting keys used to encrypt column encryption keys. Column master keys must be stored in a trusted key store. Information about column master keys, including their location, is stored in the database in system catalog views.

  • Column encryption keys are used to encrypt sensitive data stored in database columns. All values in a column can be encrypted using a single column encryption key. Encrypted values of column encryption keys are stored in the database in system catalog views. You should store column encryption keys in a secure/trusted location for backup.

See Also