+ Post New Thread
Results 1 to 13 of 13
MIS Systems Thread, Migrated to SQL 2012 - Tons of password resets! in Technical; We migrated our SIMS DB to SQL 2012 last week when we carried out the autumn upgrade, moving to new ...
  1. #1

    Join Date
    Apr 2007
    Location
    Croydon
    Posts
    501
    Thank Post
    18
    Thanked 31 Times in 30 Posts
    Rep Power
    22

    Migrated to SQL 2012 - Tons of password resets!

    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?

  2. #2
    Mcshammer_dj's Avatar
    Join Date
    Feb 2007
    Location
    Portsmouth
    Posts
    944
    Thank Post
    35
    Thanked 165 Times in 133 Posts
    Rep Power
    94
    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.

  3. #3

    Join Date
    Apr 2007
    Location
    Croydon
    Posts
    501
    Thank Post
    18
    Thanked 31 Times in 30 Posts
    Rep Power
    22
    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.

  4. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,009
    Thank Post
    680
    Thanked 1,398 Times in 1,158 Posts
    Rep Power
    353
    Quote Originally Posted by adamf View Post
    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?

  5. #5

    Join Date
    Apr 2007
    Location
    Croydon
    Posts
    501
    Thank Post
    18
    Thanked 31 Times in 30 Posts
    Rep Power
    22
    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.

  6. #6

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,433
    Thank Post
    368
    Thanked 646 Times in 528 Posts
    Rep Power
    159
    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.

  7. #7

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,009
    Thank Post
    680
    Thanked 1,398 Times in 1,158 Posts
    Rep Power
    353
    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...?

  8. #8

    Join Date
    Apr 2007
    Location
    Croydon
    Posts
    501
    Thank Post
    18
    Thanked 31 Times in 30 Posts
    Rep Power
    22
    @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.

  9. #9

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,433
    Thank Post
    368
    Thanked 646 Times in 528 Posts
    Rep Power
    159
    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!

  10. #10

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,009
    Thank Post
    680
    Thanked 1,398 Times in 1,158 Posts
    Rep Power
    353
    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.

  11. #11

    Join Date
    Sep 2007
    Location
    Somerset
    Posts
    62
    Thank Post
    5
    Thanked 15 Times in 12 Posts
    Rep Power
    17
    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"

  12. #12

    Join Date
    Jan 2014
    Location
    Isle Of Wight
    Posts
    86
    Thank Post
    90
    Thanked 8 Times in 8 Posts
    Rep Power
    3
    I migrated our SQL databases from 2008 to 2012 this morning, so far no issues have reared their heads.

  13. #13

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,332
    Thank Post
    36
    Thanked 353 Times in 238 Posts
    Rep Power
    79
    There really should not be an issue. Please raise a case.

SHARE:
+ Post New Thread

Similar Threads

  1. SIMS slow after migration to SQL 2008
    By teejay in forum MIS Systems
    Replies: 7
    Last Post: 21st November 2010, 07:50 PM
  2. Replies: 0
    Last Post: 21st January 2010, 10:32 AM
  3. Migrate SIMS database to SQL 2008
    By speckytecky in forum MIS Systems
    Replies: 8
    Last Post: 10th October 2009, 09:43 AM
  4. Bulk password reset to a default?
    By MGSTech in forum Windows
    Replies: 6
    Last Post: 2nd September 2008, 07:34 PM
  5. Ease of SIMS migration to SQL 2005
    By Ric_ in forum MIS Systems
    Replies: 12
    Last Post: 29th February 2008, 02:06 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •