Encrypting the variable data from application

Question

Hi All,

ignore the gramatical mistakes.

I have been asked that "is it possible to store the data into SQL SERVER in encrypted format and while retrieving it should come as decrypted format" and "Is it possible to secure the table structure like if we click on script as it should not show the script"??

we have 1 application in that the data entered through the application should be store in an encrypted format in sql server and while retrieving the data through application it should come in decrypted format but if any 1 directly connect to the server (Through SSMS) he/she should no able to see the data.i know the column encryption method but in that i am not able to enter the data after data encryption.

is it possible to do that and if possible can u tell me the methods to acheive this ??

please help me regarding this .

Thanks in advance.

Answer 1

Yes it is possible, but safe I do not know...

USE AdventureWorks;
GO

--If there is no master key, create one now 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = Triple_DES
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128); 
GO
select * from HumanResources.Employee
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber 
    AS "Encrypted ID Number",
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
    AS "Decrypted ID Number"
    FROM HumanResources.Employee;
GO

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi Uri Dimant,

Thanks for the response.the code working good. how about the encrypting after a new row entered into the table??

Can u provide me a stored procedure that encrypt the data whenever new row insert into the table. and in the same to decrypt???Hence i am little weak in scripting.

Thanks a lot in advance.

Answer 3

Hi Sush104,

Regarding to your description, seems you need to creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table.

Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.
For more information, please refer to CREATE TRIGGER (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189799.aspx

---

Regards, Amber zhang