x

Encrypt/decrypt all the columns in the table.

Possible Duplicates:
encryption/decryption all the columns in the table
how do you encrypt an entire SQL 2005 database?

I want to encrypt and decrypt the all columns in the table.

and also i dont want to use TDE(Transparent Data Encryption).

With the following i m able to encrypt/decrypt only one column in the table.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='LORD'

CREATE CERTIFICATE ENCDECTEST WITH SUBJECT='LORD'

SELECT * FROM SYS.CERTIFICATES

CREATE SYMMETRIC KEY TESTKEY WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY CERTIFICATE ENCDECTEST

--Sample table

CREATE TABLE USERS (UID INT IDENTITY(100,1), UFNAME VARCHAR(100), ULNAME VARCHAR(100), ULOGINDI VARCHAR(50), UPASSWORD VARBINARY(256)) Procedure to Insert Data in Table

ALTER PROC [InsertUSER]
@UFNAME VARCHAR(100), @ULNAME VARCHAR(100),
@ULOGINDI VARCHAR(12),
@UPASSWORD VARCHAR(20) AS BEGIN opening the key as it is not already
OPEN SYMMETRIC KEY TESTKEY DECRYPTION BY CERTIFICATE ENCDECTEST;

  • Insert statement INSERT INTO [USERS] (UFNAME, ULNAME, ULOGINDI, UPASSWORD) VALUES
    (@UFNAME, @ULNAME, @ULOGINDI, EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));
    END; --Insert Procedure

EXEC INSERTUSER 'Koti','Routhu','Koti_J_Routhu','GloryToLORD' -- Procedure to Retrive Users

CREATE PROC [RetrieveUSER] @UID INT AS BEGIN -- open the key as it is not already OPEN SYMMETRIC KEY TESTKEY DECRYPTION BY CERTIFICATE ENCDECTEST; -- Select statement SELECT UID, UFNAME, ULNAME, ULOGINDI, CONVERT(VARCHAR(20),DECRYPTBYKEY(UPASSWORD)) AS PASSWORD
FROM USERS WHERE UID = @UID; END;

  • Execute Retrive Procedure EXEC RetrieveUSER 100

  • With the above i m able to encrpt/decrypt only "UPASSWORD" column only.I want to encrypt/decrypt all the columns in the table.

    Please help me in this regard. Thank you.

    more ▼

    asked Mar 14, 2010 at 09:16 AM in Default

    avatar image

    kotiRouthu
    22 2 2 4

    Can you please remember to format any code in your questions to assist others in reading your question and then helping you. You simply highlight the code section and press the 'code' button in the text editing toolbar. Thanks!

    Mar 14, 2010 at 10:00 AM Fatherjack ♦♦

    I voted to close this because it is virtually identical the your previous question. I added the "not TDE" part to the other question.

    Mar 14, 2010 at 01:16 PM Tom Staab ♦

    can you explain why you dont want to use TDE please?

    Mar 14, 2010 at 02:33 PM Fatherjack ♦♦
    (comments are locked)
    10|1200 characters needed characters left

    0 answers: sort voted first
    Be the first one to answer this question
    toggle preview:

    Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

    Follow this question

    By Email:

    Once you sign in you will be able to subscribe for any updates here

    By RSS:

    Answers

    Answers and Comments

    SQL Server Central

    Need long-form SQL discussion? SQLserverCentral.com is the place.

    Topics:

    x54
    x15

    asked: Mar 14, 2010 at 09:16 AM

    Seen: 2403 times

    Last Updated: Mar 15, 2010 at 07:03 AM

    Copyright 2016 Redgate Software. Privacy Policy