Answered by:
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
- Changed type Olaf HelperMVP, Editor Friday, April 24, 2020 10:38 AM More a question then a discussion
Friday, April 24, 2020 10:33 AM
Answers
-
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 MSDNFSF@microsoft.com.- Edited by Dawn YoungMicrosoft contingent staff Monday, April 27, 2020 6:02 AM
- Proposed as answer by Dawn YoungMicrosoft contingent staff Tuesday, April 28, 2020 1:17 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Thursday, May 7, 2020 6:21 AM
Monday, April 27, 2020 5:57 AM
All replies
-
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.
Friday, April 24, 2020 1:25 PM -
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 MSDNFSF@microsoft.com.- Edited by Dawn YoungMicrosoft contingent staff Monday, April 27, 2020 6:02 AM
- Proposed as answer by Dawn YoungMicrosoft contingent staff Tuesday, April 28, 2020 1:17 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Thursday, May 7, 2020 6:21 AM
Monday, April 27, 2020 5:57 AM