Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Terry Horn]

During a recent audit it was noted that many of the database updates occurred with “sa” authority.  Because of this, we were asked to either make “sa” a service account, i.e. not being able to log into GP as “sa”, or find a way to audit every “sa” transaction.

Our feedback was that we had to log in as “sa” to do certain tasks within GP, including installing new updates, and that it is our understanding that “sa” is used in the core GP code to process transactions so we cannot change that either.

We log all “sa” transactions, but auditing 500+ pages per weekly is an unruly task and I cannot offer confidence in it being thorough.

1. Is there a way to have core GP tasks run under something other than “sa”?

——————————
Terry Horn
IT Manager
Owensboro Municipal Utilities
Owensboro KY
——————————

[Kerry Hataley]

Hi Terry,

What I have done for a while is created a poweruser that has sysadmin (sa) privileges for whom ever is going to administer the Dynamic’s System. (See attached)

Now, from an audit perspective, since this is still a SQL account and NOT an Active Directory Account the auditors do still have some issues with it. (Our auditors for NTPC is the OAG (Auditor General of Canada) so very procedure based.

Since we use FastPath, it allows us to give them some reassurance that this is used only by a single user and not a shared ‘sa’ password.

I have only ever had one 3rd party module not like this but I can’t for the life of me remember who that was – a few years back now. (Cosgdale is fine with this arrangement)

The nice thing also about this is the yearend rollovers all have a user id NOT ‘sa’, which does make them happier.

NTPC is currently on 2016R2 / CSM 39.1 so this has worked since 2010 / CSM 28.x

I hope this helps….

——————————
================================================
Kerry Hataley
Nanook Software
Microsoft Dynamics GP Consultant
================================================
————————————————————————-

[Terry Horn]

Thanks Kerry.

——————————
Terry Horn
IT Manager
Owensboro Municipal Utilities
Owensboro KY
————————————————————————-

[Rob Klaproth]

Terry,

The only time you need to use the “sa” user is when you are installing a new module, or running a service pack or upgrade on your system.  You can definitely give another user adequate permissions to do what they need to do in the accounting application.  

What I recommend is to create named administrators in your system, so if you are the admin, then login with your real user ID in GP, * NOT* the sa or the DYNSA accounts.   You can be granted POWERUSER access inside of GP and that should allow you to do everything you need.

There are a few things you need additional permissions in SQL:

-Adding a user

-Deleting a user

-Adding a user to, or removing them from a company

It is possible to grant your GP login permission to the above functions by making your GP login a “securityadmin” in SQL Management Studio.   In addition, you need to grant your GP login “db_securityadmin” and “db_accessadmin” in each of your GP companies including DYNAMICS.   By doing so, you will give yourself the permissions you need without having to use the “Sa” account.  Keep in mind, if you have some 3rd party “customizations” that are poorly written, they may have some dependency on the “Sa” permissions or may have even hard coded to run under sa, but these days most add-on software knows that this is a no-no.

——Original Message——

During a recent audit it was noted that many of the database updates occurred with “sa” authority.  Because of this, we were asked to either make “sa” a service account, i.e. not being able to log into GP as “sa”, or find a way to audit every “sa” transaction.

Our feedback was that we had to log in as “sa” to do certain tasks within GP, including installing new updates, and that it is our understanding that “sa” is used in the core GP code to process transactions so we cannot change that either.

We log all “sa” transactions, but auditing 500+ pages per weekly is an unruly task and I cannot offer confidence in it being thorough.

1. Is there a way to have core GP tasks run under something other than “sa”?

——————————
Terry Horn
IT Manager
Owensboro Municipal Utilities
Owensboro KY
——————————

[Terry Horn]

Thanks Rob!

——————————
Terry Horn
IT Manager
Owensboro Municipal Utilities
Owensboro KY
————————————————————————-

[Beat Bucher]

Hi Terry,

As Rob said, ‘sa’ is not a mandatory user to work in GP if you know how to setup a regular user to achieve 99% of the activities.. From an autditing perspective, this is crucial. The remaining 1% where ‘sa’ is really used in rare occasions is when you install the PSTL tools in GP (somehow that user is hard-coded in Dexterity and is checked against when running the PSTL setup.. and if not logged in with, you get an error message).

This is also true for some rare 3rd party ISV products that insist on the ‘sa’ user for the setup (maybe because they do want to go the safe route and not have to deal with uncertain security setup in SQL).

The only other time I use to login with ‘sa’ is in the GP Utilities when running an upgrade.. that’s it.

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
+1-514-489-4267
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
————————————————————————-

[Blair Christensen]

So some things will depend on your version of SQL Server and Dynamics, but generally, database updates have to be performed by sa because sa owns the database and has ultimate access to everything.  Do not confuse this with Dynamics GP updates, which are programmatic updates and only need to be installed by someone with (domain) administrator privileges.  Dynamics GP Utilities should be performed with DYNSA (or sa in older versions) unless you have specifically configured another user with adequate privileges.  If you have a sufficiently strong password for the sa and DYNSA accounts and only administrative users have this password, this is normally sufficient for the auditors as long as those same users aren’t showing up entering batches, approving payments or cutting checks.

If the auditors are still not satisfied, there are third-party auditing tools you can buy.  One caution: installing auditing will slow down operating, so be judicious in what you audit if you go that route.

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
————————————————————————-

[Terry Horn]

Thank you all.  This will help in our research.

——————————
Terry Horn
IT Manager
Owensboro Municipal Utilities
Owensboro KY
————————————————————————-