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
|Essentials 2013 Update 1 - 2015 Update 1 (original)|
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 and Jet Essentials version 15.1 also includes a similar Connection Method for Dynamics NAV 2013 and later - Remote Data Service - which allows for access to your NAV database when it resides on a different network domain than does your installation of Jet Professional (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:
Note: Versions of Jet Essentials 2013 through 2015 require that NAV be configured for Windows Authentication. Use of NAV User/Password Authentication requires Jet Professional or Jet Essentials 2015 Update 1.
- 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.
Please 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:
For this step, 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 it is located in the SQL Server master database (not the NAV database) in the "$ndo$srvproperty" table. Users must have read access to this table in order for Jet Professional to verify the NAV license.