Encryption using TRIPLE_DES returns NULL

  • I am trying Encrypt some numbers using SS 2005

    Here is my Table Structure

    CREATE TABLE [dbo].[Accounts](

    [Serial] [int] IDENTITY(1,1) NOT NULL,

    [ACCOUNT_NBR] varchar(max) NULL,

    [SSN] varchar(max) NULL,

    [Encrypted AC NBR] varbinary(max) NULL,

    [Encrypted SSN] varbinary(max) NULL

    ) ON [PRIMARY]

    GO

    -- Inserted 400 such records

    INSERT INTO Accounts VALUES ('6547824592798743','654982760',NULL,NULL)

    INSERT INTO Accounts VALUES ('6547627592798743','657982760',NULL,NULL)

    I am trying to Encrypt Account Number and update the [Encrypted AC NBR] filed with encrypted value.

    To accomplish my requirements I created a Master Key, Certificate and a Symmetric Key

    /*************** CREATE MASTER KEY *********************************/

    IF NOT EXISTS (

    SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'

    )

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C0MpleXP@$$word'

    GO

    USE EncryptionDB

    Go

    SELECT * FROM sys.certificates

    /*************** CREATE CERTIFICATE *********************************/

    IF NOT EXISTS (

    SELECT * FROM sys.certificates WHERE name = N'PasswordFieldCertificate'

    )

    CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';

    GO

    /*************** CREATE SYMMETRIC KEY *********************************/

    CREATE SYMMETRIC KEY PasswordFieldSymmetricKey

    WITH ALGORITHM = TRIPLE_DES

    ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;

    SELECT * FROM sys.symmetric_keys

    SELECT * FROM sys.certificates

    -- Check to see what is the GUID

    SELECT KEY_GUID('PasswordFieldSymmetricKey') AS GUID

    -- 09E2A600-F863-435D-8E7F-7D0C129DF3FD

    /*************** ENCRYPT SENSITIVE DATA *********************************/

    -- Openiing symetric key

    OPEN SYMMETRIC KEY PasswordFieldSymmetricKey

    DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

    -- Applying Encryption

    UPDATE Accounts

    SET [Encrypted AC NBR] =

    ENCRYPTBYKEY(KEY_GUID('PasswordFieldSymetricKey'),ACCOUNT_NBR);

    When i execute the Update statement where i am actually encrypting

    It says that "-- (400 row(s) affected"

    But my Result set is still NULLS

    Please let me know where am i committing a mistake..

    My Result SET

    1 6547827592798743 654982760 NULL NULL

    [font="Verdana"]
    Today is the tomorrow you worried about yesterday:-)
    [/font]

  • Any ideas or sugestions from vets wud be greatly appreciated.

    [font="Verdana"]
    Today is the tomorrow you worried about yesterday:-)
    [/font]

  • There is a typo in PasswordFieldSymetricKey. 'm' is missing from word Symmetric.

    --Vadim.

    --Vadim R.

  • Ohh NO....

    How silly...

    My bad

    Thank You

    [font="Verdana"]
    Today is the tomorrow you worried about yesterday:-)
    [/font]

Viewing 4 posts - 1 through 3 (of 3 total)

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