Basic SQL - Creating Extended Stored Procedure / xp_ndo.dll

  • Article

This post is part of "Overview of NAV-specific SQL features for application consultants". 

 

Microsoft Dynamics NAV requires two extended stored procedures from xp_ndo.dll to exist on SQL Server if Windows logins are used. If these extended stored procedures do not exist, you will get this error when trying to log on using Windows Authentication:

---
The extended stored procedure xp_ndo_enumusersids in the library file xp_ndo.dll, is not available on the LOHNDORF1 server.  Until this procedure and library have been added, it will not be possible to connect to this server from Microsoft Dynamics NAV with Windows Authentication, but you will still be able to connect with Database Server Authentication.  You can read more about adding this extended stored procedure in the help pages on the product CD. Follow the hyperlink to the readme.txt file on the Servers page under the Documentation section of SQL Server.

---

XP_Ndo

When this happens you have two options: Use Database Authentication instead, or create the extended stored procedures on the SQL Server. a normal installation of a NAV client will create these procedures automatically, which is why in most cases you don’t need to do it. But if for some reason it was not created, then this is how to do it manually:

1)    From the product DVD, open the folder sql_esp, and run the file xp_ndo.exe to extract the file xp_ndo.dll somewhere you your disk. Then copy it into this folder on your SQL Server (on SQL 2008): C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. Remember, copying files into the “Program Files”-folder requires administrative privileges on Vista and Windows2008, which is why you should extract the file to a different folder first, and then copy it into this location.

2)     Open SQL Server Management Studio. Extend Databases -> System Databases -> master -> Programmability. Right click on “Extended stored procedures”, and select “New extended stored procedure”.

3)    Name it xp_ndo_enumusersids, and in the DLL field, select the full file and path for xp_ndo.dll (on SQL Server 2008 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\xp_ndo.dll):

CreateXP_Ndo

4)     Assign permissions to the public role: In the Extended Stored Procedures properties, click on “Permissions”, then “Search”. Type in “Public”, and click OK. Then assign Execute permissions to Public:

AssignPermissions

5)     Finally, repeat the previous steps and make another extended stored procedure which is exactly identical, except this one must be called xp_ndo_enumusergroups instead of xp_ndo_enumusersids.

You should now have two extended stored procedures, and should be able to use Windows login from NAV.

 

 

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Comments

  • Anonymous
    November 05, 2008
    PingBack from http://blogs.msdn.com/nav_developer/archive/2008/11/05/basic-sql-overview-of-nav-specific-sql-features-for-application-consultants.aspx

  • Anonymous
    November 05, 2008
    As the title says, this post is not really for NAV developers, but for application consultants who don't

  • Anonymous
    November 05, 2008
    As the title says, this post is not really for NAV developers, but for application consultants who don't

  • Anonymous
    May 04, 2010
    Worked on Windows Server 2008 x64 note: .dll for x64 is "xp_ndo_x64.dll". I also added NETWORK SERVICE permissions to the .dll location.

    • Anonymous
      March 15, 2017
      I, too, discovered that I had to use the 64-bit version of the dll file. It worked great! Thank you for posting this information.

  • Anonymous
    November 05, 2010
    Visual Studio and SQL Server management Studio are two different entities. So it takes a lot of time when we try an interaction with the database from Visual Studio then it takes a lot of time. When we are using desktop applications then we do not realize this time delay, but while using web applications the time delay becomes an important factor because the database is on the remote server so a huge time delay is observed. Normally, the query that we write in Visual Studio is first compiled and then executed in Sql Server which then returns the results. So, this is why a time delay is observed. The answer to this is Stored Procedure usage. The Stored Procedure is stored in compiled format in SQL server so when we call it from Visual Studio application then it is just executed. Thus saving a lot of time. An illustration of proper usage of STORED PROCEDURE has been displayed in the following video. You may see it: www.visiontechno.net/.../storeprocedure.html

  • Anonymous
    December 22, 2010
    Hi Lars A star as always - do you ever get anything wrong!  Hope you enjoy your Christmas - you returning back home as per usual? Have a good one David

  • Anonymous
    March 29, 2011
    in a cluster installation it doesn't work cuz SQL Server doesn´t have access to c: only have access to a share unit. What is the correct solution in that case? adding the shere unit to windows path? copying the xp_ndo.dll to installation directory in all nodes and copying the address manually not using browse button?

  • Anonymous
    May 29, 2013
    @Reynier, repeat the process on all instances that make up the cluster.

  • Anonymous
    June 05, 2013
    Thanks, it worked for me too on SQL SERVER 2008 R2 32-bit. But there was no xp_ndo.exe file in my DVD. I got the xp_ndo.dll file itself in SQLDatabasePFilesMicrosoft Dynamics NAV60Database directory in the product DVD. (NAV 2009 R2)