Open Forum

Expand all | Collapse all

TIP: Using a non "sa" account to run GP utilities

  • 1.  TIP: Using a non "sa" account to run GP utilities

    TOP CONTRIBUTOR
    Posted Sep 06, 2018 01:15 AM
    Edited by Rob Klaproth Sep 06, 2018 01:16 AM

    Many people have often asked, do I need to provide the "sa" account to my GP administrator to run GP Utilities?  In the past, it has always been thought for that to be the case, but fortunately, with some testing, I have discovered it is not true at all!

    However, it does involve giving your GP admin "sysadmin" permissions in the fixed server role of SQL.  So, while they are not the actual "sa" user, they do need to have "sysadmin" permissions as a fixed server role.

    Q: What can I do without sa if I create a user and give them sysadmin?
    A: Why everything of course!  That user can create a company, they can upgrade or apply a service pack, pretty much anything the "sa" user can do in utilities.

    Q: Should that user I create be a GP user or a SQL User?

    A: Good question!  If you just want them to run utilities to upgrade GP or create a company, they DO NOT need to be a GP User!  (Shocked me when I tested!).  However, if you want them to be able to login to GP and be an admin INSIDE of GP, they would need to first be created as a user in GP, and then granted the "sysadmin" fixed server role.

    Q: Why do they need "sysadmin" as a fixed server role

    A: If you want them to be able to create a company using GP Utilities, this requires the "sysadmin" role in SQL server to be able to create a database in SQL

    Q: What if I just make them "dbo" in DYNAMICS and all the GP databases?

    A: They would not be able to create a new company, sysadmin is required for that.  They MAY be able to upgrade or apply a service pack, since DBO technically has permissions to drop and re-create objects in a database - which is what GP needs when it's doing an upgrade.  HOWEVER, when you apply a service pack or upgrade to a new GP version, often times GP Utilities will create, or re-create assemblies (CLR) - and this may not be able to be done without the "sysadmin" fixed server role.

    Q: What about Management Reporter

    A: It is possible to use a non-sa account there as well - however, if you are creating the datamart, the user would need to have the "sysadmin" fixed server role because it needs to be able to create the database for the datamart

    Q: What about administering GP, i.e. creating new users
    A: It is possible to do this without "sa", by giving the user "sysadmin" permissions in the fixed server role.  If you do not wish to do this, FastPath has a document outlining specific permissions needed without giving "sysadmin":
    Fastpath White Paper - Minimizing the use of 'sa' in Microsoft Dynamics GP

    Gofastpath remove preview
    Fastpath White Paper - Minimizing the use of 'sa' in Microsoft Dynamics GP
    Des Moines, IA - Fastpath has published a white paper that demonstrates how to mitigate the risk of the 'sa' user in Microsoft Dynamics GP. Out of the box, Microsoft Dynamics GP creates the 'sa' user with full privileges to create, modify and delete any and all data within the SQL database and the GP application.
    View this on Gofastpath >





    ------------------------------
    Rob Klaproth
    Dynamics Certified Professional
    (GP Install & Configure)
    Sr. GP Consultant
    Armanino, LLP
    San Diego, CA
    ------------------------------
    GPUG Summit - Post


  • 2.  RE: TIP: Using a non "sa" account to run GP utilities

    GPUG ALL STAR
    Posted Sep 06, 2018 09:36 PM
    ​Interesting timing on this post, Rob! I just blogged about something very similar last week.

    https://jenkuntz.ca/2018/08/gp-utilities-user-permissions/

    I was coming from an angle of workstation installs and the requirement to log into GP Utilities and GP, for first run "get SQL messages".

    Jen

    ------------------------------
    Jen Kuntz, CPA, CGA, Microsoft MVP (Business Applications)
    Manager, Business Solutions
    Energy+ Inc.
    Cambridge, ON, Canada
    ------------------------------

    GPUG Summit - Post


  • 3.  RE: TIP: Using a non "sa" account to run GP utilities

    GOLD CONTRIBUTOR
    Posted Sep 07, 2018 08:29 AM
    To add to this, while what you say is true, when you give a user or login the 'sysadmin' role in SQL, you are also giving them all permission on the SQL side, meaning they can run any type of script they want, create or remove databases, create or remove users, add or remove permissions to other user, they are basically 'all-powerful' on the SQL side, so that is really the reason why we state that 'sa' is required to run Utilities because A) only the 'sa' login, by default, has all the permissions on the SQL side that GP Utilities requires in order to do upgrades, create new databases, etc., and B) on the security side of things, you probably don't want multiple users running around with sysadmin permissions.

    This is also why we created the DYNSA login, which has SQL permissions in order to create users, and many of the things that 'sa' can, but doesn't have 'sysadmin' permissions, so there are some things it cannot do.

    In the Documentation folder in each install of Dynamics GP, there is a SecurityPlanning.pdf file which has a chapter on 'Core application security tasks' also, that gives options on giving non-sa users permissions to do things like create and delete users, give users access to GP company databases, backup and restore databases, business alerts, SQL maintenance, and more.

    Just wanted to add this information, as normally we don't recommend giving users sysadmin permissions as it gives that user maximum permissions on both the GP and SQL side.

    Thanks

    ------------------------------
    Derek Albaugh
    Sr. Support Engineer
    Microsoft
    Moorhead MN
    ------------------------------

    GPUG Summit - Post


  • 4.  RE: TIP: Using a non "sa" account to run GP utilities

    GPUG ALL STAR
    Posted Sep 07, 2018 09:02 AM
    ​Great point Derek...

    In my post I recommend creating a GP user without permissions to anything in GP but making that login a sysadmin in SQL. This is in essence the same as using DYNSA but you could have named accounts for those that require this access.

    Jen

    ------------------------------
    Jen Kuntz, CPA, CGA, Microsoft MVP (Business Applications)
    Manager, Business Solutions
    Energy+ Inc.
    Cambridge, ON, Canada
    ------------------------------

    GPUG Summit - Post


  • 5.  RE: TIP: Using a non "sa" account to run GP utilities

    GPUG ALL STAR
    Posted Sep 10, 2018 09:22 AM
    Derek,

    I have to disagree with you. Your comments reflect a common misunderstanding. You said:

    "To add to this, while what you say is true, when you give a user or login the 'sysadmin' role in SQL, you are also giving them all permission on the SQL side, meaning they can run any type of script they want, create or remove databases, create or remove users, add or remove permissions to other user, they are basically 'all-powerful' on the SQL side"

    This is simply untrue for GP users. If you give a GP User sysadmin access they cannot access SQL directly with their GP login. In GP, they cannot do anything outside of the context of their GP User. For example, if you give a GP user sysadmin in SQL, but don't give them access to add users in GP, they will not be allowed to add users in GP. If you give access to add users but don't let them delete companies, they won't be able to do it.

    For GP UTILITIES specifically, a sysadmin user could do anything in utilities, because Utilities doesn't offer additional security, but that is a failure on the GP side, the user is still not all-powerful in SQL.

    Second, you said:

    "In the Documentation folder in each install of Dynamics GP, there is a SecurityPlanning.pdf file which has a chapter on 'Core application security tasks' also, that gives options on giving non-sa users permissions to do things like create and delete users, give users access to GP company databases, backup and restore databases, business alerts, SQL maintenance, and more.

    Just wanted to add this information, as normally we don't recommend giving users sysadmin permissions as it gives that user maximum permissions on both the GP and SQL side."

    Again, none of those things can be done by a GP user outside of GP and all of them can be controlled with GP security. In the documentation you refer to, option 2 is "Assign the specific Microsoft Dynamics GP administrator SQL login accounts to the SysAdmin fixed server role." (Planning for GP Security, P.35, Option 2). Also, options 4 and 5 are more complicated variations on this theme. I find it odd that you don't recommend an option that is recommended in the documentation.

    Both 'sa' and 'DYNSA' are effectively anonymous in many organizations because more than one person holds the password. In my opinion (along with most auditors and security experts), that is a worse option.

    For GP UTILITIES, I generally recommend using sa for utilities as well for the reason you stated: "only the 'sa' login, by default, has all the permissions on the SQL side that GP Utilities requires" and because I've seen enough weird stuff in my years of working with GP. For Dynamics GP specifically, using sa is far from the best option and when you give people wrong information it leads to poor security choices.

    I've personally tested all of this. A GP user with sysadmin has no access to SQL outside of GP applications and GP security. Even sa can be restricted in GP by removing its Power User role. The big difference is that sa DOES have rights in SQL to make changes behind the scenes.

    Any time you want to walk through this I'm available. I'll be at Summit as well and I'm happy to test any of this with you.




    ------------------------------
    Mark Polino
    Director of Client Services
    Fastpath
    Altamonte Springs FL
    ------------------------------

    GPUG Summit - Post


  • 6.  RE: TIP: Using a non "sa" account to run GP utilities

    GPUG ALL STAR
    Posted Sep 14, 2018 11:29 AM
    Jen reached out an asked me to take another look at this, believing that Derek's reply was only directed at GP Utilities. I had a couple of other people look at it and they got the impression that Derek's comments applied to GP to as well. My comments above still apply to GP.

    With respect only GP Utilities. I'm inclined to use 'sa'. Why? Use of GP utilities falls under the Christmas rule. If it's something that doesn't happen often, but is important, it takes more work to get it right. If a non-sa user needs to run GP utilities, asking an sa user to be involved provides more eyes on the process and ensure that what is being done is correct. Rob's idea of assigning sysadmin to a SQL user (not a GP user) would open up considerable access and to do that just for GP utilities seems like significant overkill. However, there may be exceptions. A REIT with hundreds of small entities and growing might fit the exception because they would regularly be creating GP companies. Short of something like that, I think it makes more sense to use and involve an sa user.

    Mark

    ------------------------------
    Mark Polino
    Director of Client Services
    Fastpath
    Altamonte Springs FL
    ------------------------------

    GPUG Summit - Post


  • 7.  RE: TIP: Using a non "sa" account to run GP utilities

    GPUG ALL STAR
    Posted Sep 14, 2018 05:03 PM
    ​Thanks for the clarification Mark. I don't disagree about using 'sa' for most circumstances, and I agree, the way the posts thread, it wasn't super clear that Derek was responding only to the "create a SQL sysadmin user" part.

    My limited use case for non-'sa' users for GP Utilities is workstation installs where GP Utilities is required, briefly, and needs virtually no access on the part of the end users. Junior IT staff are often installing workstations throughout the year as new staff come and new computers get issued etc. so it's a lot more regular than the other primary tasks done in GP Utilities. They shouldn't require 'sa' just to do that, nor do they require many permissions/rights. As you pointed out, other scenarios are relatively rare and thus using 'sa' makes sense when it comes to companies etc.

    Jen

    ------------------------------
    Jen Kuntz, CPA, CGA, Microsoft MVP (Business Applications)
    Manager, Business Solutions
    Energy+ Inc.
    Cambridge, ON, Canada
    ------------------------------

    GPUG Summit - Post


If you've found this thread useful, dive deeper into User Group community content by role