When configuring a Jet Reports data source connection for Microsoft Dynamics GP, you must provide SQL account credentials that allow the Jet Excel add-in to retrieve data directly from the SQL Server database. Unlike the Dynamics GP client, Jet Reports does not connect through the GP application layer. Instead, it communicates directly with SQL Server. To ensure Jet Reports respects the same permissions defined in Dynamics GP, specific SQL-level security configurations are required.

Configuring SQL Permissions

1. Create a Dedicated SQL Login

Jet Reports cannot use the standard Dynamics GP login because GP encrypts the user’s password before sending it to SQL Server.
For example, if the user enters “mypassword” in GP, SQL actually receives an encrypted string such as “w72;#9bo^!”.

Because of this, a separate SQL Login must be created specifically for the Jet Reports user.

2. Assign the Required Database Permissions

After creating the SQL login, permissions must be applied to ensure the user can access only the companies and data they are authorized to view.

Jet Reports requires read access to:

DYNAMICS Database (mandatory)

This is essential for the Jet add-in to function.
Required table:

• SY01500

Note: In newer Dynamics GP versions, the DYNAMICS database may have a customized name.

3. Company Database Permissions (for GL Functionality)

Each Dynamics GP company corresponds to a separate SQL database.
For Jet Reports to run GL functions, read access must be granted to the following tables within each authorized company database:

• GL10110

• GL20000

• GL10111

• GL30000

• GL00105

• GL00100

• GL00200

• GL00201

• SY40101

• SY40100

• GL00102

• GL10000

• GL10001

If a user should not access a specific GP company (e.g., Fabrikam, Inc.), you must explicitly deny access to that specific SQL database.

Security Handling

All security enforcement occurs at the SQL Server level since Jet Reports queries SQL directly.
Due to the sensitivity and complexity of SQL Server security, Jet Reports can only specify the minimum required permissions; the broader configuration should be managed by your database administrator according to internal policies.

If You Need Any Help, Contact us Now!