Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

editting data in an encrypted column Expand / Collapse
Author
Message
Posted Monday, December 24, 2012 5:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2012 2:50 AM
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


Post #1399865
Posted Wednesday, December 26, 2012 3:13 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 11:07 AM
Points: 710, Visits: 1,452
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"
Post #1400196
Posted Wednesday, December 26, 2012 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1400253
Posted Monday, December 31, 2012 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2012 2:50 AM
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.
Post #1401318
Posted Monday, December 31, 2012 2:20 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 11:07 AM
Points: 710, Visits: 1,452
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"
Post #1401320
Posted Monday, December 31, 2012 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2012 2:50 AM
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!
Post #1401330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse