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 named Column2 that uses oneway 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.
Answer: See the explanation.
Explanation:
Box 1:
First create a hash key using the certificate.
Not AES: AES is not based on hashing.
Box 2:Add a column with varbinary data type.
Box 3:Box 4:
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
Reference: SQL Server 2012, Encrypt a Column of Data
All picture links are broke for 70-464
SHA1 is not a valid encryption algorithm. https://msdn.microsoft.com/en-us/library/ms188357.aspx
Try to execute the following in Management Studio (after master key and certificate had been created):
CREATE SYMMETRIC KEY Key1
WITH ALGORITHM = SHA1
ENCRYPTION BY CERTIFICATE Cert1;
GO
and you will get the following error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘SHA1’.
the right awnser is
– CREATE SYMMETRIC KEY Key1 WITH ALGORITHM = AES_256…
– ALTER TABLE Table1 ADD Column2 varbinary(256);
– OPEN SYMMETRIC KEY …
– UPDATE…
CREATE SYMMETRIC KEY Key1 WITH ALGORITHM = SHA1 is NOT the right awnser!
AES is two way encryption and read its
“You need to replace Column1 with a new encrypted column named Column2 that uses ONEWAY hashing”
What about “..oneway hashing” requirement? You can decrypt the encrypted column anytime.
I think the answer pane is incorrect and refers to this question:
http://www.aiotestking.com/microsoft/which-code-segment-should-you-execute-before-you-remove-column1-2/
To encrypt the column one way the update statement should be:
UPDATE table1
SET column2 = EncryptByKey(Key_GUID(‘Key1’)
,HashBytes(‘SHA1’, CONVERT( varbinary, Column2)));
Reference: https://msdn.microsoft.com/en-us/library/ms179331(v=sql.110).aspx
— Create Master Key
Create Master Key Encryption By Password = ‘P@ssw0rd’;
— Create certificate Cert1
Create Certificate Cert1 With Subject = ‘Encrypt Column1 values’;
— Create symmetric based on certificate Cert1
Create Symmetric Key Key1 With Algorithm = AES_256
Encryption By Certificate Cert1;
— Modify table1 to add column2
Alter Table table1 Add Column2 varbinary(256);
— Open symmetric key
Open Symmetric Key Key1 Decryption By Certificate Cert1;
— Update table with encrypted values
Update table1
Set Column2 = EncryptByKey(Key_GUID(‘Key1’),Column1, 1, HashBytes(‘SHA1’, Convert( Varbinary, Column1)));
— Check encrypted and decrypted values
Select
Column1,
Column2 As [Encrypted Value],
Convert(NVarchar, DecryptByKey(Column2, 1 , HashBytes(‘SHA1’, Convert(Varbinary, Column1)))) As [Decrypted Value]
From table1;
Go
Is this correct
Yes is the correct,
I passed my 70-464 exam last year and i prepare this exam by using dumps because study without exam dumps is very difficult. My cousin suggest me Dumps4download.com website where i can preparation 70-464 vce questions.
http://www.dumps4download.com/70-464-dumps.html
?