Transferring GP To A New Server Without Resetting All Passwords

[One of the annoyances when migrating a client’s installation of Microsoft Dynamics GP to a new server is that, unless the new server has the same name as the old one, the password of every user needs to be reset as the server name is encrypted in with the password.

The is another alternative though and that is to create a DNS CNAME Alias and use this as the server name in the SQL Server ODBC instead of the servers actual name. When GP is migrated to a new box you only need to amend the DNS CNAME Alias and users can continue logging in with the same password.

To do this you need to log onto the DNS Server and then go to (Windows Start menu ¯ Administrative Tools ¯ DNS) and into the Forward Lookup Zone for your domain and right click New Alias (CNAME). Enter an Alias name and the fully qualified domain name for the SQL server (in the example below GPTEST is configured as an alias for SQL2.azurecurve.co.uk);

DNS Manager

Once done open the SQL Server Configuration Manager (Windows Start menu ¯ All Programs ¯ Microsoft SQL Server 2008 R2 ¯ Configuration Tools ¯ SQL Server Configuration Manager) and expand the SQL Server Network Configuration node and click on Protocols for MSSQLServer and ensure TCP/IP is Enabled;

SQL Server Configuration Manager

Then it’s a case of modifying the SQL Server ODBC System DSN on each client to use the CNAME Alias (GPTEST) instead of the server name (SQL2);

Microsoft SQL Server DSN Configuration

If you’re using Management Reporter you will need to change SQL Server in (Tools ¯ Company) the Company Setup;

Company Wizard - Microsoft Dynamics GP

The one Dynamics GP related product I’ve had issues with this approach was FRx which did not like the use of the CNAME Alias and insisted on having the server name; however, this did not impact on the user’s password.

With the above configuration, once SQL2 is retired and a new server brought online all we need to do is update the CNAME Alias with the new server name and all users will be able to log in without the password needing to be changed.featured-image]

What should we write about next?

Your Name (required) -
Your Email (required) -

(Visited 3,012 times, 1 visits today)

11 thoughts on “Transferring GP To A New Server Without Resetting All Passwords

  1. Perry Smith says:

    Ian,

    Thanks for this. I think I will do this for all of my new installs. I am going to modify the procedure a bit for a server transfer tonight that they are retiring the old server name.

    Perry Smith
    Integrated Systems Solutions

    1. Ian Grieve Ian Grieve says:

      Hi Perry,

      Pleased you found the post useful.

      This approach really comes into it’s own during disaster recovery situations when you need a backup server up and running in place of live quickly and don’t want to spend time resetting passwords. Fire up the backup server, change the DNS and you’re good to go.

      Ian

  2. zack zeeaay says:

    I got kind of lost as it is not that much clear.
    Old Server = A
    New Server = B

    DNS forward look up zone on A or B ?

    odbc ON A (old) OR B (new) with

    How about, if A is still being used as sandbox and B as new Server ?

    How would one know he is going on A or B

    Also, if A is old and being retired then where to get the DNS from if that is being setup on A?

  3. Ian Grieve Ian Grieve says:

    Hi Zack,

    The DNS would typically be on the domain controller which would usually be a different machine to the SQL server.

    So the server configuration would be;
    Old Server = A
    New Server = B
    Domain Controller/DNS Server = Z

    So users know which server they are logged into, when keeping the old server as a test box, you could give the ODBC a different name; I typically change the company names as well when the client has a separate server for testing to make sure they know if they’re logged into the test Live company or the live Live company.

  4. Hatton Humphrey says:

    First off, thanks for posting this! Even as long ago as it was written, it’s still useful. I am in the process of migrating a Dynamics GP 10 application to a new SQL server and had toyed with the concept of using the HOSTS file to get around this.

    Have you found that this can be considered a long-term solution or are there ramifications in using this beyond an emergency solution?

    1. Ian Grieve Ian Grieve says:

      I have clients who have been running GP with a CNAME alias for 4 years or more. As long as you install Management Reporter with the CNAME as well it will operate without problems.

  5. Ven Sharma says:

    Hello Ian:

    Invaluable post. How would this work with a named instance of SQL Server?

    Thanks

    Ven Sharma

    1. Ian Grieve Ian Grieve says:

      Hi Ven,

      The CNAME alias points at the Windows Server running SQL so you’d configure it exactly as described above.

      When you enter the ODBC name it would be entered as GPLIVE\GP assuming you had a server called GPLIVE containing a named instance of SQL Server called GP.

Leave a Reply

Your email address will not be published. Required fields are marked *