x

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)

more ▼

asked Mar 13, 2010 at 11:38 AM in Default

kotiRouthu gravatar image

kotiRouthu
22 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Mar 14, 2010 at 05:08 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 14, 2010 at 07:49 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left
Your answer
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:

x1834
x38
x9

asked: Mar 13, 2010 at 11:38 AM

Seen: 1909 times

Last Updated: Mar 14, 2010 at 12:30 PM