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


editting data in an encrypted column


editting data in an encrypted column

Author
Message
jpmuir
jpmuir
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
Hi all,

I'm attempting (unsuccessfully) to reset a password, the issue is that the column with the passwords in is encrypted. I don't know what key was used to encrypt the data or the what the certificate is. Is there a way of resetting the password in one of the rows without these bits of info?

Ive attempted copying a password that i know to be correct from a different db into the password column but it doesn't work, I'm assuming this is due to the way I'm trying to update the password without encrypting it upon entry?

I also attempted setting the password to Null so that I could attempt to change the password on the environment login screen - again without success.

any suggestions?

Thanks
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6425 Visits: 2032
I'm attempting (unsuccessfully) to reset a password, the issue is that the column with the passwords in is encrypted. I don't know what key was used to encrypt the data or the what the certificate is. Is there a way of resetting the password in one of the rows without these bits of info?


No. Without the key that was used to encrypt the data you will not be able to unencrypt the data, which you would need to be able to do in order to read the old values. Even if you cannot read the old values you need the encryption key in order to encrypt the new values so that when the value gets unencrypted by whatever application it is supporting it is getting the value you expect it to see.

Is there not a way in the application the database is supporting to reset the password?

Joie Andrew
"Since 1982"
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62497 Visits: 17959
Where is the encrypted data being decrypted? Is all the encryption handled in the application or in the database?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jpmuir
jpmuir
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
Joie Andrew - I don't think there's a way to reset the password from the application other than the change password option each user is given on the login page - unfortunately this requires the old password.

Sean Lange - I think the data is encrypted in the database rather than the application.


Sorry took a while to get back to you. Thanks for the quick responses especially over the holiday period. We have set up the user with another account so its not quite as urgent as it was, although the problem with the old username is still there.
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6425 Visits: 2032
Joie Andrew - I don't think there's a way to reset the password from the application other than the change password option each user is given on the login page - unfortunately this requires the old password.


So what you could try for future scenarios is something like this:

- Create a new user in the application and setup a known password
- Query for that user in the database
- Note the value of the encrypted password column for the known password
- If a user needs a password reset and does not know the password update that user record to have the password match what you set in step one
- Have the user reset their password in the application using the password you set in step one as the "old password"

Joie Andrew
"Since 1982"
jpmuir
jpmuir
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
I had tried to do that initially except I had naively used a known password from a different db which obviously used different encryption settings.

Thanks for the help! :-D
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