Jet Professional and Jet Essentials (version 14.1 and higher) include a data source type for Dynamics NAV 2013 and later (SQL Server).
|Jet Professional (and Jet Essentials 2015 Update 1)||Jet Essentials 2013 Update 1|
For organizations and users with direct access to the local SQL Server on which their NAV 2013 (or later) database is stored, the "SQL Server" type of data source can provide substantially faster reporting access than is traditionally possible through NAV's Web Services.
Remote Data Sources
Jet Professional also includes a similar Connection Method for Dynamics NAV 2013 and later - Jet Remote Data Service - which allows for access to your NAV database when it resides on a different network domain than does your installation of the Jet Excel add-in (system administrators can review the admin guide for this data source type for more information).
In order to be able to test and use the Dynamics NAV 2013 SQL Server data source type, the following is required:
The user must be using NAV User/Password Authentication or be a Windows user (or be a member of an ADO group using Windows authentication) in the NAV database and have a reasonable set of permissions (the user does not need to be a SUPER user, but will need sufficient permissions to read data). This is what will be used to determine the user's "Read" permissions in NAV:
Remote connectivity that allows direct access to the NAV database on the SQL Server must be in place. The SQL Server and Database name will be specified in the Jet Professional Data Source Settings:
A SQL Server login that allows read access to the entire NAV database.Note: This login does *not* have to be the same Windows user that is described in Requirement #1. You have the option to use the credentials for (a) the current Windows user, (b) another Windows user, or (c) a separate SQL Server user:
Whichever credentials are used (and it is possible for all Jet users to utilize the same credentials for this setting - i.e., a "proxy" user set up specifically for this purpose), the login needs only the public server role and the db_datareader database role:
It is also possible to assign a Windows user the same permissions - if you are not using Mixed Mode authentication on the SQL Server. Again, this does *not* have to be the same Windows user that is described in Requirement #1.
If the NAV license has been uploaded on a SQL server-wide basis (which is the default) then the license is located in the $ndo$srvproperty table of the SQL Server master database (not in the NAV database). Users must have read access to this table in order for Jet Professional to verify the NAV license.