When setting up encryption for a column, you specify the information about the encryption algorithm and cryptographic keys used to protect the data in the column. Always Encrypted uses two types of keys: column encryption keys and column master keys. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys.
The Database Engine stores encryption configuration for each column in database metadata. Note, however, the Database Engine never stores or uses the keys of either type in plaintext.
It only stores encrypted values of column encryption keys and the information about the location of column master keys, which are stored in external trusted key stores, such as Azure Key Vault, Windows Certificate Store on a client machine, or a hardware security module. To access data stored in an encrypted column in plaintext, an application must use an Always Encrypted enabled client driver.
When an application issues a parameterized query, the driver transparently collaborates with the Database Engine to determine which parameters target encrypted columns and, thus, should be encrypted. For each parameter that needs to be encrypted, the driver obtains the information about the encryption algorithm and the encrypted value of the column encryption key for the column, the parameter targets, as well as the location of its corresponding column master key.
Next, the driver contacts the key store, containing the column master key, in order to decrypt the encrypted column encryption key value and then, it uses the plaintext column encryption key to encrypt the parameter.
The resultant plaintext column encryption key is cached to reduce the number of round trips to the key store on subsequent uses of the same column encryption key. The driver substitutes the plaintext values of the parameters targeting encrypted columns with their encrypted values, and it sends the query to the server for processing.
The server computes the result set, and for any encrypted columns included in the result set, the driver attaches the encryption metadata for the column, including the information about the encryption algorithm and the corresponding keys.
The driver first tries to find the plaintext column encryption key in the local cache, and only makes a round to the column master key if it can't find the key in the cache. Next, the driver decrypts the results and returns plaintext values to the application. A client driver interacts with a key store, containing a column master key, using a column master key store provider, which is a client-side software component that encapsulates a key store containing the column master key.
Providers for common types of key stores are available in client-side driver libraries from Microsoft or as standalone downloads. You can also implement your own provider. Always Encrypted capabilities, including built-in column master key store providers vary by a driver library and its version. For details of how to develop applications using Always Encrypted with particular client drivers, see Develop applications using Always Encrypted.
Encryption and decryption occurs via the client driver. This means that some actions that occur only server-side will not work when using Always Encrypted. These actions include but are not limited to :. The following is an example of an UPDATE that attempts to move data from an encrypted column to an unencrypted column without returning a result set to the client:.
If SSN is a column encrypted using Always Encrypted, the above update statement will fail with an error similar to:. In this scenario, the data will be unencrypted when sent back to the server because the destination column is a regular varchar that does not accept encrypted data. The Database Engine never operates on plaintext data stored in encrypted columns, but it still supports some queries on encrypted data, depending on the encryption type for the column.
Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption. Deterministic encryption always generates the same encrypted value for any given plain text value.
Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. 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 searching, grouping, indexing, and joining on encrypted columns.
Use deterministic encryption for columns that will be used as search or grouping parameters. For example, a government ID number. Use randomized encryption for data such as confidential investigation comments, which aren't grouped with other records and aren't used to join tables.
For details on Always Encrypted cryptographic algorithms, see Always Encrypted cryptography. Please note that some of these tasks are not supported in Transact-SQL and require the use of client-side tools. As Always Encrypted keys and protected sensitive data are never revealed in plaintext to the server, the Database Engine can't be involved in key provisioning and perform data encryption or decryption operations.
It also eliminates the need to move the data outside of the database for cryptographic operations. Make sure you run key provisioning or data encryption tools in a secure environment, on a computer that is different from the computer hosting your database. Otherwise, sensitive data or the keys could leak to the server environment, which would reduce the benefits of the using Always Encrypted. The wizard will provision the required keys and configure encryption for selected columns.
If the columns you're setting encryption for already contain some data, the wizard will encrypt the data. The following example demonstrates the process for encrypting a column.
Queries on columns encrypted by using randomized encryption can't perform operations on any of those columns. Indexing columns encrypted using randomized encryption isn't supported. Security functions , String functions. See also. Decrypt , EncryptBinary , DecryptBinary. For all other algorithms, a key in the format used by the algorithm. For these algorithms, use the GenerateSecretKey function to generate the key.
It includes the following algorithms:. In addition to these algorithms, you can use the algorithms provided in the Standard Edition of ColdFusion. The Standard Edition of ColdFusion installs a cryptography library with the following algorithms:. If you install a security provider with additional cryptography algorithms, you can also specify any of its string encryption and decryption algorithms. Optional; if you specify this parameter, also specify the algorithm parameter.
The binary encoding in which to represent the data as a string. Optional Specify this parameter to adjust ColdFusion encryption to match the details of other encryption software. If you specify this parameter, also specify the algorithm parameter. For Block Encryption algorithms: This is the binary Initialization Vector value to use with the algorithm.
This must be a binary value that is exactly the same size as the algorithm block size. Use the same value in theDecryptfunction to successfully decrypt the data.
For Password Based Encryption algorithms: This is the binary Salt value to transform the password into a key. The same value must be used to decrypt the data. Optional The number of iterations to transform the password into a binary key. Specify this parameter to adjust ColdFusion encryption to match the details of other encryption software.
Do not specify this parameter for Block Encryption algorithms. Use the same value to encrypt and decrypt the data. This function uses a symmetric key-based algorithm, in which the same key is used to encrypt and decrypt a string. The security of the encrypted string depends on maintaining the secrecy of the key. Some of these are not used with the encrypt function, but are used with other functions:. In certain cases, you may want to disable some algorithms.
This provider includes the algorithms listed in the Parameters section.
0コメント