Create a SQL query to generate a new database master key and certificate for the HandsOnOne database. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
Construct a SQL query to generate a new symmetric key for encrypting data. The symmetric key should use the AES algorithm with a 256-bit key size, and it should be protected by the certificate you created in Step 2. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
Construct a SQL query to alter the Customer table and add a new column named CustomerNameEncrypted with data type varbinary(128). This column will be used to store the encrypted values of the CustomerName column. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
Using the symmetric key you created in Step 2, write an SQL UPDATE query that encrypts the values in the CustomerName column and adds the encrypted values to the CustomerNameEncrypted column. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
Construct a SQL SELECT query to view the encrypted values of the CustomerNameEncrypted column in the Customer table. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
Construct a SELECT SQL query that uses the symmetric key to decrypt the values in the CustomerNameEncrypted column. Note that you will need to convert the hexidecimal values into a character string in order to read the decrypted values. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
The database master key protects the certificate and asymmetric private keys that exist in the database. When the database master key is created, it is encrypted by using the AES_256 algorithm and a password created by the user. To allow the automatic decryption of the master key, a copy of the master key is encrypted using the service master key and kept in both the both the master and database.
The next phase is to create a certificate that is secured or protected by the database master key. A certificate is a numerically signed security entity that contains a public, and optionally, a private key for SQL Server. An elective when creating a certificate is encryption by password argument. The argument describes a password protection scheme of the certificate's private key. If you create the certificate without including this argument it means we are specifying that the certificate is to be protected by the database master key.
Symmetric-key algorithms are algorithms for cryptography that implement the equivalent cryptographic keys for both encryption of plaintext and decryption of ciphertext. The symmetric keys may be identical or there may be a simple transformation to go between the two keys. To create Symmetric-key you have to provide a name for the key. After naming, the symmetric key must be encrypted by using at least a certificate, symmetric key or a password. The symmetric key can have one or more encryption of each type. Simply, a distinct symmetric key can be encrypted by using multiple passwords, certificates, asymmetric keys and symmetric keys at the same time (Goyal, Pande, Sahai, & Waters, 2006). Before encrypting and decrypting of column data you must be granted control permission to the database and also create permissions on the database
Reference
Goyal, V., Pandey, O., Sahai, A., & Waters, B. (2006). Attribute-based encryption for fine-grained access control of encrypted data. In Proceedings of the 13th ACM conference on Computer and communications security (pp. 89-98). Acm.
