November 30, 2005 at 2:26 pm
My company is looking into encrypting data. We are currently using SQL 2000. How good is SQL 2005 encrypting feature compared to other s/w from third party companies on the market? This is my first time to explore data encryption so I would like to hear your epxerience. Thanks.
December 1, 2005 at 7:49 am
SQL 2005 supports encrypting/decrypting data natively.
The following code is from BOL:
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 = AES_256
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
-- 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
December 5, 2005 at 11:27 am
Hi
I've just looked at the following:
Encryptinizer from http://www.netlib.com
SecureDB (http://www.valyd.com)
DBEncrypt from http://www.appsecinc.com
We have ended up opting for SeureDB from valyd. It offered all the features that we needed, the GUI was easiest to use and the pricing and performance were right.
check out this link:
http://nwc.securitypipeline.com/showArticle.jhtml?articleID=18901525
good luck
Dave
December 9, 2005 at 9:27 am
It entirely depends on your requirements, but 2005 natively encrypts fields, objects et al.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply