SQL Server Performance

Encrypting all the columns in a row

Discussion in 'SQL Server 2008 General Developer Questions' 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
    BEGIN
    OPEN 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