Navision 4.0 Upgrade to SP1

afzal_baig

Hello there,

I have Navision base 4.0 using SQL 2000 with SP4. I got set of CDs from Microsoft for Navision client 4.0 with SP1.

I have tried upgrading the database by using SQL backup/restore and Navision option of Backup/Restore (within Navision application) and for both I get message confirming the upgrade successful. However, once upgrade I cannot login using Windows authentication. I have tried removing all users from SQL and re-synchronizing (all users) the security but could not get back in (I could only login using DB user - that is my backup user with "super" rights).

I have also tried removing and adding a test using within Navision (Tools / Security / Windows) but that also did not allow me login.

I appreciate if anyone else has the same problem or if any solution/suggestions?

Thank you.

ara3n

Have you setup SQL to authenticate in mixed mode? db as well as windows authentication?

I suggestion using sp3 client. The security enhancement they've added eases a lot of pain of synchronization.

afzal_baig

Thank you for your reply.

Yes, I have SQL server "Security" set as "SQL Server and Windows". Also, I have everything working perfect without SP1. I can try SP3 but I am not sure how would it affect with custom modifications, and some of these are major changes.

Thank you.

ara3n

You just need to get the client. Your objects will not be modified, so you customization won't be an issue.

afzal_baig

Thanks again for your quick reply. I have installed SP3 and upgrade completed successfully. However, I have the same problem with SP3 also where I could login using local DB user, but for Windows user(s) I am getting same authentication errors.

This time also, I have tried following but nothing would work:
1. Adding a new user (Windows)
2. Removing all users from SQL 2000 DB (using Enterprise Manager) and resync security. I could see users getting added very fast but would not allow access.

Much appreciate if you can give me any other idea or suggestion?

Thank you.
Best regards,
Afzal Baig

ara3n

what roles have you given the user in navision super?
Also what error do you get?

Also when you login as that user. in login window put the server name, login, pw. then do a lookup on Databasename. Can you select the user?

Also have you check any setting to allow only dbowner? to check this.
Click on file->database->alter->Options tab. IS member of db_downer checked?

It should be unchecked.

afzal_baig

Thanks again for your help.

I have local DB and Windows Authenticated users both with "super" role.
Please visit http://www.pesl.com/navsp3/ to see exact error messages or what I have in term of settings.

Yes, I can browse and pick the database by both "Windows Authenticated" user ID and local user (local user is the only thing which works).

Yes, I have given db_owner to my ID (Windows AD) but same error.

This time, I have even tested by removing all "application" type "roles" from SQL 2000 DB (using Enterprise Manager) and resync the security. I could see all roles and users being added back in SQL DB, however, unable to authenticate user ID.

Is there a parameter or setting inside Navision user setup which was not required by using Nav 4.0 without SP, but SP may require that?

ara3n

Just a thought, is the SQL server the same box as the domain controller?

Also try changing the security model to standard in Advance tab,

Is there a parameter or setting inside Navision user setup which was not required by using Nav 4.0 without SP, but SP may require that?

No there isn't. The user simply needs to have the public role.


Also did you try deleting all the windows users from Navision?
Tools->security->windows login-> delete them and add them again?

afzal_baig

In my test current setup, SQL DB/Server is on a seperate box, and is part of current domain (same as client). However, SQL server is not a domain controller (which I would not expect is required any how).

Client with SP3 is running on Windows XP Professional and also I tested it running using Windows 2003 server (same OS as test SQL setup).

I have tried setting SQL server security setting as "Windows" only but this also did not work.

I have tried removed all users (Windows authentication) from Navision, test SQL database and even under Server/Security; none would work.

Where can I find the option of "security model to standards", in "advanced tab"?

Thanks again.

ara3n

Where can I find the option of "security model to standards", in "advanced tab"?

File->Database->alter->advance tab->security Model. Change it to standard.


Afterwards synchronize security.
As far as windows users concerned, you need to add them in navision. and synchronize will add to sql server.

Also if you created the sql database as windows user, then you can't add your self. Create a new database as SA db login. Or change the ownership of the db to SA.

afzal_baig

Thank you and much appreciate your dedication and help.

Yes, by changing the security as "Standard", at least this time it is a different error message. Please visit http://www.pesl.com/navsp3/ to view the new error.

I have a feeling that since I have made so many changes to DB, I might have dropped this procedure by error. I am going to restore a fresh database and upgrade/convert to SP3; hoping that I am getting close. I will post back any progress I may see.

Kind regards,
Afzal

ara3n

you need to put the xp_ndo.dll into the binn folder. then run the following statement on server. the dll file is in the cd in sql folder. double click on the exe file and it will extract the dll file.


you need to copy the following code to query analyzer and run it. Change the directory to where the sql binn folder is. There is also a readme.txt file on cd for more info.

USE master

EXEC sp_addextendedproc xp_ndo_enumusergroups, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo.dll'

GO

 

GRANT EXECUTE 

ON [xp_ndo_enumusergroups]

TO PUBLIC

GO

 

USE master

EXEC sp_addextendedproc xp_ndo_enumusersids, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo.dll'

GO

 

GRANT EXECUTE 

ON [xp_ndo_enumusersids]

TO PUBLIC

GO

 

If you get an error that xp_ndo_enumusergroups already exists, then you need to write

Drop procedure xp_ndo_enumusergroups

Or

Drop Porcedure xp_ndo_enumusersids

afzal_baig

Thanks again.

The DLL file was already under that path and I needed to run "drop procedure" for both xp_ndo_enumusergroups and xp_ndo_enumusersids. However, even after adding these procedures, I could not connect back to SQL 2000 server and ended up reinstalling SQL2000 server with SQL SPs and restoring data backups. My mistake is that I did not take backup of master database before dropping the procedures.

I have my test environment working again without Nav SP but sorry to share my experience that SP3 upgrade is no go for me .....