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 named Column2 that uses one-way hashing.
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.
Select and Place:
Answer: 
Explanation:
Note:
* There are a few different hashing algorithms available in SQL Server 2005: MD2, MD4, MD5, SHA, SHA1,
with each having pros and cons.
* In cryptography, SHA-1 is a cryptographic hash function designed by the United States National Security
Agencyand published by the United StatesNISTas a USFederal Information Processing Standard.SHA stands
for “secure hash algorithm”.The four SHAalgorithmsare structured differently and are distinguished asSHA-
0,SHA-1,SHA-2, andSHA-3.SHA-1 is very similar to SHA-0, but corrects an error in the original SHA hash
specification that led to significant weaknesses.The SHA-0 algorithm was not adopted by many
applications.SHA-2 on the other hand significantly differs from the SHA-1 hash function. SHA-1 is the most
widely used of the existing SHA hash functions, and is employed in several widely used applications and
protocols.
* To encrypt a column of data using a simple symmetric encryption In Object Explorer, connect to an instance
of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2012;
–If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY
ENCRYPTION BY
PASSWORD = ‘23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj’ GO
CREATE CERTIFICATE Sales09
WITH SUBJECT = ‘Customer Credit Card Numbers’;GO
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_Encryptedvarbinary(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
SQL Server 2012, Encrypt a Column of Data
Ref: http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example- using-symmetrickeys/