Decrypt by key returning Null in SQL server 2005

Question

0.00/5 (No votes)

See more:

hello All,

I am trying the following encrypt and decrypt code in sql 2005 to check and then use in vb.net application.
The encryptbykey is working fine. But the decrypt by key retruning null.

The database field is nvarchar(16)

An additional query: If i change the symmetric key password in future, will the old encrypted data be lost?

Thanks in advance

SQL

'To encrypt data field

OPEN SYMMETRIC KEY DataProtector DECRYPTION BY password='12345'
Insert into [USER](USERID,USERP)
Values ('ID', Encryptbykey(KEY_GUID('DataProtector'),'Password'))
 CLOSE SYMMETRIC KEY DATAPROTECTOR



'To decrypt data

OPEN SYMMETRIC KEY DataProtector DECRYPTION BY password='12345'
Select USERID,DECRYPTBYKEY(USERP))as USERP FROM [user] where USERID = 'ID'
CLOSE SYMMETRIC KEY DataProtector

Posted 6-Mar-14 21:45pm

atul sharma 5126

Updated 6-Mar-14 21:46pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-03-06T22:28:00

"The encryptbykey is working fine."
Are you sure? How did you check? Answer: by not getting errors until you tried to use the data...

Do yourself a favour, and stop storing encrypted passwords - they are a major security risk.
Why? Simple: you need to provide a password each time to decrypt them - so everything I need to get access to your passwords list is stored...with your encrypted passwords.

Instead, use Hashed passwords - these are not reversible functions so the original value cannot be "decrypted" and stolen. There is some info here: Password Storage: How to do it.[^] - it's C#, but it'll give you the idea.

And to answer your other question:
" If i change the symmetric key password in future, will the old encrypted data be lost?"

Oh yes. That is kinda the idea of encryption keys: only the actual key will decrypt the data - so if you change you key, all the old data is scrap because it won;t work with the new key.