Dynamics GP 2015 - ODBC Connection - user permissions

we recently upgraded to Dynamics GP 2015 -- no problems here.  

During the setup and upgrade of this, I found out that that all of our clients have ODBC connections to the server configured with SQL server credentials of sa!  Previous versions, the connection was created by the installer package, so it could have been this way for some time and I was never aware of it, but with this one, the method our partner used to set it up, I have to create the ODBC connection myself.

Now being a security oriented person, this makes make CRINGE and I need to change this.

All of my GP users are in an AD security group already, the server is setup for mixed mode authentication, I just need to know what roles or database permissions I need to give the group so that GP can perform any/all necessary actions it needs to.  While I am pretty sure I could do this by giving them db_owner, that seems overkill but it would probably work so I thought I would ask here.

Each GP user has access to each company, so that is not really a concern.  I recognize that security within the application is controlled / monitored / handled by the GP application.  My users do not have admin privileges on their machines so them maintaining their credentials for the ODBC connection is not practical so at least for the ODBC portion, I'd like to have that part authenticate with their windows info.

I don't think they would need access to the management report databases though, this would be more governed by the management reporter service running and the MR client connects to that service is the way it looks.

Not sure what to do about the Dynamics database either.

Any suggestions or best practices on this someone can refer me to?   Even if a direct link within customer source.  I could not find but I am not good at finding things on customer source.  I did ping my partner again and they told me they just set all the ODBC up as "sa" becuase "its easier and it works".  But that puts me in a very weird bind from a security perspective.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
When the ODBC connection is created on each computer the SQL credentials supplied are only used to connect to the SQL server at that one time to confirm the connection and settings. This is actually optional and you could set up the ODBC connection with no SQL login provided, you simply would not be able to test it. If SQL credentials are provided at the time of creating the ODBC, these are not stored anywhere within the ODBC connection or the local computer.

When that ODBC connection is subsequently used by the Dynamics GP application to connect to the SQL server it uses the permissions of the GP user specified at that time.

When a new user is created inside the Dynamics GP application that automatically creates a new SQL user with the DYNGRP role. As you mentioned, all GP security is controlled by the application. One other important thing to understand is that when the new SQL user is created by GP the password is encrypted and can only be unencrypted by the GP application. So even if that SQL user has access to absolutely everything, that user login cannot be used outside of the Dynamics GP application because of the encrypted password.

There is no need for you to be changing or controlling the security for GP users directly in SQL server. All the security should be controlled in the Dynamics GP application. If you are concerned about security in there, you may want to get some help from your Dynamics GP partner to go through the user permissions and make sure that setup windows are secured.

Hope that helps address your concerns.

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
LappiMAAuthor Commented:
Hi Victoria -

thank you for the response!   I'm not concerned about security within the GP application itself -- that is good to go and is largely maintained by our accounting department.  My role is IT / System administration and the usage of sa at setup made me concerned.  Your explanation seems likely and makes sense although I have not tested since you posted but I will later today (I guess I could have done this without posting by checking user activity on SQL server).  

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.