July 17, 2010 at 10:50 pm
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
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
July 19, 2010 at 10:24 am
Any ideas or sugestions from vets wud be greatly appreciated.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
July 19, 2010 at 3:58 pm
There is a typo in PasswordFieldSymetricKey. 'm' is missing from word Symmetric.
--Vadim.
--Vadim R.
July 19, 2010 at 5:21 pm
Ohh NO....
How silly...
My bad
Thank You
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply