Create GP Users without using ‘sa’

 

A while back at a conference Nicole Albertson of eOne and I were talking about not using the ‘sa’ user and using a different GP user to create users. She brought up the central point of this piece, but I can’t find that she ever wrote about it. (If she did, feel free to add a link in the comments).  It’s been long enough that her thoughts have mingled with mine and I want to make sure that she gets credit.

sql-server sa

In most cases logging in as ‘sa’ is not required for Dynamics GP. The exceptions are really installing/upgrading products and possibly troubleshooting a specific issue. The Professional Services Tools no longer require ‘sa’. Third party applications should not be requiring ‘sa’ either. The point is that using ‘sa’ should be a rare exception, not the norm. Admittedly, ‘sa’ is incredibly convenient sometimes. So is parking in a fire lane. That doesn’t mean that it’s a good idea.

One place where people are often genuinely confused is creating new users. Any user in GP can be configured to add, administer, and delete users. The ‘sa’ user is not required. The Dynamics GP Planning for Security document, (Page 35, Creating User Records) offers a number of options for allowing any GP user to administer users. Most of these come down to variations on assigning a user to a fixed SQL Server role. The simplest, and most flexible, option is to assign a user to the SQL Server SYSADMIN role and ensure that the user is a member of DYNGRP (Option 2).

When they first see this option, most DBA’s recoil reflexively and default to the word “no”. The other fixed role options generate a similar response. The problem is that DBA’s are missing a piece of the puzzle that is unique to GP.

DBA’s hate the idea of assigning a user to the SYSADMIN role because designating a user as a SYSADMIN typically gives them total control over a database, but that is not the case with a GP user. The GP user’s password is encrypted prior to sending it to SQL. This means that the password that SQL sees is not the same as their GP password. When a user logs into GP, their password is unencrypted to allow GP to interact with SQL. Consequently, GP users cannot directly access SQL with their GP login credentials. It can’t be done. I’ve bolded that line so that you can show it to your DBA.

A GP user’s access to the power of SYSADMIN is limited to those tasks in GP that require SYSADMIN rights. The user is constrained in that they can only interact with SQL within the tasks available in GP. Another indicator of this is that changing a GP user’s password at the SQL level breaks their access to GP.

As a test for your DBA, create a GP login with some basic inquiry rights. Set that user as a member of the SYSADMIN role in SQL. Feel free to use your test environment if that makes them more comfortable. Give them the GP login and password and ask them to try to use SQL tools to access the database directly. It won’t happen.

Using the ‘sa’ user is dangerous, and not just because it offers total control. It also offers a level of anonymity. If multiple users have the ‘sa’ password, figuring out who used ‘sa’ to perform a task becomes extremely difficult, if not impossible. Once a DBA becomes comfortable that setting a few GP users as SYSADMINs won’t give them unfettered access to SQL, this option makes it much easier to limit the use of ‘sa’.

 

 

 

 

 

Advertisements

6 Replies to “Create GP Users without using ‘sa’”

  1. You can create an AddIn for GP that logs in as the current user and does all sorts of malicious things. It would, however, require a good chunk of technical knowledge and use of GPConnNet.dll to login as a user. The GPConnNet.dll requires keys and keys are only available from Microsoft to certain groups of people, but just make sure that you are aware of this if you go this route as it is not 100% bullet proof.

    1. Any malicious add in that uses the current user would be the same or worse if ‘sa’ is regularly used as the current user. With a non-sa user you’re still going to run into some constraints. With ‘sa’ it’s open season.

      1. Also, if you have robust SDLC policies and procedures in place, there’s no way for anyone to deploy a malicious AddIn to your production system.

  2. Nice article !!
    I have quick question around, if you can help me will be good. (I am not sure if its right place to ask)
    We are having ISV product using GP Addin(.Net) and we are connecting SQL using GPConnNet.dll. But in case customer using Fastpath AD login My application fails to login to SQL as I am using GP global’s to connect to SQL. My understanding is Fastpath update/modify GP login password after it loads into GP global.

    Can you please suggest possible cause?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.