Post reply

EncryptByKey and DecryptByKey Newbie???

  • October 30, 2006 at 12:28 pm

    #170858

    This is my first attempt encrypting/decrypting data in a SQL 2005 DB.

    I found some very useful info here:

    http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp

    but, I'm unsure of my results.

    Here's the code so far that I'm testing in SQL Querry Analyzer:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd'

    -- Create a Certificate

    CREATE CERTIFICATE MyCert WITH SUBJECT = 'My DB Access', EXPIRY_DATE = '10/31/2010'

    -- Create a Symmetric Key

    CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert

    OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert

    --Adding Data

    OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert

    INSERT INTO table1 (col1) VALUES (EncryptByKey(Key_GUID('MySymKey'), 'ThisAndThat.com'))

    Result: unreadable encryption code

    --Reading Data

    OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert

    SELECT DecryptByKey(col1) FROM table1

    Result: 0x54686973416E64546861742E636F6D

    Q. Why aren't my results; "ThisAndThat.com" ????

  • October 30, 2006 at 1:19 pm

    #668838

    Try changing your read data script to this:

    OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert

    SELECT CONVERT(VARCHAR, DecryptByKey(col1)) FROM table1

    Let me know if that works...

  • October 30, 2006 at 4:24 pm

    #668884

    That worked!

    Q. Because it converted binary to varchar?

  • October 30, 2006 at 5:21 pm

    #668895

    Well the function info in BOL says it returns a varbinary(8000). You need to return it to a usable character string in order to make sense of it. Varbinary is good for storing large objects, but not character data. For instance

    SELECT CONVERT(VARBINARY, 'This makes sense to humans')

    Returns a binary that we can't read (well, not sensibly). So going the other way...

  • October 30, 2006 at 8:31 pm

    #668918

    Q. Implementing within Application?

    Next hurdle will be including the DB Encrypt / Decrypt within a workin Windows APP and Windows Service.

    Q. How long does the database remember the "Create Master Key", Create Certificate, Create SymmetricKey, etc... ?

    Another words, If I use the following code to first Encrypt the data (done in a Service), then do I repeat the code on the seperate Windows App to Decrypt the data for viewing?

    [Encrypt in Service]

    CREATE

    MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd'

    CREATE CERTIFICATE MyCert WITH SUBJECT = 'My DB Access', EXPIRY_DATE = '10/31/2010'

    CREATE

    SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert

    INSERT INTO table1 (col1) VALUES (EncryptByKey(Key_GUID('MySymKey'), 'ThisAndThat.com'))

     

    [Decrypt in windows App]

    CREATE

    MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd'

    CREATE

    CERTIFICATE MyCert WITH SUBJECT = 'My DB Access', EXPIRY_DATE = '10/31/2010'

    CREATE

    SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert

    OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert

    SELECT Convert(VarChar,DecryptByKey(col1)) FROM table1

     

     

  • November 20, 2015 at 1:54 pm

    #1841460

    Hi All,

    I have a DB encrypted on one server that is retiring. I moved the database over to the new server. Now I'm wondering how I transfer over the key and the encryption?

    Regards,

    Bryan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply