Nav4.0SP1/SQL2005 - Does not work

Microsoft confirmed: Navision 4.0 SP1 does NOT work on SQL 2005 when using permissions. Business Analytics, Business Notifications, Commerce Gateway, and Commerce Portal also do not currently work with SQL 2005. In a recent support discussion with Microsoft while trying to implement a customer on Navision 4.0 SP1 on Microsoft SQL Server 2005, they said that they have bugs with: - Database Logins - Windows Logins - Any C/AL calling .READPERMISSION (such as all the login and logout triggers in ApplicationManagement) This is (supposedly) not an issue if you aren’t using users or permissions. Also, in the process, I discovered that Navision’s finsql.exe also does not use the new SQL ODBC drivers, so in some rare cases, you will have to install the SQL 2000 drivers or an older version of MDAC to even get Navision to browse the server list. Send an email if you want the MS document detailing this. Informingly, Jeremy

Jeremy, could you put the document in the downloads section, it would be usefull to many of us I am sure. Thanks… UP load here…

Hmm, we have one customer, where we are using Navision 4.0 SP1 and MS SQL 2005 running for about 2 months now. Database and Windows Logins working fine, no other problems either. They are not using Business Analytics, Business Notifications, Commerce Gateway or Commerce Portal though. We are using build 21990 (you can get that from MBS, if you ask), maybe that helps?

Hi, I think there is incorrect information, or information is not complete. Can you be more detailed about it? Maybe you’re talking about x64 bit SQL 2005? There were minor problems with permissions but i doesn’t means Navision can’t work with SQL 2005. Regarding BN, BA and other additional products: there are too many differencies between SQL 2005 and 2000 in SQL analyst and notifications services, so products from Navision 4.0 can’t be just transfered to SQL 2005 - it must be another products. It will be delivered soon.

Jeremy et alia: I have communicated the same information that I am about to post here to Microsoft regarding this particular issue. We already have 3 customers live w/ SQL Server 2005 and Navision 4.0 SP1, and every single one of them is using Users and Permissions. I have attached the document that I created for Microsoft to this email. At the end of the day all that is required is the changing of permission on the application roles so that they have REFERENCE permissions granted to the entire database. More specific information is detailed in the attached document as to why. As for the build difference (21666 vs. 21990), it fails either way. 21990 was released because of the first key was not being set as a clustered index in SQL. Let me know if you have any questions…my information is attached to this post as well. Thanks! Scott Frappier
Attachment: SQL 2005 and Navision 4.0 ( 93504bytes )

Thank you for posting that Scott, I really appreciate your willingness to share. We got screenshots from that document to support some questions that we had as a result of the official announcement, and I didn’t think they had given us the whole thing. Those screenshots look like Vista screenshots. Did you make them from a Vista beta installation or do you have a theme that just looks like Vista? Did you test this on a regular Win2K3 and/or XP installation?

Daniel: I’m actually using WindowsBlinds 5.01 on my Windows 2003 Server. It works great, and keeps me satisified so that I do not install a new OS just for the looks. I have tested this on a Windows 2003, Windows XP, and Windows 2000 box and it seems to work w/o problems. All of our customers that are using SQL Server 2005 have this implemented for the time being, until Microsoft contacts me w/ an update on how the development team decided to address the issue. As for the thank you; no problem! I always like to help, and I’ve found the Navision community to usually be one of the most open bunch of people that I have ever met my entire life! It’s the least I can do.

Hey Scott, Thanks a ton. MBS support did get the messages, apparently, because I got your solution from them. Glad to hear there is SOME hope for this working out ok.

Hi, I have one more problem. I have created all users and granted REFERENCE permissions to the application role. I synchronized all users and everybody can connect now. I tried to change Expiration Date on one of the users. It was changed OK. Then I tried to log in and change it back but i can not change anything in the User table. The Message → “…Security system has not been synchronized successfully. User AAA does not exist on the SQL server” But the user exists and i can log in with this user name. Any Ideas???

For some reason I can’t donloa the attachment. Do I need special permission?

Hi Ahmed, yes there are some problems with downloads when they are created to attachments to a thread, instead of put int he down load area. I will try to fix it for you.

Ahmed, can you try again, and let me know if it works or not. Thanks.

Hello David Yes it works now. Thank you. Daniel sent me the this morning, allready. [:)]