DRAG DROP
You have a table named Table1 that contains 1 million rows. Table1 contains a column
named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column that uses two-way encryption.
Which code segment should you execute before you remove Column1?
To answer, move the appropriate code segments from the list of code segments to the
answer area and arrange them in the correct order.
Answer: See the explanation
Explanation:
Box 1:Box 2:
Box 3:
Box 4:
Box 5:
Note:
* Use AES_256 for two-way encryption.* Use varbinary to store key.
* CLOSE SYMMETRIC KEY (Transact-SQL)
Closes a symmetric key, or closes all symmetric keys open in the current session.
* Example:
CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09;
GO
— Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
— Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
— Encrypt the value in column CardNumber using the
— symmetric key CreditCards_Key11.
— Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID(‘CreditCards_Key11’)
, CardNumber, 1, HashBytes(‘SHA1’, CONVERT( varbinary
, CreditCardID)));
GO
close symmetric key; //it’s sintaxis error
CLOSE SYMMETRIC KEY Key1;
Create Symmetric Key Key1 With Algorithm = AES_256 Encryption By Certificate Cert1;
Alter Table Table1 Add Column2 varbinary(256);
Open Symmetric Key Key1 Decryption By Certificate Cert1 With Password = ‘Pass@w0rd’;
Update Table1 Set Column2 = EncryptByKey(KEY_GUID(‘Key1’),Column1);
Close Symmetric Key Key1;
Use master;
go
Create Master Key Encryption By Password = ‘Pass@w0rd’
Use Test;
Go
Create Certificate Cert1 With Subject = ‘Encrypt Sensitive Data’;
Go
Create Symmetric Key Key1 With Algorithm = AES_256 Encryption By Certificate Cert1;
Alter Table Table1 Add Column2 varbinary(256);
Open Symmetric Key Key1 Decryption By Certificate Cert1;
Update Table1 Set Column2 = EncryptByKey(KEY_GUID(‘Key1’),Column1);
Close Symmetric Key Key1;