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

    avatar image

    kotiRouthu
    22 2 2 4

    (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

    avatar image

    Rob Farley
    5.8k 16 22 28

    (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

    avatar image

    Rob Farley
    5.8k 16 22 28

    (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.

    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:

    x2091
    x54
    x13

    asked: Mar 13, 2010 at 11:38 AM

    Seen: 2455 times

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

    Copyright 2016 Redgate Software. Privacy Policy