DECRYPTION Error

Question

DECRYPTION Error

pritesh-807803

Mr or Mrs. 500

Points: 564
More actions
April 26, 2010 at 10:25 pm

#218262

Hi All,
Pls look at one of sample where we tried to encrypt and decrtpt data. but it is not happening.
Declare @AditiTest Table
(UserID int, UserName nvarchar(50),UPassword nvarchar(20))
Select * from @AditiTest
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'SQLAuthority'
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
INSERT INTO @AditiTest
SELECT 1 AS UserID,'Pritesh Patel' as UserName,ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'HireCraft') as UPassword
Select * from @AditiTest
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT UPassword,DECRYPTBYKEY(UPassword) AS DecryptSecondCol
FROM @AditiTest
DROP SYMMETRIC KEY TestTableKey
DROP CERTIFICATE EncryptTestCert
DROP MASTER KEY

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

What error are you getting? If it's the password complexity error, try a password of something like 4SQLAuthority! instead. If it's the string or binary data would be truncated, make the following changes (UPassword field should be VARBINARY and you'll need to do a CONVERT once you decrypt):

Declare @AditiTest Table

(UserID int, UserName nvarchar(50),UPassword VARBINARY(256))

Select * from @AditiTest

OPEN SYMMETRIC KEY TestTableKey DECRYPTION

BY CERTIFICATE EncryptTestCert

INSERT INTO @AditiTest

SELECT 1 AS UserID,'Pritesh Patel' as UserName,ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'HireCraft') as UPassword

Select * from @AditiTest

OPEN SYMMETRIC KEY TestTableKey DECRYPTION

BY CERTIFICATE EncryptTestCert

SELECT UPassword, CONVERT(VARCHAR, DECRYPTBYKEY(UPassword)) AS DecryptSecondCol

FROM @AditiTest

K. Brian Kelley
@kbriankelley

pritesh-807803 Mr or Mrs. 500 Points: 564 More actions · Answer 2

Hi

Thanks for your replay..

If you run my submitted code you will find decryption data is showing as NULL.

mean Data is encrypted but decryption is not happening

thanks,

Pritesh

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Replace the portions of your code with the applicable portions of the code I posted and it will work. I tested before posting.

K. Brian Kelley
@kbriankelley