Dynamics GP Security with Jet Reports

Overview

When defining a Jet Reports data source connection to a Dynamics GP database, it is necessary to provide the account credentials that will be used to create that connection:

The Jet Excel add-in does not utilize the Dynamics GP client but, instead, connects directly to the SQL server itself in order to retrieve information from the database.  In order to have the Jet add-in respect all of the permissions in Dynamics GP, there are a few steps that need to be taken.

Configure the SQL Permissions

1. The first step is to create a new SQL login for the Jet add-in user on the SQL server.  The Jet add-in cannot use the existing Dynamics GP login for the user due to the fact that the password that is entered in the Dynamics GP client is encrypted when it is sent to the SQL server.

   For example, if the password that the user types into the Dynamics GP client is “mypassword”, it will actually get sent to the server as something like “w72;#9bo^!”.

   After the new login has been created, security permissions must be assigned on the SQL server in order to restrict the user from accessing companies (which are stored as databases on the server) and tables that they are not allowed to access.

   The user will need to have read access to the DYNAMICS database on the server, in addition to having read access to any other database (each one representing a company in Dynamics GP) from which they are allowed to retrieve data.

2. In addition to requiring read access to these databases, the Jet add-in also has a small set of tables to which it needs access. This list is below:

   DYNAMICS Database (this is required for the Jet add-into work):

   • SY01500

   In recent versions of Dynamics GP, the DYNAMICS database can be given a customized name.

   Company Databases (these are required for the GL function to work):

   • GL10110
   • GL20000
   • GL10111
   • GL30000
   • GL00105
   • GL00100
   • GL00200
   • GL00201
   • SY40101
   • SY40100
   • GL00102
   • GL10000
   • GL10001

3. If there is a specific company (“Fabrikam, Inc.”, for example) in Dynamics GP that a user should not have access to, the security settings for that particular user would need to be modified on the SQL server to deny them access to the corresponding company database.

Since the Jet add-in queries the SQL server directly, all security is handled at the server level.  Due to the sensitive nature and complexity of security settings on the SQL server database, we cannot recommend specific settings that should be used.  The exception is with the tables above, to which the Jet add-in requires explicit read access to in order to function properly.