editting data in an encrypted column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » editting data in an encrypted column

editting data in an encrypted column

Rate Topic

Display Mode

Topic Options

Author

Message

jpmuir

Posted Monday, December 24, 2012 5:09 AM

Forum 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

Joie Andrew

Posted Wednesday, December 26, 2012 3:13 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

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

Sean Lange

Posted Wednesday, December 26, 2012 7:53 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547, Visits: 8,204

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

Post #1400253

jpmuir

Posted Monday, December 31, 2012 2:14 AM

Forum 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

Joie Andrew

Posted Monday, December 31, 2012 2:20 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

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

jpmuir

Posted Monday, December 31, 2012 2:59 AM

Forum 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! BigGrin

Post #1401330

charlesrivera564

Posted Tuesday, January 01, 2013 5:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 01, 2013 4:56 AM
Points: 1, Visits: 8

You will not be able to unencrypt the data without the key you used to encrypt the data. Is there no way or close source to found the key ?

internet gambling

Post #1401577

opc.three

Posted Tuesday, January 01, 2013 5:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 6,696, Visits: 11,716

charlesrivera564 (1/1/2013)

You will not be able to unencrypt the data without the key you used to encrypt the data. Is there no way or close source to found the key ?

Both of your posts have been reported as containing link-spam in your signature. Please go spam for internet gambling sites somewhere else.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401583

« Prev Topic | Next Topic »

Permissions