Question

For this hands-on project, you will use the SQL Server named instance SQLSERVERHOA, and the HandsOnOne database and tables you created in previous chapters. The objective of this activity is to practice generating keys and encrypting/decrypting data. Document each step by taking a screen shot of the Query Editor window after successfully executing each SQL query. 1.In SQL Server Management Studio, open a new Query Editor window, which you will use for completing all steps in this activity. 2.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. 3.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. 4.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. 5.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. 6.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. 7.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.

(Just want screen shots of each task)

Answer 1

First start your SQL server named SQLSERVERHOA which should be pretty easy as the problem states you already created this server and also created the database and table in previous projects.

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password.Here password is a certificate.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

creates a master key with password as 'password'

Here I have created a certificate Shipping04 protected by a password. You can change the name of certificate and other details as per your requirements.

USE AdventureWorks2012; CREATE CERTIFICATE Shipping04 ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' WITH SUBJECT = 'Sammamish Shipping Records', EXPIRY_DATE = '20201031'; GO

certificate_name
Is the name by which the certificate will be known in the database.

AUTHORIZATION user_name
Is the name of the user that will own this certificate.

ASSEMBLY assembly_name
Specifies a signed assembly that has already been loaded into the database.

[ EXECUTABLE ] FILE ='path_to_file'
Specifies the complete path, including file name, to a DER-encoded file that contains the certificate. If the EXECUTABLE option is used, the file is a DLL that has been signed by the certificate. path_to_file can be a local path or a UNC path to a network location. The file will be accessed in the security context of the SQL Server service account. This account must have the required file-system permissions.

WITH PRIVATE KEY
Specifies that the private key of the certificate is loaded into SQL Server. This clause is only valid when the certificate is being created from a file. To load the private key of an assembly, use ALTER CERTIFICATE.

ALTER TABLE table_name
DROP COLUMN column_name; adds a column with name column_name to your table table_name.INSERT CORRECT TABLE AND COLUMN NAMES IN THESE PLACES.

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;