My question is what are the command or codes for these steps? For this hands-on

Question

My question is what are the command or codes for these steps?

For this hands-on project, you will use the SQL Server named instance SQLSERVERHOA and the HandsOnOne database and tables that 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 Management Studio, open a new 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. Create a SQL query to generate a new database master key and certificate for the HandsOnOne database. 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 that you created in Step 2. Construct 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. Using the symmetric key that you created in Step 2, write a SQL UPDATE query that encrypts the values in the CustomerName column and adds the encrypted values to the CustomerNameEncrypted column. Construct a SQL SELECT query to view the encrypted values of the CustomerNameEncrypted column in the Customer table. Construct a SELECT SQL query that uses the symmetric key to decrypt the values in the CustomerNameEncrypted column. Close SQL Server Management Studio.

Explanation / Answer

1. Opening Query Editor Window

After Opening the SQL Server Studio you will see the File Menu
Click Open and then got to the File and Open It.

The Studio will automatically open the type of editor for the file extension, copies the contents of the file
and also gives you access o the connection dialog if required.

2. Master Key

USE dbname;

GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Enter new key here';

GO

3.Symmetric Key Generation

The symmetric key generation follows the below given process

CREATE SYMMETRIC KEY key_name   
[ AUTHORIZATION owner_name ] //your name over here
[ FROM PROVIDER provider_name ] // provider name here
WITH
[
<key_options> [ , ... n ] // Options from below in key_options
| ENCRYPTION BY <encrypting_mechanism> [ , ... n ]
]

  
There are multiple key options for you to choose from one of the given below.

<key_options> ::=
KEY_SOURCE = 'pass_phrase' //best use
ALGORITHM = <algorithm>
IDENTITY_VALUE = 'identity_phrase'
PROVIDER_KEY_NAME = 'key_name_in_provider'   
CREATION_DISPOSITION = {CREATE_NEW | OPEN_EXISTING }

  
  
   There are multiple algorithms in which the encrption of data is provided . Some of them are listed below
  
<algorithm> ::=
   DES
   |TRIPLE_DES
   | TRIPLE_DES_3KEY
   | RC2
   | RC4
   | RC4_128
| DESX
   | AES_128
   | AES_192
   | AES_256   

4. & 5. & 7.

USE table_name;
GO

// creating column in which encrypted data will be entered
ALTER TABLE col_name
ADD col_name varbinary(128);   
GO

// Opening the Symmetric Key

OPEN SYMMETRIC KEY key_name
DECRYPTION BY CERTIFICATE certificate_name;

// Saving the result in column.
UPDATE table_name
SET col_name= EncryptByKey(Key_GUID('key)_name'), NationalIDNumber);
GO

//Verify the Encryption

OPEN SYMMETRIC KEY Name_OF_Symmetry_key_used
DECRYPTION BY CERTIFICATE certificate_name;
GO

// Verifying Decryption
  
SELECT original_id, encrypt_id   
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(key_value))   
AS 'Decrypted ID Number'
FROM table_name;
GO

6. View the Encrypted Values

8. Close SQL SERVER MANAGEMENT STUDIO

sqlcmd -Q

Hope it helps.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.