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

kotiRouthu gravatar image

kotiRouthu
22 2 2 2

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x38
x14

asked: Mar 14, 2010 at 09:16 AM

Seen: 2105 times

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