[sims] cannot set application role 'sims' because it does not exist or the p/w is incorrect

[harryeds]

Hi

 

I'm trying to upgrade an old SIMS db on a virtual machine. I've upgraded it to db version 3.61.387 and done the SQL2005 migration but now get the following errors:

 

(I have the correct - I think - versions of SIMS for the timeframe we're talking about, 7.97 for the main app, 2.11 for dbupgrade)

 

1. cannot set application role 'sims' because it does not exist or the password is incorrect when trying to log into dbupgrade using sysman & default password

 

I've tried detaching and re-attaching the database as advised in supportnet

 

2. can log into dbupgrade with sa user & password, but when trying to perform the next lot of upgrades i get:

 

Setuser failed because of one of the following reasons: the database principal 'sims' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

 

The SQL Batch which caused the error was :

 

----------- End of file -------------------------------------------

 

--schema version and model

if not exists(select 1 from sysusers where uid = user_id('sims') and isapprole = 1)

begin

SETUSER 'sims'

end

 

3. When trying to log into sims using sysman login, I get reason 0. As it says in http://www.edugeek.net/forums/mis-systems/12680-sql-migration-login-failed-reason-0-a.html , SIMS can see the database because it brings up 'Invalid username/password' for an incorrect user/pw combination but reason 0 for a correct one. I've tried all the suggestions in the above thread.

 

Any ideas, anyone?

 

Thanks

Harry

[matt40k]

If you run dbupgrade, what version does it think the database is? It sounds like it's failed on one of the upgrades and you need to try again.

[harryeds]

Thanks for the reply Matt. It knows that it's on version 3.61.387. When I ran the SQL 2005 migration, I could connect to the SQL 2000 server but it didn't list any databases - I had to load the database file manually. Surely if it had failed at any point during the dbupgrade process, it would have brought up an error message?

[vikpaw]

I sent you a load of my archive files I guess you got them. Sorry they were messed up, you'd have to sort through them and check the version number in properties for some of them as they came from various sources.

[jinnantonnixx]

Good advice in the above posts. Is it OK now?

 

SIMS uses application roles to do its database tasks (rather than basing it on user access). Sometimes these roles don't get set up or go wrong during migration. Even if the role password is wrong, the whole system will fail.

 

Luckily, there's a routine call db_p_install which does a lot of things, including reset role passwords and memberships. If your client and database are at the correct version, you could do worse than try this routine.

[harryeds]

Thanks jinnantonnixx - I'll give that a go. Didn't you get my PM vikpaw when you sent me those files - thanks alot mate much appreciated.

 

It does seem like there's no application roles set up cos the table is empty in sql management studio - take it that aint how it should be?

 

Harry

[harryeds]

Dunno if I've done it right (no sql experience) but I've gone into sql management studio - expanded the sims db - expanded programmability - expanded stored procedures and then executed db_p_install

 

When I still got reason 0 and other above same messages i tried executing all db_p_*

 

still no luck -

 

I've got the right software version, upgraded to the right db version and run the migration - can't understand why I keep getting all these errors!!

[matt40k]

Have you applied this 3.99.001 etc and 3.99.501 etc patches? Most likely those haven't run

[harryeds]

YES MATT!!

 

Top man, that's sorted it.

 

Cheers mate.

[vikpaw]

:party: HUZZAH!

 

I'll get the ready Harry, will there be advanced previews..?