[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 () 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);
Once done open the SQL Server Configuration Manager () and expand the SQL Server Network Configuration node and click on Protocols for MSSQLServer and ensure TCP/IP is Enabled;
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);
If you’re using Management Reporter you will need to change SQL Server in () the Company Setup;
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?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
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
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
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?
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.
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?
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.
Hello Ian:
Invaluable post. How would this work with a named instance of SQL Server?
Thanks
Ven Sharma
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.