When defining a SQL Server data source connection to Dynamics NAV 2013 (or higher), the following error message is displayed:
Complete text from error message:
The data provider returned the following error:
Login failed for user 'DOMAIN\USER'.
The current Windows user does not have sufficient permissions to directly access the NAV SQL database.
In order for users to utilize the Dynamics NAV 2013 and later (SQL Server) data source with 'Windows Authentication (current user)' for the first authentication parameter in the Jet Reports Data Source Settings, the user must be added to the root security of the SQL Server Management Studio and mapped to the Dynamics NAV 2013-2018 database with db_datareader permission and a public role.
While this will resolve the error and make the connection possible, it is not the recommended configuration, as it will require that all users be added to your SQL Server security in order to connect. While Jet Reports will still restrict the users to only have access to the data to which they have permissions in Dynamics NAV, users could go outside of Jet Reports and obtain access to all of the data in the database (e.g. Excel > Data > Get Data > From Database > From SQL Server Database).
The recommended configuration for the ‘Dynamics NAV 2013 and later (SQL Server)’ data source is as follows.
Create a SQL Server database user with db_datareader access to the Dynamics NAV database and a public role.
This step requires that you enable SQL Server and Windows Authentication mode (SQL Server > Properties > Security)
In the Jet Data Source Settings, switch to SQL Server Authentication and enter the appropriate credentials:
If your organization utilizes the Jet Service Tier to distribute data sources to users then you must make the data source changes in the Jet Administration Console and edit the scopes for that data source as shown below: