encryption/decryption all the columns in the table

Question

question

kotiRouthu asked Mar 13, '10

encryption/decryption all the columns in the table

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

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),
@SALARY 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,SALARY) 
    VALUES   
  (@UFNAME, @ULNAME, @ULOGINDI,
      EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD),
      EncryptByKey(Key_GUID('TEST_KEY'),@SALARY));   
  END;

--Insert Procedure

EXEC INSERTUSER 'Koti','Routhu','Koti_J_Routhu','GloryToLORD','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

Please help me to encrypt/decrypt all the columns in the table. Thank you

A later question (duplicate; now closed) was asked by the same person with the following additional comment:

i dont want to use TDE(Transparent Data Encryption)

sql-server-2008 encryption tde

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-14T05:08:06Z

Rob Farley answered Mar 14, '10

What you're describing isn't TDE.

USE YourDB;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ENVDECTEST;
GO
ALTER DATABASE YourDB
SET ENCRYPTION ON;
GO

This will encrypt everything in your database file. You can still read it, but the file and backups will now be encrypted - no-one will be able to access your data except through proper db connections.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-14T19:49:51Z

Rob Farley answered Mar 14, '10

If you have some reason why you don't want to use TDE, then you are stuck applying the encrypt functions to each column individually, effectively repeating the type of code you already have.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question