question

kotiRouthu avatar image
kotiRouthu asked

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-2008encryptiontde
10 |1200 characters needed characters left characters exceeded

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

Rob Farley avatar image
Rob Farley answered

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 characters needed characters left characters exceeded

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

Rob Farley avatar image
Rob Farley answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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