EncryptByKey and DecryptByKey Newbie???

  • Kevin Snyder

    Ten Centuries

    Points: 1082

    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" ????

  • Aaron Ingold

    SSCrazy Eights

    Points: 9070

    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...

  • Kevin Snyder

    Ten Centuries

    Points: 1082

    That worked!

    Q. Because it converted binary to varchar?

  • Aaron Ingold

    SSCrazy Eights

    Points: 9070

    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...

  • Kevin Snyder

    Ten Centuries

    Points: 1082

    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

     

     

  • Bryan Murtha

    SSC Enthusiast

    Points: 135

    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 6 (of 6 total)

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