Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Encryption using TRIPLE_DES returns NULL Expand / Collapse
Author
Message
Posted Saturday, July 17, 2010 10:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:06 PM
Points: 211, Visits: 525
I am trying Encrypt some numbers using SS 2005

Here is my Table Structure
CREATE TABLE [dbo].[Accounts](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[ACCOUNT_NBR] varchar(max) NULL,
[SSN] varchar(max) NULL,
[Encrypted AC NBR] varbinary(max) NULL,
[Encrypted SSN] varbinary(max) NULL
) ON [PRIMARY]

GO

-- Inserted 400 such records
INSERT INTO Accounts VALUES ('6547824592798743','654982760',NULL,NULL)
INSERT INTO Accounts VALUES ('6547627592798743','657982760',NULL,NULL)




I am trying to Encrypt Account Number and update the [Encrypted AC NBR] filed with encrypted value.
To accomplish my requirements I created a Master Key, Certificate and a Symmetric Key

/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C0MpleXP@$$word'
GO



USE EncryptionDB
Go
SELECT * FROM sys.certificates


/*************** CREATE CERTIFICATE *********************************/
IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N'PasswordFieldCertificate'
)
CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';
GO

/*************** CREATE SYMMETRIC KEY *********************************/
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;

SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates

-- Check to see what is the GUID
SELECT KEY_GUID('PasswordFieldSymmetricKey') AS GUID
-- 09E2A600-F863-435D-8E7F-7D0C129DF3FD

/*************** ENCRYPT SENSITIVE DATA *********************************/
-- Openiing symetric key
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
-- Applying Encryption
UPDATE Accounts
SET [Encrypted AC NBR] =
ENCRYPTBYKEY(KEY_GUID('PasswordFieldSymetricKey'),ACCOUNT_NBR);

When i execute the Update statement where i am actually encrypting

It says that "-- (400 row(s) affected"

But my Result set is still NULLS

Please let me know where am i committing a mistake..

My Result SET
1      6547827592798743     654982760    NULL    NULL




Today is the tomorrow you worried about yesterday
Post #954471
Posted Monday, July 19, 2010 10:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:06 PM
Points: 211, Visits: 525
Any ideas or sugestions from vets wud be greatly appreciated.


Today is the tomorrow you worried about yesterday
Post #954945
Posted Monday, July 19, 2010 3:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:24 AM
Points: 994, Visits: 2,232
There is a typo in PasswordFieldSymetricKey. 'm' is missing from word Symmetric.

--Vadim.
Post #955158
Posted Monday, July 19, 2010 5:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:06 PM
Points: 211, Visits: 525
Ohh NO....
How silly...
My bad

Thank You



Today is the tomorrow you worried about yesterday
Post #955215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse