For this Critical Thinking Assignment, you will complete the hands-on activity,
ID: 3836830 • Letter: F
Question
For this Critical Thinking Assignment, you will complete the hands-on activity, Project 6-2: Encrypting and Decrypting Data,based on p. 236 of the MCSA guide to Microsoft® SQL server 2012textbook.
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.
In SQL Server Management Studio, open a new Query Editor window, which you will use for completing all steps in this activity.
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
Explanation / Answer
The SQL query to execute the above tasks are as follows,
1. Opening Query Editor Window
· Open the SQL Server Studio.
· Now, click on File menu .
· Click Open and then got to the File and Open It.
· The Studio will automatically open the type of editor.
· Now type the command, To create master key the password- 'passwd' is used to encrypt the key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passwd'; GO
To generate the self signed certificate the following command is used,
CREATE CERTIFICATE HandsOnOne
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'HandsOnOne Records',
EXPIRY_DATE = '15201510';
GO
2. Create the symmetric key for data encryption. Use following command as cerificate- 'HandsOnOne',
CREATE SYMMETRIC KEY HandsOnOne_symmetrickey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HandsOnOne;
GO
3. Add a new column CustomerNameEncrypted into the existing table, Use the following command:
ALTER TABLE Customer
ADD CustomerNameEncrypted varbinary(128) NULL;
GO
5.Encrypt the above given column and also assign a value, use the following command:
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY HandsOnOne_symmetrickey
DECRYPTION BY CERTIFICATE HandsOnOne;
GO
UPDATE Customer
SET CustomerNameEncrypted = EncryptByKey (Key_GUID('HandsOnOne_symmetrickey'),CustomerName)
FROM dbo.Customer;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY HandsOnOne_symmetrickey;
GO
5.Query to view the encrypted values of the table:
select customerNameEncrypted from customer;
GO
6.To decrypt the values the following commands are used,
OPEN SYMMETRIC KEY HandsOnOne_symmetrickey
CERTIFICATE HAndsOnOne;
GO
-- Now list the encrypted ID
SELECT CustomerNameEncrypted AS 'Encrypted Customer Name',
CONVERT(varchar, DecryptByKey(CustomerNameEncrpted)) AS 'Decrypted Customer Name'
FROM dbo.Customer;
-- Close the symmetric key
CLOSE SYMMETRIC KEY HandOnOne_symmetrickey;
GO