Open Forum

Expand all | Collapse all

GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

  • 1.  GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    Posted Aug 18, 2021 02:26 PM
    We want to investigate moving to use a high availability setup using the always on database mirroring tools in SQL.
    We're on GP version 18.3.1245 currently and our user IDs are created with the 'SQL Server Account' option versus the 'Directory Account'.

    I'm seeing most sources say that the GP user passwords on the mirrored server won't be in sync with the primary SQL server and the only way to have users log onto the mirrored server would be to manually reset the GP passwords using the User Setup screen.

    Does anyone use this sort of replication setup, and if so, how do you handle the passwords on the mirrored server? I'm also now wondering if we were using the 'Directory Account' as opposed to the 'SQL Server Account' if this wouldn't be an issue at all to worry about.

    Of interest were these articles/forums posts:
    GP 2018 & SQL AlwwaysOn
    Description of the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP

    I also have a document titled "Guidance for running Microsoft Dynamics GP with Microsoft SQL Server AlwaysOn Availability Groups" that has a section on synchronizing the passwords, but I'm not entirely sure if that's proven it can work in terms of logging into GP application because of how GP encrypts/hashes the user password based on the SQL server (or perhaps ODBC connection).

    As a side note, we do use aliases for our SQL server instances at the moment.

    #Technical #Admin​​​​
    Academy - Online Interactive Learning from Experts


  • 2.  RE: GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    GPUG ALL STAR
    Posted Aug 19, 2021 03:06 AM
    @Julie Maillet

    GP Power Tools can recreate broken/damaged/missing SQL Logins and Database Users using its Database Validation feature.

    If the logins are working, but you want the reset the passwords, the SQL Login Maintenance feature will handle that for you.

    Both features allow for either a fixed password or randomly generated passwords to be used. They can also send emails to end users with their temporary password and force them to change the password on next login.

    See this article for formatting the password reset emails:

    https://winthropdc.wordpress.com/2020/11/11/gppt-using-hmtl-to-format-password-reset-emails/



    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    Posted Aug 19, 2021 07:52 AM
    Hi Julie

    In addition to Dave's suggestions and the links you have in your original post,
    please also take a look at Ian Grieve's post about using Cnames to circumvent the password reset requirement.
    I had a similar requirement (lower environment) and was able to test Ian Grieve's recommendation.

    Ian Grieve's CName link

    Good luck

    ------------------------------
    Dynamics GP Credentialed Pro (Install)
    Ven Sharma
    Finance and Admin Systems, Administration
    American Public University System (APUS)
    Charles Town WV
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    GPUG ALL STAR
    Posted Aug 19, 2021 10:25 AM
    Julie,
    I was going to second Ven on this topic.. by using an Alias (or CNAME) in the DNS, you can simply switch from one server to the other, and since the ODBC connection wont change, there is no need to reset the password.
    I've never tried the scenario described in the Microsoft paper about log shipping or server replication, but I'd guess that security should be integral part of the replication process..
    Replication Security Best Practices - SQL Server | Microsoft Docs

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    Posted Aug 19, 2021 10:51 AM
    Edited by Julie Maillet Aug 19, 2021 10:51 AM
    Great information everyone, thank you!

    Sounds like as long as we have the CNAME setup (which we already do), regardless of the SQL server or GP client being used, as long as the ODBC connection uses the same CNAME the GP user passwords should work. So in the case of my mirroring, if we had to cut over to the mirrored SQL server, we update the CNAME, and off we go without doing any password resets.

    Also, does anyone know or can confirm, the GP password in application encryption/hash is done based on the ODBC, and has nothing to do with the GP client, or SQL server itself? Trying to come full circle on how the password encrypt works, but I would assume this is the case and explains why as long as you have the CNAME setup in ODBC the passwords remain the same (and this would be applicable in say a lifecycle situation also).

    ------------------------------
    Julie Maillet
    Medavie Blue Cross
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    GPUG ALL STAR
    Posted Aug 19, 2021 10:55 AM
    Hi Julie

    Does this article I wrote in 2008 help

    https://winthropdc.wordpress.com/2008/10/01/why-does-microsoft-dynamics-gp-encrypt-passwords/

    Regards

    David

    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    Posted Aug 19, 2021 11:06 AM
    Yes it does, thanks again!

    ------------------------------
    Julie Maillet
    Medavie Blue Cross
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: GP Password Reset when using High Availability/Replication/Mirroring/Always On Setup

    GPUG ALL STAR
    Posted Aug 19, 2021 10:58 AM
    Hi Julie,
    That is correct.. If 2 workstations are connecting to the same GP instance, but under a different ODBC server name, then one of the computer will fail to authenticate to GP, since the Pwd may have been set / reset from a different server name.
    This happens when some users setup manually their ODBC connection to the SQL server and use the IP address instead of the SQL server name.
    I've done this in the past to setup an Alias when we switched the GP SQL server from an old version to a new one over a week-end, so the users wouldn't have to get all their passwords reset. Simply flipping the CNAME entry in the DNS did the job.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


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