[sims] Very stressed. Sims invalid username / password for all users

[edutech4schools]

Hi all,

 

 

Following on from another post I decided to test the Sims backups at the few primary schools I work for. I created a virtual server and installed SQL2008r2 (full), then Sims and finally I used dbattach to restore the Sims databases from each school. This worked fine for the first few schools but I have come across an issue that is stressing me out a bit. The Sims database imports fine but when I try to login it says 'invalid username / password'.

 

The backups are created using SQL management studio, running a maintenance plan.

 

What I have tried so far,

 

-Loging in as different users

 

 

-In dbattach, restore the database, then detach the database and finally attach the database.

 

 

-Running the following script to export the database

REM -- Transfer Sims Login Info back to Database --

osql -S(local)\sims2008 -E -Q sims.sims.db_p_transfer_login

 

 

REM -- Create SQL Backup (*.Bak) of Sims SQL Database --

"D:\Program Files\Microsoft SQL Server\SQL2008\MSSQL\Binn\DbAttach.exe" /SERVER=(local)\sql2008 /DATABASE=sims /USER=sa /PASSWORD=??????? /AUTO /BACKUP /PATH="D:\Program Files\Microsoft SQL Server\MSSQ2008\MSSQL\Backup\Sims.BAK"

 

 

REM -- End Of Script --

 

 

But still no joy.

 

I did a Google search and found the following bit of code but have not tried it as I do not want to make things worse.

osql -S%COMPUTERNAME%\SIMS2008 -Usa -Psa_password_here -n -Q"exec sims.sims.db_p_transfer_login"

 

Anyone got any ideas.

Edited July 8, 2014 by edutech4schools

[vikpaw]

Sounds like you're doing the right thing.

That bit of code you found, you have already executed in the backup script above.

 

My first answer would have been to run that transfer logins script, then take a backup.

After that when you restore the database, it should push those logins back in to the system and work.

 

Make the backup after the transfer logins code as you have been then try just restoring the db using sql studio, not dbattach. See if that makes a difference.

Also with dbattach / restore are you getting any error messages? Could they perhaps be using trusted logins or something different.

 

Also, when you say you installed SIMS on the test server did you use an installable ISO and does the test server version of SIMS match exactly the backup source file version.? Are you using the latest version of dbattach..?

 

One last thing to try after you've checked the obvious above, and things like being on the same flavour of SQL at all sites e.g. 2008. You can run exec sims.db_p_install in a similar way to the first command and this should manually push any logins stored in the database into the test server. This is worth a shot.

 

====== WARNING: IF YOU'RE JUST TESTING AVOID BELOW HERE =============

If that fails - the last resort is not pleasant at all, but i would want to use dbattach to detach the database from the live server, copy it, then reattach it. This is the process that should automatically move the logins for you. BUT is way too risky if you're just testing. It's always scary and if there is something funny going on, you might stop everyone from logging in to the live server.

[edutech4schools]

Thanks for the help.

 

That bit of code you found, you have already executed in the backup script above.

 

The code I found seems to have different switches from the one I have been running.

 

Make the backup after the transfer logins code as you have been then try just restoring the db using sql studio, not dbattach. See if that makes a difference.

I will try that later tonight.

 

Also with dbattach / restore are you getting any error messages? Could they perhaps be using trusted logins or something different

No errors and not using trusted logins.

 

Also, when you say you installed SIMS on the test server did you use an installable ISO and does the test server version of SIMS match exactly the backup source file version.? Are you using the latest version of dbattach..?

Do not have the iso but used simsinst in the setups folder, but it does work fine when I use another schools database.

 

One last thing to try after you've checked the obvious above, and things like being on the same flavour of SQL at all sites e.g. 2008. You can run exec sims.db_p_install in a similar way to the first command and this should manually push any logins stored in the database into the test server. This is worth a shot.

Yes all the same SQL version.

I will do this later tonight.

[robjduk]

Are the databases named differently? We just migrated and get your issue if the connect.ini file has not been updated.

[edutech4schools]

Just so you know, When I look in SQL studio, under the Sims database, in Security, users are all the Sims users.

 

 

Going back to the code, This is the code I have been using

1

osql -S(local)\sims2008 -E -Q sims.sims.db_p_transfer_login

and this is the code I have not used as I found it on the internet

2

osql -S%COMPUTERNAME%\SIMS2008 -Usa -Psa_password_here -n -Q"exec sims.sims.db_p_transfer_login"

Then you say I could use this code

3

exec sims.db_p_install

EDIT: So the code I have been using (1) does not have exec and the one you suggested (3) does, so does that mean the code I found on the internet (2)is better?

 

 

Make the backup after the transfer logins code as you have been then try just restoring the db using sql studio, not dbattach. See if that makes a difference.

No joy using SQL studio. Imports fine but same issue.

 

 

Are the databases named differently? We just migrated and get your issue if the connect.ini file has not been updated.

No all databases are just named Sims. I have also checked all the connect.ini files.

Edited July 9, 2014 by edutech4schools

[vikpaw]

Didn't notice that! Yep exec is normal. Actually search for that transfer line here and you'll find a number of examples. You can even run it in SQL studio.

 

That would explain the problem if it wasn't doing what it should.

 

Where it has worked is because the logins have been transferred at some point in the past and not changed.

 

Edit sorry said install above, meant the transfer code.

[edutech4schools]

YAY all fixed. Somehow a typo had appeared in the code that moved the users. No idea how that happened as I was simply running the same batch file on each server.

 

Thanks for the help.

 

Final question, does FMS have the same issues? If so should the code look like this? with fms.fms

osql -S(local)\sims2008 -E -Q fms.fms.db_p_transfer_login

[vikpaw]

Yay :0)

There is similar code. Search the forum for that line and you'll find it. @Rawns posted both the sims and FMS ones a while back.

It's very similar if not identical bar the DB name.

[vikpaw]

found it, i was mobile before http://www.edugeek.net/forums/mis-systems/78287-passwords-reset-when-attaching-database.html#post696538