Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xrum
Yak Posting Veteran
87 Posts |
 Posted - 2010-05-11 : 15:26:06
|
| hi,my new database stores encrypted passwords.I need to move everything from one db to another, but the passwords in the old database as in text format.I am using a simple decrypt/encrypt .net funciton in my application to encrypt them before I store the passwords in the db.How do i go about this? i'm stuck. If i move them as is, noone will be able to login, as i decrypt the password before taking out of the db which will make them invalid....... |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2010-05-12 : 04:24:14
|
| you can use the stored procedure sp_helprevlogin to get all the user names and passwords, and then you can execute the same in the other server/instance. But all the passwords will be in encrypted form only.ArnavEven you learn 1%, Learn it with 100% confidence. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-05-12 : 04:42:47
|
| sp_helprevlogin is for sql server users, not custom users created in a database table.What kind of encryption/decryption algorithm do you use? You can most likely find a sql server version of the algorith and encrypt all the passwords that are not encrypted yet, directly in the database and then migrate them all using a backup or export them to file or something.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 04:57:41
|
| 1) Move database2) Run a one-time process to encrypt the passwords, in situ (you will probably have to use your .NET App/function for this so that it is the same logic as the APP uses, but obviously if you can do it all in SQL it will be easier / faster / self contained.)We added an "encrpyed-password" column when going from old-fashioned/insecure "plain text" passwords to encrypted so we could do it in situ. During the change over the login-validation routine would accept either Plain or Encrypted password. Once we had fixed everything so that there were no more Plain Text requests in the application we then deleted the plain-text password column form the DB/table.By the by, you mention encrypt/decrypt - it is more normal for a password routine to have a one-way encryption so there is no decryption possible. This approach is still at risk from a dictionary attack, so normal to then append some extra info before encryption (e.g. the ID of the user record) so that the password "foobar" is never encrypted the same for two people. So-called HASH and SALT (Wikipeadia has some useful explanations)And then, next up from that, you need a "I forgot my password" routine, given that you can no longer send the user their original password. We tackle that with two additional fields in the user table - temporary password, and expiry time. When the user does a "lost password" request we set the temporary password (and email them the plain-text version) which is valid for, say, 2 hours. We then let them login with either the original password (not yet overwritten in the DB, and surprising how often people remember it!) or the temporary password within the permitted time window; if they use the temporary password we take them to a "change your password routine" where they have to choose a new password and we then save that, and delete the temporary password. |
|
|
|
|
|
|
|
|
Moving encrypted passwords