NAS throws error: 515,"23000",[Microsoft][ODBC SQL

AndreasD

Hello,
I have installed Navision 4.0SP3.
The client works without problems.

Now I installed the corresponding Navision Application Server.
Then I created a NAS service (middle tier).
The account used to authenticate the service has all rights in SQLServer (sysadmin) and in Navision. It has the super role in general and for the company both for a windows and a database account and the database account has been given all rights with the super template.

Whenever I start the service, it creates the following error message in the Application event log of the Win2k3 Server:

The following SQL Server error(s) occurred:

515,"23000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'name', table 'tempdb.dbo.#$ndo$groups

How can I correct this?
Nowhere can I find such a table, neither in this nor in another db.

Thank you for any hints and support.

kine

you cannot find this table because it is temporary table created just for some short period and is accessible just only with the creating process (you can see that it is in tempdb and because there is # at the beginning of the table name, it is local temp table). It seems that NAV is trying to enumerate all groups which is the account member of and during that there is some group which has no name (or is not existing anymore). Check that the used account is not member of nonexisting group and that all groups in your Active Directory which is the user member of have names.

AndreasD

Thank you Kamil,

However, the user is a local user on the server.
The server has a parallel installation of Navision 3.70B and Navision 4.0SP3.
The same user is used for about 8 NAS services of Navision 3.70 - with no problems whatsoever.

If the problem were the group membership in a non-existing group it should have occurred much sooner.
How could I check to which groups the NAS-user account belongs?

Perhaps the problem lies somewhere else, with the NAS installation?

AndreasD

Hello,

now I updated the xp_ndo.dll in SQL-Server 2000, deleted the two existing extened stored procedures in the master db:
xp_ndo_enumusergroups
xp_ndo_enumusersids

and created them again.

I also unregistered the communication components in
C:\Program Files\Common Files\NAVISION\COMMUNICATION COMPONENT
Then I updated them, so they are all now from February 22, 2007.
Then I registered them again.

This all was to no avail. The same error still exist.

Does anybody have another idea how to solve this issue?

kine

Which security model you are using for the 4.0SP3 DB? I assume that you have Extended security model there... try to switch it to standard...

Grant

We just encountered and resolved a similar error for an upcoming 3.6 to 5.0 conversion. It turned out that the version of xp_ndo.dll that was being used, was the culprint. This error went away as soon as we used the 5.0 version instead of the 3.6 version. The DB we were trying to connect to when we got the error was a 5.0 DB that was restored using SQL.

AndreasD

Hi,

I tried that.
renamed the current xp_ndo.dll, copied in the one from the folder sql_esp from 40SP3, deleted the two ext stored procs and created them again with QueryAnalyzer:

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

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

GRANT EXECUTE
ON [xp_ndo_enumusergroups]
TO PUBLIC
GO

GRANT EXECUTE
ON [xp_ndo_enumusersids]
TO PUBLIC
GO

The error message when starting the NAS is just the same.
Did you do anything different? Did I overlook anything?

Can't s.o. from Microsoft answer this?
It should be very much in their own interest.

Grant

I know it sounds strange, but the behaviour we saw before resolving the problem by using the proper xp_ndo.dll, was that we got this error when opening up a SQL DB using a client on the same server as the SQL DB, but did not get the error when opening up a SQL DB using a client from a different server. I know this doesn't help resolve your issue, but maybe it will help in the troubleshooting process? Starting to look more & more like you'll need assistance from Microsoft.

AndreasD

Thank you Grant,

finally I installed NAS 40SP3 on another machine, after I realized that I did not have to install SQLServer there too.
The NAS 40SP3 on the other machine startet without problems. Here too I had a parallel installation of nas 3.70 and 40SP3.

So the problem on the first server will remain a mystery, I guess.

kriki

[Topic moved from Navision forum to SQL General forum]

AndreasD

Hello,
I kept on changing things and trying this and that. In the end the problem disappeared, now the NAS runs. I do not know exactly what solved the problem.
Of what I recall I did this:

• Re-Registered the DLLs of the NAS one by one • Updated the xp_ndo.dll in the folder …\binn of the SQL-Server with the one of NAS 40SP3 • Deleted and renewed the Extended Stored Procedures based on the xp_ndo.dll (see other entry this thread) • Restarted the SQL-Server-services (next time this would be the first thing I do) If need be I would even restart the server first • Uninstalled and Re-Installed the Security Enhancements of IE in the Win2k3-Server • Installed and started the NAS on another server • Changed the NAS user from a local to a domain account • Started the Navision Application Server Manager and had the respective NAS displayed in it; then stopped, started and applied all settings of the NAS • Logged the NAS user out of another Terminal Server session)

It is rather unfortunate that I cannot pinpoint the solution here but anyway, it works now.

Thank you for your contributions.