Securing ODBC connection to Great Plains backend 1

[jkendig65]

Great Plains Version 8
Microsoft Server 2003
SQL 2000

I am working for a company that is going through SOX compliance. The controller asked for ODBC access to Great Plains and indicated that he had update access. I setup an ODBC connection using SQL authentication and using my Great Plains credentials. I was given read only (supposedly) access but was able to make changes in MS Access via the ODBC connection. To confirm the access, the administrator setup a test account (not limited to read only) and I went into the test database and made changes. The DBA verified the change was accepted. What is the best and most efficient way to secure backend users ODBC connections to read only access for Great Plains? I believe most the users access ODBC with thier GP Account (SQL credentials). Most of these users would be connecting to ODBC for reporting purposes.

Thanks

 

[NEmacGuy]

I would go to your DBA.
I would have a few questions:
Using your GP Security Credentials ?????? I thought Great plains encrypts the users password so it only works for GP not for ODBC.
I normally setup a domain group for reporting users and give them read-only access. Consideration should be given as to which databases / tables / fields they will have access to. Just the work for a DBA.

 

[klewis10367]

I do know that scenario would not work in Version 9, but 8.0 I am not sure. You might want to consider upgrading. In 9.0 your Great Plains user password is encrypted, and if by chance unencrypted through Enterprise manager you would be forced to change it when logging into Great Plains again. One work around that you might want to consider is setting up your odbc connection with Windows Authentication and then adding the users through EM and give them read only to the tables.

 

[jkendig65]

We were also told that Great Plains encrypts the password and the user must change the password at least once to solve the problem (i.e. enable encryption), but I changed my password within GP and was still able to access the backend databases. We have considered Windows authentication and the network adminstrator is researching that route. I initially thought you control most ODBC connections by setting up a read only group and granting the group only read only access to the table, but I haven't used ODBC in a while and I'm currently trying to remediate this from an audit issue. I do appreciate any and all feedback. Thanks

 

[winthropdc]

Do you have Omni Password which is part of the Omni Tools suite?

Omni Password v8 has the option to remove the encryption from user's passwords to allow the same password to work from Crystal Reports and FRx, etc.

If you do, you can turn this option off and force all users to change the passwords again to re-enable the encryption.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer - Great Plains
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)

http://www.microsoft.com/Dynamics

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.