[sims] Migrated to SQL 2012 - Tons of password resets!

[adamf]

We migrated our SIMS DB to SQL 2012 last week when we carried out the autumn upgrade, moving to new hardware at the same time. We followed the instructions from Capita as there is no migration utility yet and now a large percentage of people are getting invalid username/password errors. At first, we thought it was the usual forgotten password syndrome, but we've reset close to 100 passwords this morning already.

 

Has anybody else migrated and noticed this behaviour?

[Mcshammer_dj]

How did you move the data. It is worth remembering that in the current structure SIMS password s are stored in the master.mdf and only passed to the sims database during a dbattach action. I would imagine that the capita migration ensures that the passwords are transfered as a part of the migration.

[adamf]

By following the guide release by Capita on SupportNet - Resource 20241. Detached the db from the old server using dbAttach, copied it across to the new server and reattached it using dbAttach.

[vikpaw]

By following the guide release by Capita on SupportNet - Resource 20241. Detached the db from the old server using dbAttach, copied it across to the new server and reattached it using dbAttach.

That's the process that usually works, at least on SQL 2008 and prior to that. Maybe something has changed...?

I guess you didn't do a dummy run on a test server first?

[adamf]

I did try a migration out on our test server first, logged in with my own user account and it worked. When we did it on the live server, logged in with my own account and it worked as expected. Given that it worked, I didn't try other staff user accounts, it would have been difficult to do it without knowing their password in any case.

[matt40k]

It's nothing to do with sql2012, it's the age old master issue, there is a store procedure that backups the SQL user/passwords from the master db to the sims/fms db, I'm guessing you don't change your password very often for sims. You have three options, one suck it and accept you'll have to reset loads of passwords, two, uses it as an excuse to move to more secure windows authentication, and the third is to backup the db on sql2012, go back to the old server run (I think) exec sims.db_p_transfer_logins, create a backup, restore it to SQL 2012, them restore your sql2012 backup you did... Hopefully that'll get you your logins back... Just check the 3rd option with your sims support team as it can get messy very quickly.

[vikpaw]

I know a couple of passwords and have test accounts, but you can never be sure. The only real way to test would be to make a clone of your current setup, change a number of passwords, then do the update with that data, or take it from the test server. @matt40k - why didn't the dbattach detach - reattach procedure run the relevant transfer logins storedprocs...?

[adamf]

@vikpaw - I think I will do that in future, although I don't plan on moving it to another server anytime soon.

 

We ended up with just resetting them all as people discovered they were not working.

[matt40k]

I think there is a report you can run to give you a list... if you've their email address in SIMS, you can email the temp password to them, if there was only a tool that could bulk import email address

@vikpaw - pass, most likely a CR somewhere about it - I know they're looking at it - but that's a while off - they need to do something for SOLUS3 db as well!

[vikpaw]

I requested a parameter option for dbattach to allow the logins to be transferred without needing to detach, but i'm sure it got lost somewhere.

[splattthecat]

We use the following to transfer logins as part of our daily backup script to reduce the risk of this happening:

osql -S%COMPUTERNAME%\%NEW_INSTANCE_NAME% -Usa -P%NEW_SA_PASSWORD% -n -Q"exec %SIMSDB%.sims.db_p_transfer_login"

[IWDave]

I migrated our SQL databases from 2008 to 2012 this morning, so far no issues have reared their heads.

[PhilNeal]

There really should not be an issue. Please raise a case.