How can we set the ODBC as the actual user of Microsoft Dynamics Great Plains

We setup all our ODBC as always.  We use SQL authentication and “sa” username/passwordto set up the ODBC.  We want the ODBC to have either the user’s actual SQL username or windows authentication username.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
We understand that to create ODBC correctly we have to use SQL “sa” credential.  But what we want is to setup ODBC with actual username name assign in GP not “sa”.  We want this because we found a low-level user using the ODBC via excel to list payroll data.

Please advice.
Hi Ramante, I didn't understand your question. But I can tell you that any SQL user can be used while creating the ODBC connection to the respective SQL server. You cannot use GP users for this purpose because their real passwords are not known (assigned and encrypted by GP).
rayluvsAuthor Commented:
When we started installing GP some years back, we were told to always use 'SA' to create the ODBC.

Since  'SA' has full access to GP DB, a point was risen in security; can a user set to only view Receivables in GP have access to Payroll infor if he uses ODBC (which has  'SA' as a user) in an Excel or any other apps that uses ODBC.

Hope we gave info to state our concern.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

No dear, creating the SA doesn't decide the connection's privileges. The user you use when creating the ODBC connection has nothing to do after the creation. The privileges are decided when you use the created ODBC connection, the privileges of the user you're using to log-in to the SQL server via this ODBC connection will apply.

Finally, create a user who has access to the Recevables tables (RMXXXXX) (create the user in SQL not GP), and this user can be used from Excel  using any existing ODBC connection.
I meant: replace "creating the SA" with "creating the ODBC connection using SA account".
rayluvsAuthor Commented:
Please excuse the continual question on the topic, but I'm reading your answer and somehow I'm not understanding.

Let me ask it this way:  

If I create a user in GP, let say 'JSMITH' and give it access XYZ Company DB and also give it access only to RM module:

   - When creating the ODBC for the user, should I created with the user name I created
     in GP?

   - If thats is true and I have to create the ODBC with that user name, can this user
     use the ODBC with any other apps, like Excel, to access the tables other that RM?

   - In order to created the ODBC for that specific user, should I first create it in SQL,
     then in GP?

Please clear this for us, our main concerne is security.
-  You cannot create the ODBC using the user you created in GP because the password you assigned it to him in GP is different than the SQL password GP assigns to him.

-  Yes, the same ODBC connection that was created for GP can be used for anything else.

-  You can create the ODBC connection using any SQL user, but not using GP users (because their passwords are not know to you. Here's how:

a- Create ODBC connection using sa

b- Create a user in SQL for the user you need to give him access to the receivables and give him access to RMXXXX tables; (Rec_User for example_

c- Give the user his credentials (Username: Rec_user and the Password) and let him use it to access the RM tables from Excel.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Just tested in GP and found that the user created in GP is also created in SQL.

Just to understand, when you say  'Create a user in SQL for the user ', you mean create a new name? That is, if I created JSMITH in GP, create another like JohnSmith?

Then use that name 'JohnSmith' for the new ODBC?
Yes, create JohnSmith in addition to GP's one. You can use JohnSmith or any other users (like SA) to create the ODBC, but use JohnSmith to access RMXXXX tables in Excel.

Yes, GP creates the user in SQL, but with different password.
rayluvsAuthor Commented:
I think I am giving the wrong impression.  I don't want to make understand that what i want user JohnSmith is to have access GP tables in Excel, all the contrary, I don't want JohnSmith, who have access to RM, use a ODBC and via Excel have access to PR tables.

If you restrict JohnSmith to RM tables in SQL, he won't be able to access the PR tables in Excel, even if he used an ODBC connection that was created using SA account.
rayluvsAuthor Commented:
Reading all your support, somehow we think we've been seeing the security issue with ODBC all wrong.

To fully understand the HOW-TO setup an ODBC and at the same setup proper access security, the following steps should be followed:

1. Create a GP user 'JSMITH'
2. Give 'JSMITH' access within GP to the desired Companines
3. Setup 'JSMITH' within GP to only access the module RM, nothing els
4. Then goto SQL and create a user for ''JSMITH' such as 'JohnSmith'
5. Go into SQL and restrict 'JohnSmith' to only access RM tables
6. Create the ODBC with the SQL username 'JohnSmith'
7. Finally, have the user enter GP with user 'JSMITH'

Please advice.
1. Create a GP user 'JSMITH' // This is the GP user for John Smith
2. Give 'JSMITH' access within GP to the desired Companines // Ok
3. Setup 'JSMITH' within GP to only access the module RM, nothing els // This is up to you
4. Then goto SQL and create a user for ''JSMITH' such as 'JohnSmith' // This is the SQL user which will be used by any application other than GP
5. Go into SQL and restrict 'JohnSmith' to only access RM tables // Yes
6. Create the ODBC with the SQL username 'JohnSmith' // Ok
7. Finally, have the user enter GP with user 'JSMITH' // Ok

But what are you gonna do with JohnSmith account?
rayluvsAuthor Commented:
Ok now I see.  I think I am sending you the message the WE WANT a user to use another application like Excel to connect to GP Db via the ODBC.

Let me clarify.  We have no intention or plans to setup an OBDC or any other applications to connect to the SQL instance for a user to access the GP Companies data with apps other than the actual GP application.  

Our mention in our initional entry of using ODBC & Excel was a comment, that we caught a user doing this.  We have told all users not to access GP data no other way other than via GP application itself.

Since we can't be next to the user at all times, we want if the user does use ODBC with another apps, he/she can't access other modules that wasnt setup for them in GP Secuiryt.

In conclusion, we only want GP Companies database to be access only by GP application.

This being said, can we safely say the the following steps in creating the ODBC will only give access to the user only to area setup for he/she in GP Security?

    1. Create a GP user 'JSMITH'
    2. Give 'JSMITH' access within GP to the desired Companies
    3. Setup 'JSMITH' within GP to only access the module RM, nothing else
    4. Create the ODBC with the SQL username 'JSMITH'
    5. Finally, have the user enter GP with user 'JSMITH'

       By setting ODBC like this if the user DOES goes an use ODBC, he will only be able to see
       the RM tables?
The ODBC connection itself does not give any access to the data without a user and password.

    1. Create a GP user 'JSMITH' // Ok
    2. Give 'JSMITH' access within GP to the desired Companies  // Ok
    3. Setup 'JSMITH' within GP to only access the module RM, nothing else // Ok
    4. Create the ODBC with the SQL username 'JSMITH'  // You cannot, because you don't know the password (it's not the same password you put in GP). Use sa here or any other SQL user.
    5. Finally, have the user enter GP with user 'JSMITH // Ok
Don't worry dear, when you create the ODBC connection in the client's machine using sa, you're not giving any access to user to GP data. Once the ODBC connection wizard finishes, the sa credentials will be deleted, they're used in the wizard just to test the connection and fetch the settings.
rayluvsAuthor Commented:
Ok, so it's safe to create all ODBC connections with 'SA'.  Then its safe to continue setting up ODBC as always done.

But going back on your comment "// You cannot, because you don't know the password" on ID: 37306998, if we create the user in GP and assign the password ourselves, we know password.  Still we should use that user name for ODBC creation?

Please explain.
Suppose you created a JSmith in GP with a password of "123", GP is going to create an SQL user JSmith but with a secret password in order to protect the data, because if "123" is the SQL password, the user wold be having access to GP tables and he may mess with them using some other application. So, you won't be able to know JSmith real SQL password.
rayluvsAuthor Commented:
When you say "a secret password in order to protect the data", you mean the password SQL assign is different that one entered in GP when creating JSmith ?
Victoria YudinOwner / Dynamics GP ConsultantCommented:
Just to clarify slightly...when you create a user (or change a user password) inside GP, the password entered is encrypted by the GP application using not just the user ID and password entered, but also the ODBC settings on the computer where you are logged into GP at the time.  This encrypted password is what's stored in SQL for the user and without being unencrypted by the GP application, there is no way to use this user/password combination from anywhere else.  

You can see more more information on that here: 
rayluvsAuthor Commented:
Thanx Lots!
rayluvsAuthor Commented:
To close the question and have everything in our end, the proper procedure for ODBC creation for user  John Smith who will only have access to RM module is the following:

    1. Create a GP user 'JSMITH' for user John Smith
    2. Give 'JSMITH' access within GP to the desired Companies
    3. Setup security for 'JSMITH' within GP to only access the module RM
    4. Finally, create the ODBC with the SQL username 'SA'
    5. Have the user  'JSMITH' enter GP and start working

With these steps we can be at ease that John Smith will not have access to unauthorized SQL tables if he uses the ODBC created for this GP instance?

Our main concern with this issue is security access (see ID: 37304442).  We caught a user trying to
access GP Payroll table using an ODBC created for a GP user.  The user had his Excel an a started connection to that ODBC.
Yes, the above steps are healthy and correct.

No user will be able to access any SQL tables unless he knows an SQL username and password that has access to tables. The ODBC connection itself has no use without a username and password. And as stated previously, GP username and password won't be usable outside GP.
rayluvsAuthor Commented:
Thanx all!
rayluvsAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.