Cannot find the user '$ndo$shadow'

mcurrie

I just did a technical upgrade from Nav 4.01 to 5.0. I also upgraded from SQL 2000 to a SQL 2005 db.

The db seems fine from the testing I've done as far as viewing\modifing data - doing regular user based transactions however when I attemp to import objects I get the following message:

15151,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot find the user '$ndo$shadow', because it does not exist or you do not have permission.

SQL:
GRANT ALL ON "database"."dbo"."Tablename" TO [$ndo$shadow]

I've read numerous posts relating to $ndo$shadow and how to fix this issue in previous versions of SQL however this app role does not exist in SQL 2005.

Just wondering if anyone has run into this issue before.

Thanks.

ara3n

you need to synchronize security.

Also in file->database->alter->advance tab->security Model change it to standard.

mcurrie

I am on the Standard security model already and I have tried doing a sync. When I do try the sync it only runs for a fraction of a second which makes me believe it is not actually doing anything.

Any suggestions?

ara3n

The ndo$shadow is an application role .

In SQL SMS looked at this location and see if you see $ndo4shadow role

Database->YourDatabaseName->Security->Roles->Application Roles.

mcurrie

That app role does not exist and it will not allow me to create it. From what I have read it does not exist when running either SQL 2005 or a version of Nav higher then 4.03???

dmccrae

This app role is still used in SQL Server 2005 when using the Standard security model.

When you upgraded from 4.01 your security model should have been set to Enhanced, since this is the model used in 4.0.

Did you then change it to Standard yourself?

mcurrie

No, it was Standard after the upgrade was complete. I never changed it.

ara3n

I don't know if there is a way to create the app role.

I suggest to do a navision backup .fbk.
Create a new database and restore the Navision backup .fbk.

dmccrae

It sounds like that is the root of the problem.

Do you have many users in the system?

I am wondering if you could change the model to Enhanced and back again to Standard, to re-create the app role.

mcurrie

It is a test database so I'm the only user currently. I tried changing the the security model to enhanced but it seems to just hang. I tried this yesterday and let it run over night. When the I checked this morning the application was not responding so I had to kill it.

I'm going to try the backup\restore and see if that creates the app role.

Thanks for all the suggestions thus far. The restore could take a while so I will update when finished.

Matt

dmccrae

The hanging is likely to be because it needs to be in single-user mode to change the security model, and you have the database opened by some other session.

I can confirm that creating a new database will create an app role, so the restore route should work for you. I was looking for a quicker way!

Good luck.

mcurrie

Backup\restore worked.

Thanks guys!

ara3n

Great to hear.

DenSter

Thanks for following up, and good to hear you got it working . I am curious though to hear exactly how you did the initial upgrade. Did you go straight from a 4.01/SQL2000 to 5.0/SQL2005, or did you first convert the database on SQL2000 and then migrate to SQL2005? There's all sorts of ways to migrate, and it would be useful to hear exactly what steps you took.

mcurrie

For the SQL portion I simply detached a database from SQL2000 copied it to the SQL2005 server and re-attached it. I've had the SQL2005 server up and running for a while now and have used other 4.01 databases on it so that wasn't an issue. However, I believe the first time I attempted this same exercise I had to update the trace flag on the sql2005 installation and I also had to do the extended stored procedures.

As for the technical upgrade from 4.01 to 5.0 I followed these instructions which I copied from a post made by David Singleton on another forum.

- I create a backup of OBJECTS ONLY from your 4.00 database.
- Restore these objects into a new 5.00 database.
- Now export all table objects form this 5.00 database.
- Import all the 5.00 converted tables back into your 4.00 database.
- Close version 4 and open SQL Server Management Studio.
- Locate you 4.00 database, and open Table $ndo$dbproperty.
- Change databaseversionno from 60 to 80
- Now open your database in 5.00.

Hope this can help someone else. If anyone has any questions don't hesitate to PM me.

Thanks again

dmccrae

Changing the databaseversionno should only be done if you are absolutely sure that it won't compromise steps necessary during the database upgrade.

That is sometimes possible but in general, should be avoided.

In this case I believe it was the reason for the missing app role - the necessary security objects are created during the database upgrade step, dependent on the security model.

kriki

mcurrie wrote:

As for the technical upgrade from 4.01 to 5.0 I followed these instructions which I copied from a post made by David Singleton on another forum.

You could also give the link to the post on "another forum". We are not offended if you do this.