Sign Up for Training |
insightsoftware Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

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
dsstg_button.png dsstg_import_select.png

 

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:

  1. 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:

    sqlconnect1.png
    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.
  2. 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 Data Source Settings: 

    blobid0.png
     
  3. A SQL Server login that allows read access to the entire Dynamics NAV or Business Central 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:
    blobid1.png
  4. 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:

    sqlconnect3a.png
    sqlconnect4a.png

    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. 

  5. 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.

Was this article helpful?
0 out of 0 found this helpful

Comments