Post reply

AES encrypted column

  • March 14, 2012 at 7:09 am

    #257382

    Hi,

    I have been tasked with dumping data from an encrypted column. Acutally the data in the field has been encrypted via the client application using AES. It is not encrypted in the SQL table. I have never had to 'de-crypt' data before.

    The column is a varchar field and I just have to decrypt it and then pull the translated data.

    The developers have provided me the key - I just have no idea how to use it in a statement..

    Here's a sample of the encrypted data 'mPhZTe370xFPEwZCjniN8Q=='

  • March 14, 2012 at 7:22 am

    #1459162

    out of interest what is the data when it is un-encrypted? credit card numbers, passwords, sensitive information?

  • March 14, 2012 at 7:25 am

    #1459166

    it's password hint answers...

    I have the questions and the key from the devs.

  • March 14, 2012 at 7:29 am

    #1459169

    here's exactly how i've done it, along with the link i used to build my model.

    I used DESX, and changed the code below to AES_128.

    note AES requires Server 2003.

    --http://www.4guysfromrolla.com/articles/022107-1.aspx

    -- use the database tempdb

    USE tempdb;

    -- create symmetric key 'AES128SecureSymmetricKey'

    -- using the AES_128 encryption algorithm

    /*

    AES encryption algorithms are currently supported only on Windows 2003.

    If you are using Windows 2000 or Windows XP, you do not have access to AES encryption algorithms from SQL Server.

    If you try to use an AES encryption algorithm, you will get an error:

    Either no algorithm has been specified or the bit length and the algorithm specified for the key are not available in this installation of Windows.

    */

    -- and encrypt the key using the password

    -- 'StrongPassword'

    CREATE SYMMETRIC KEY AES128SecureSymmetricKey

    WITH ALGORITHM = AES_128

    ENCRYPTION BY PASSWORD = N'StrongPassword';

    -- must open the key if it is not already

    OPEN SYMMETRIC KEY AES128SecureSymmetricKey

    DECRYPTION BY PASSWORD = N'StrongPassword';

    -- declare and set varible @STR to store plaintext

    DECLARE @STR NVARCHAR(max)

    SET @STR = N'Hello DESX';

    -- declare and set varible @encrypted_str to store

    -- ciphertext

    DECLARE @encrypted_str NVARCHAR(MAX)

    SET @encrypted_str =

    EncryptByKey(Key_GUID('AES128SecureSymmetricKey'), @STR);

    -- display ciphertext

    SELECT @encrypted_str AS CipherText;

    -- declare and set varible @decrypted_str to store

    -- decrypted ciphertext

    DECLARE @decrypted_str NVARCHAR(MAX)

    SET @decrypted_str = DecryptByKey(@encrypted_str);

    -- display decrypted text

    SELECT CONVERT(NVARCHAR(100), @decrypted_str) AS PlainText;

    -- close and drop the key

    CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;

    DROP SYMMETRIC KEY AES128SecureSymmetricKey;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2012 at 7:31 am

    #1459171

    thanks you so much lowell.

    good karma to you

  • March 14, 2012 at 7:35 am

    #1459175

    I shall remember that Lowel, copy and ftp home.

    Kyrpto, was just making sure that the data wasn't going to breach any standard like PCI-DSS if it was credit card details in the raw format.

  • March 14, 2012 at 7:36 am

    #1459176

    krypto recopy my post, i edited it to use all nvarchar(max) columns; DESX uses varbinary as the cypher results, but AES is still text, so i tweaked and re-tested, and it works as expected.

    you should be able to paste a sample string in there for the encrypted value and confirm it decrypts correctly.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2012 at 8:21 am

    #1459222

    guess we are confused we have a password field (varchar) with an encrypted value of 'mPhZTe370xFPEwZCjniN8Q=='

    Am i using this correctly?

    USE tempdb;

    -- create symmetric key 'AES128SecureSymmetricKey'

    -- using the AES_128 encryption algorithm

    /*

    AES encryption algorithms are currently supported only on Windows 2003.

    If you are using Windows 2000 or Windows XP, you do not have access to AES encryption algorithms from SQL Server.

    If you try to use an AES encryption algorithm, you will get an error:

    Either no algorithm has been specified or the bit length and the algorithm specified for the key are not available in this installation of Windows.

    */

    -- and encrypt the key using the password

    -- 'StrongPassword'

    CREATE SYMMETRIC KEY AES128SecureSymmetricKey

    WITH ALGORITHM = AES_128

    ENCRYPTION BY PASSWORD = N'StrongPassword';

    -- must open the key if it is not already

    OPEN SYMMETRIC KEY AES128SecureSymmetricKey

    DECRYPTION BY PASSWORD = N'StrongPassword';

    -- declare and set varible @STR to store plaintext

    DECLARE @STR NVARCHAR(max)

    SET @STR = N'mPhZTe370xFPEwZCjniN8Q==';

    -- declare and set varible @encrypted_str to store

    -- ciphertext

    DECLARE @encrypted_str NVARCHAR(MAX)

    SET @encrypted_str =

    EncryptByKey(Key_GUID('AES128SecureSymmetricKey'), @STR);

    -- display ciphertext

    SELECT @encrypted_str AS CipherText;

    -- declare and set varible @decrypted_str to store

    -- decrypted ciphertext

    DECLARE @decrypted_str NVARCHAR(MAX)

    SET @decrypted_str = DecryptByKey(@encrypted_str);

    -- display decrypted text

    SELECT CONVERT(NVARCHAR(100), @decrypted_str) AS PlainText;

    -- close and drop the key

    CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;

    DROP SYMMETRIC KEY AES128SecureSymmetricKey;

  • March 14, 2012 at 8:46 am

    #1459242

    to check that one, specific value, this will do what you ask, but you must change the "StrongPassword" to whatever the key is the developers gave you:

    USE tempdb;

    -- create symmetric key 'AES128SecureSymmetricKey'

    -- using the AES_128 encryption algorithm

    /*

    AES encryption algorithms are currently supported only on Windows 2003.

    If you are using Windows 2000 or Windows XP, you do not have access to AES encryption algorithms from SQL Server.

    If you try to use an AES encryption algorithm, you will get an error:

    Either no algorithm has been specified or the bit length and the algorithm specified for the key are not available in this installation of Windows.

    */

    -- and encrypt the key using the password

    -- 'StrongPassword'

    CREATE SYMMETRIC KEY AES128SecureSymmetricKey

    WITH ALGORITHM = AES_128

    ENCRYPTION BY PASSWORD = N'StrongPassword';

    -- must open the key if it is not already

    OPEN SYMMETRIC KEY AES128SecureSymmetricKey

    DECRYPTION BY PASSWORD = N'StrongPassword';

    -- declare and set varible @encrypted_str to store

    -- ciphertext

    DECLARE @encrypted_str NVARCHAR(MAX)

    --we KNOW this value came from database for testing

    SET @encrypted_str = N'mPhZTe370xFPEwZCjniN8Q==';

    -- display ciphertext

    SELECT @encrypted_str AS CipherText;

    -- declare and set varible @decrypted_str to store

    -- decrypted ciphertext

    DECLARE @decrypted_str NVARCHAR(MAX)

    SET @decrypted_str = DecryptByKey(@encrypted_str);

    -- display decrypted text

    SELECT @decrypted_str AS PlainText;

    -- close and drop the key

    CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;

    DROP SYMMETRIC KEY AES128SecureSymmetricKey;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2012 at 8:50 am

    #1459245

    thanks again lowell

    you da man

Viewing 10 posts - 1 through 9 (of 9 total)

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