Test Connection works but formula returns error

Questions and Answers asked by users to users, about how others use Jet Express.

Test Connection works but formula returns error

Postby shameil » Fri Nov 04, 2016 9:23 am

Using JetExpress, the Test Connection appears to work (417ms) and the Data Source Settings look normal for Dynamics NAV 2013 and later (SQL Server)

But when I write a simple GL formula it results in an #VALUE! error.

On debugging, the first two lines read as follows.
An unexpected error has occurred:
Login failed for user 'DOMAIN\username'

Any pointers would be much appreciated!

Thanks
shameil
 
Posts: 4
Joined: Fri Nov 04, 2016 9:07 am

Re: Test Connection works but formula returns error

Postby HPDeskJet » Fri Nov 04, 2016 9:37 am

What Authentication type are you using in your Data Source Settings for connecting to the NAV database?
auth_type.png
auth_type.png (23.74 KiB) Viewed 2250 times
HPDeskJet
 
Posts: 171
Joined: Tue Sep 06, 2011 9:20 am

Re: Test Connection works but formula returns error

Postby shameil » Mon Nov 07, 2016 7:02 am

Hi
I am using "Windows authentication (other user)".
Our IT Dept have created a user called "navadmin" and this is specified as the username.
The account whose NAV roles and permissions are used is "Windows authentication (current user)".
Hope this helps to pinpoint the problem.
shameil
 
Posts: 4
Joined: Fri Nov 04, 2016 9:07 am

Re: Test Connection works but formula returns error

Postby MWilson » Mon Nov 07, 2016 9:53 am

Hello Shameil,

Can you verify that the user account you are specifying after choosing "Windows Authentication (other user)" has db_datareader access to the NAV database on the SQL Server?
db_datareader.PNG
db_datareader.PNG (48.49 KiB) Viewed 2245 times


Best regards,
MWilson
MWilson
 
Posts: 157
Joined: Mon Mar 31, 2014 8:37 am

Re: Test Connection works but formula returns error

Postby shameil » Tue Nov 08, 2016 2:11 am

Hi, yes the navadmin user does have db_datareader access to the database that I am trying to connect to.
shameil
 
Posts: 4
Joined: Fri Nov 04, 2016 9:07 am

Re: Test Connection works but formula returns error

Postby MWilson » Tue Nov 08, 2016 9:20 am

Hello Shameil,

Can you attach a screenshot of the Authentication tab of the Data Source Settings and a screenshot of how the user is configured in SQL Server Management Studio?

Best regards,
MWilson
MWilson
 
Posts: 157
Joined: Mon Mar 31, 2014 8:37 am

Re: Test Connection works but formula returns error

Postby shameil » Fri Nov 11, 2016 7:18 am

Hi MWilson

Apologies for the delayed response. Screenshots are attached of the Authentication settings and the connection account (navadmin).
When we kept getting the #VALUES! error, we experimented with adding the current user to SQL Server and this seemed to solve the problem - screenshot attached. However, our IT Dept is not keen to add all JetReport designers to SQL Server - is there a recommended way to configure JetReports/SQL Server?
Also, Jetreports is running very slowly but that's a topic for another day...

Many thanks for your help
Attachments
current user.JPG
current user.JPG (67.62 KiB) Viewed 2213 times
navadmin user.JPG
navadmin user.JPG (73.41 KiB) Viewed 2213 times
Authentication tab.JPG
Authentication tab.JPG (62.89 KiB) Viewed 2213 times
shameil
 
Posts: 4
Joined: Fri Nov 04, 2016 9:07 am

Re: Test Connection works but formula returns error

Postby MWilson » Fri Nov 11, 2016 2:34 pm

Hello Shameil,

What I would recommend is to check if your SQL Server is set for 'Mixed Mode Authentication' (SQL Server and Windows Authentication).
If it is, create a single SQL Server user (not a Windows AD user) in the root security of SSMS and give that user db_datareader permissions to the NAV database.
Open Windows Explorer type %APPDATA% in the address bar and press enter (this brings you to C:\Users\Username\AppData\Roaming).
Delete the 'Jet Reports' folder.
In the Jet Data Source Settings choose your data source, switch to 'SQL Server Authentication', add the credentials and test your connection.


Best regards,
MWilson
MWilson
 
Posts: 157
Joined: Mon Mar 31, 2014 8:37 am


Return to Ask the Community

Who is online

Users browsing this forum: No registered users and 1 guest