Hi,
I am trying to bulk update the weblogin and password for imisusers. The only way that is mentioned in the forums, is using the MembershipWebService.asmx/RegisterWebUser webservice. We have about 400000 rows and going the webservice way would involve lot of traffic on the server. Is there any way we can directly update the database using stored procedures instead of webservice?
Thanks
Nandhini
Here's some perl code which generates some salt and the corresponding iMIS-compatible encrypted password
perl -e 'use MIME::Base64(); use Digest::SHA1 qw(sha1); $password="helloworld"; $salt=rand(); print MIME::Base64::encode($salt); $password=~s/(.)/$1\000/g; print MIME::Base64::encode(sha1($salt . $password));'
MC41NTY4MTAxMzQzOTY5ODY=
j/KYCA2ndit6SN/ttxkSz/GZgCY=
and some code to check a password :-
perl -e 'use MIME::Base64(); use Digest::SHA1 qw(sha1); $password="helloworld"; $salt=MIME::Base64::decode("MC45MjIxNzE4NzAwODczNzI="); $password=~s/(.)/$1\000/g; if(MIME::Base64::encode(sha1($salt . $password)) eq "oHw6jmq2g+KM9Pizxic35rrUUE8=\n"){print "Correct"}else{print "Wrong"};'
Correct
perl -e 'use MIME::Base64(); use Digest::SHA1 qw(sha1); $password="dumbguess"; $salt=MIME::Base64::decode("MC45MjIxNzE4NzAwODczNzI="); $password=~s/(.)/$1\000/g; if(MIME::Base64::encode(sha1($salt . $password)) eq "oHw6jmq2g+KM9Pizxic35rrUUE8=\n"){print "Correct"}else{print "Wrong"};'
Wrong
I managed to incorporate the above perl into a script which updates
user passwords, as well as creates new user web logins. Using the
activestate compiler, this was compiled into a .EXE, and added into
the iMIS Desktop "Generate Reports" option, so is now available to our
staff as a "one click" item they can select at any time (eg: to
auto-create a login and/or reset a users lost password right away).
It also emails new credentials to users.
It's also capable of processing an entire existing iMIS system (eg: to
create initial logins for everyone)
It also runs nightly on a windows schedule, and picks up any new users
added during the day, creates logins for them, and emails out the
credentials.
Phew!! Let me know if anyone's interested in that kind of stuff.
Hi
I'm really interested in this script - would be great if you could pass it on.
Thanks, Jenny
Jenny Taylor
The Law Society of Scotland
I would actually be interested in the script if the offer is still open
--
Keith A. Williams
Irrigation Association
keith@irrigation.org
http://www.irrigation.org
I found a TSQL way mention at http://www.imiscommunity.com/generate_logins_from_unencrypted_passwords_... that I was going to implement if that helps.
source code is here:
http://chrisdrake.com/general_imis_password_generator.pl.txt
I can probably make some adjustments and package it up as a compiled .EXE program where you can pass in the paramaters needed to make it work on your own system (stuff like database username/password, SMTP server/login/password, greeting email, and so on).
If you need instructions for how to add this into your iMIS menu, I can probably make some up for you as well.
This script was born from a script which does other stuff - so there's thing in there that are probably unused, and this is slightly customised for the way our club uses iMIS already - you'll probably not need the clubnames/chapter/endorsement stuff (if it's even used - I think that might be a carryover from the script I started from).
Enjoy!
Yes and no. You can update
Yes and no. You can update the password using the aspnet_Membership_SetPassword stored procedure, however you will need a way to encrypt the password (the sproc doesn't do it for you). (See sample code at the end of this comment.)
There is no sproc that allows username changes (the ASP.Net Membership Provider does not support username changes), but you may be able to just update the UserName and LoweredUserName values in aspnet_Profile; I have not tested this and do not know if it will have adverse effects.
You will also need to update the usernames in Name_Security, UserMain, and Users; the passwords do not need to be updated, as none of them are used anymore. Finally, delete the name of any user whose username/password you change from the UserLegacyInfo table.
Sample code for encrypting a user's password (C#):
private static string GetSalt() { byte[] buf = new byte[16]; (new RNGCryptoServiceProvider()).GetBytes(buf); return Convert.ToBase64String(buf); } private static string EncodePassword(string pass, string salt) { byte[] bIn = Encoding.Unicode.GetBytes(pass); byte[] bSalt = Convert.FromBase64String(salt); byte[] bAll = new byte[bSalt.Length + bIn.Length]; byte[] bRet; Buffer.BlockCopy(bSalt, 0, bAll, 0, bSalt.Length); Buffer.BlockCopy(bIn, 0, bAll, bSalt.Length, bIn.Length); HashAlgorithm s = HashAlgorithm.Create("SHA1"); bRet = s.ComputeHash(bAll); return Convert.ToBase64String(bRet); } Usage: string password = "theuser'snewpassword"; string salt = GetSalt(); password = EncodePassword(password, salt); // Call the aspnet_Membership_SetUserPassword stored procedure, passing 1 (Encrypted) for the @PasswordFormat field.