ENCRYPTBYKEY, CERTIFICATES, SYMMETRIC KEYS

Question

Hello, I am having trouble understanding:

• What does the certificate do?
• What is ENCRYPTBYKEY?
• What makes it different from ENCRYPTBYPASSPHRASE?
• What does KEY_GUID mean?
• What does 'cleartext' and @cleartext mean?
• What do Symmetric Keys and Asymmetric Keys do?

Here is a sample codeI got off the internet:

--step 1
USE TESTDB ;  
CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '123Alex!@#$789';  
--DROP MASTER KEY

--step 2
CREATE TABLE TABLE3 
(ID int,
PERSONNAME nvarchar(200),
AGE int,
HOBBY nvarchar(200),
SALARY varbinary(128))

--step 3
CREATE CERTIFICATE MyCertificate1
   WITH SUBJECT = 'Secret info - Salary';  
GO  
--drop CERTIFICATE MyCertificate1

CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE MyCertificate1;  
GO  
--drop SYMMETRIC KEY SSN_Key_01 

--step 4
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE MyCertificate1;  
INSERT INTO TABLE3 VALUES 
(1,'Alex',40,'skateboarding', EncryptByKey(Key_GUID('SSN_Key_01'), '1000')),
(2,'John',32,'wrestling',EncryptByKey(Key_GUID('SSN_Key_01'), '1500')),
(3,'Asti',25,'jogging',EncryptByKey(Key_GUID('SSN_Key_01'), '24560')),
(4,'Jay',53,'football',EncryptByKey(Key_GUID('SSN_Key_01'), '502')),
(5,'Selena',40,'yoga',EncryptByKey(Key_GUID('SSN_Key_01'), '2500'))
--truncate table TABLE3
--DROP TABLE TABLE3

--step 5
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE MyCertificate1;  

SELECT 
*,
CONVERT(varchar, DecryptByKey(SALARY)) AS [Decrypted Salary]
    FROM TABLE3

CLOSE SYMMETRIC KEY SSN_Key_01

Answer 1

Please take a look at the code and Docs articles for the questions that you have which are extremely broad and comes off as a homework assignment. Come back and ask specific questions about the part of items that you need help with and what you currently understand about it so that we can help.

The way it stands, this is just too broad as there are Docs articles about all of these items.

-Sean

---

The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

Answer 2

Hello paoloam.roxas,

As Sean mentioned, your questions are broad and too many. You better to ask one or two specific questions in a thread.

Actually, you can google for your questions, it has a lot related articles. Here I post some linkages to help you understand:

• What does the certificate do?          SQL Server Certificates and Asymmetric Keys
• What is ENCRYPTBYKEY?                 ENCRYPTBYKEY (Transact-SQL)
• What makes it different from ENCRYPTBYPASSPHRASE?    EncryptByKey vs EncryptByPassphrase
• What does KEY_GUID mean?            KEY_GUID (Transact-SQL)
• What does 'cleartext' and @cleartext mean?      plaintext-vs-cleartext
• What do Symmetric Keys and Asymmetric Keys do?    Symmetric vs. Asymmetric Encryption – What are differences?

Hope it will help.

Best Regards

Dawn

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].