locked
ENCRYPTBYKEY, CERTIFICATES, SYMMETRIC KEYS RRS feed

  • 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


    Friday, April 24, 2020 10:33 AM

Answers

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:

    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.


    Monday, April 27, 2020 5:57 AM