When defining a Jet Professional data source connection to a Dynamics GP database, it is necessary to provide the account credentials that will be used to create that connection:
Jet Professional does not utilize the Great Plains client but instead connects directly to the SQL server itself in order to retrieve information from the database. In order to have Jet Professional respect all of the permissions in Great Plains there are a few steps that need to be taken.
Configuring the SQL Permissions
The first step is to create a new SQL login for the Jet Professional user on the SQL server. Jet Reports cannot use the existing Great Plains login for the user due to the fact that the password that is entered in the Great Plains client is encrypted when it is sent to the SQL server. For example, if the password that the user types into the Great Plains 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 Great Plains) from which they are allowed to retrieve data.
In addition to requiring read access to these databases, Jet Professional also has a small set of tables to which it needs access. This list is below:
DYNAMICS Database (this is required for Jet Professional to work):
Note: 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):
If there is a specific company (“Fabrikam, Inc.”, for example) in Great Plains 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 Jet Professional 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 Jet Professional requires explicit read access to in order to function properly.