SQL Server Performance

Encryption/Decryption all the row data

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by koti.routhu, Mar 16, 2010.

  1. koti.routhu New Member

    Hi all,
    With the following i am able to encrypt/decrypt only columns which are varbinary.I am not able to encrypt the integer/numeric values.
    To protect the one row,i have to encrypt each column in the row.Which results reducing the performace in realtime.CREATE
    MASTER KEY ENCRYPTION BY PASSWORD='LORD'CREATE
    CERTIFICATE ENCDECTEST WITH SUBJECT='LORD' CREATE SYMMETRIC KEY TESTKEY WITH ALGORITHM=TRIPLE_DES
    ENCRYPTION
    BY CERTIFICATE ENCDECTEST-- Create a Sample TableCREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),UPASSWORD VARBINARY(256))
    -- Create a Procedure to Insert Data in TableCREATE PROC [InsertUSER] @UFNAME
    VARCHAR(100), @ULNAME VARCHAR(100), @ULOGINDI
    VARCHAR(12), @UPASSWORD
    VARCHAR(20)AS
    BEGINOPEN SYMMETRIC KEY TESTKEY
    DECRYPTION BY CERTIFICATE ENCDECTEST;
    INSERT INTO [USERS](UFNAME, ULNAME, ULOGINDI, UPASSWORD)
    VALUES
    (@UFNAME, @ULNAME, @ULOGINDI,EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));
    END;EXEC INSERTUSER 'Koti','Routhu','Koti_J_Routhu','GloryToLORD'
    -- Procedure to Retrive UsersCREATE PROC [RetrieveUSER] @UID INT
    AS
    BEGINOPEN SYMMETRIC KEY TESTKEY
    DECRYPTION BY CERTIFICATE ENCDECTEST; SELECT UID, UFNAME, ULNAME, ULOGINDI, CONVERT(VARCHAR(20),DECRYPTBYKEY(UPASSWORD)) AS PASSWORD
    FROM USERS WHERE UID = @UID;END
    ; EXEC
    RetrieveUSER 100
    Is there any way to encrypt all the rowdata as one column adding with delimiters, and data stored in the all columns in encrypted format
    and when decrypt the row, resulted data should be populated as columns of that row.
    Thank You,
  2. satya Moderator

Share This Page