SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encryption using TRIPLE_DES returns NULL


Encryption using TRIPLE_DES returns NULL

Author
Message
Minnesota - Viking
Minnesota - Viking
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 564
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:-)

Minnesota - Viking
Minnesota - Viking
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 564
Any ideas or sugestions from vets wud be greatly appreciated.


Today is the tomorrow you worried about yesterday:-)

rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 2306
There is a typo in PasswordFieldSymetricKey. 'm' is missing from word Symmetric.

--Vadim.
Minnesota - Viking
Minnesota - Viking
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 564
Ohh NO....
How silly...
My bad

Thank You


Today is the tomorrow you worried about yesterday:-)

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search