Requirements for using the Dynamics NAV and Business Central SQL Connector
Moderate
Overview
Jet Reports (version 14.1 and higher) includes data source types for Dynamics NAV 2013-2018 (SQL Server) and Dynamics 365 Business Central on-premise (SQL Server)
| Jet Reports 2015 Update 1 and higher | Jet Essentials 2013 Update 1 |
|---|---|
For organizations and users with direct access to the local SQL Server on which their Dynamics NAV 2013-2018 or Business Central database is stored, the "SQL Server" type of data source can provide substantially faster reporting access than is traditionally possible through Web Services.
Remote Data Sources
Jet also includes a similar Connection Method for Dynamics NAV 2013 and later - Jet Remote Data Service - which allows for access to your Dynamics 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).
Requirements
In order to be able to test and use the Dynamics NAV 2013-2018 or Business Central 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 Dynamics NAV or Business Central 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:
Versions of Jet Essentials 2013 through 2015 require that Dynamics NAV be configured for Windows Authentication. Use of NAV User Password Authentication requires Jet Reports 2015 Update 1 or higher.
Remote connectivity that allows direct access to the database on the SQL Server must be in place. The SQL Server and Database name will be specified in the Jet ribbon > Settings > Data Source Settings > Connection tab:
-
Navigate to Authentication tab.
A SQL Server login that allows read access to the entire Dynamics NAV or Business Central database.
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 Dynamics NAV database). Users must have read access to this table in order for Jet Reports to verify the Dynamics NAV license.