Today's #TipTuesday is short, but also related to a post from a couple of weeks ago where I posted what the minimum permissions required were for using GP Utilities. Coincidentally (or perhaps not?), last week a consultant posted a "tip" (link removed) on this on the GPUG Open Forum, where there was a dangerous suggestion made, that I'm not sure they fully realized the implication of (at least I hope they didn't think that one through and posted in haste…).
Good intentions?
The tip started out with good intentions: do I need to use 'sa' for GP Utilities? Good question and the answer is a resounding no. The login needs to have sufficient permissions to perform the desired tasks.
However, the poster suggested that via testing he realized that the login doesn't even need to be a GP login to use GP Utilities and implying you could just create a login in SQL with the "sysadmin" fixed server role for that instead of creating one in GP first.
The poster added a link to Fastpath's white paper on security, even though they glaringly were misinterpreting the recommendations. Read the white paper, and follow it's recommendations for sure, but to be perfectly clear, nowhere do they suggest creating a SQL login for anything. It's a fantastic resource for understanding all the ways you do not need to be using 'sa' in Dynamics GP for day to day administration.
SQL user vs. GP user
The SQL Server 'sa' account is a SQL login, it exists on mixed-mode installations only and has nothing to do with GP in the sense that it's not related or created by a Dynamics GP installation. It's the "system administrator" user in SQL which has permissions to EVERYTHING in SQL - from all the data, to creating and dropping databases, logins etc. The role its assigned is "sysadmin", a server role.
Every user created within Dynamics GP, including DYNSA, are GP logins. Every GP login's password is encrypted so that it cannot be used elsewhere to obtain unauthorized access to data. Regular GP logins aka users that aren't "web client only" users, have a corresponding SQL login created once you create that GP user.
It's "safe" to elevate the permissions of a GP login in SQL if that user should have the ability to manage users and user access in Dynamics GP for instance, or perform tasks in GP Utilities like adding a company. Never remove security on a GP login in SQL from what the default is, but adding a server role or database role is fine, when specific situations like what I mention above exist. Why is it safe? The passwords are encrypted so that login is useless outside of Dynamics GP.
What's so dangerous about the other suggestion?
If you have concerns about giving someone your 'sa' password because of what it can do in SQL or GP, you should have the same reservations about making a new SQL login with the 'sysadmin' server role too. Saying "it's for GP Utilities" is ignoring a huge security concern. All it takes is for that user to be curious about what else that login can do and they can be using that SQL login anywhere to get into SQL Server - either via SSMS, dropping a database, or by running otherwise unauthorized queries in an external application.
The 'sa' password should be tightly controlled and in fact, rarely used, in my opinion. For the same reason, given a SQL login (not a GP login) "sysadmin" privileges in SQL Server should be treated exactly the same as the 'sa' password - in extremely limited circumstances only.
