I regularly work with customers who do not know what the SQL Server Instance Name is for their Dynamics GP environment. For non-technical users, this is totally understandable--it isn't something they would normally need to know. With IT folks, this is more common than you might think, as many system admins deal with many other technical items, but are just not familiar with SQL Server. And to top it off, the fact that Dynamics GP still uses ODBC DSNs can further confuse things for people who aren't familiar with how all of the data access plumbing works.
Let's talk about the ODBC DSN first, as it is a very common source of confusion with Dynamics GP.
When you launch Dynamics GP, you will see the main "Welcome to Dynamics GP" login window.
The first field on this login window is labeled "Server". This field name causes confusion because that value isn't actually a Server name at all. It's an ODBC Data Source Name, or DSN. A DSN is just an arbitrary name that is assigned for the connection to the SQL Server.
For typical GP installations, I recommend using the default value, which is currently Dynamics GP XXXX, with the X values representing the version number. Because the ODBC DSN affects how passwords are encrypted and decrypted for Dynamics GP, you'll want to make sure that you use the same DSN name on all GP workstations.
So where is the ODBC DSN setup and configured?
On 64-bit versions of Windows, which are pretty much the norm these days, you need to launch the 32-bit ODBC Data Source Administrator tool to check the ODBC DSN settings. This is the second layer that confuses a lot of people. Since Dynamics GP is still 32-bit, it uses the 32-bit ODBC drivers, and therefore uses a 32-bit ODBC DSN.
On newer versions of windows, in Administrative Tools, there is an "ODBC Data Sources (32-bit)" entry that you can launch.
On older versions of 64-bit Windows, this separate 32-bit option is not listed, and only the 64-bit version is available. To open the 32-bit Data Sources, you have to use Windows Explorer to open it. It is located in the unfortunate location of:
C:\Windows\SysWOW64\odbcad32.exe
If you launch via either of the above options, you will see this ODBC Administrator 32-bit window.
Notice that the window title says 32-bit. You can then select your Dynamics GP DSN and click Configure.
And finally, once that is open, you will see your REAL SQL Server instance name that is being used for your Dynamics GP ODBC DSN, shown in the Server field.
So that was the longer, but safer way to check the SQL Server instance name on a machine where Dynamics GP is installed. This is safer because some customers have multiple environments, multiple SQL Servers, and multiple SQL Server instances. Occasionally, there may be a GP machine that is pointed to a test SQL server instance, so it never hurts to double check.
If you are more technical and have access to the SQL Server or to SQL Server Management Studio, there is another way to quickly check the SQL instance name. When you launch Management Studio and login to the GP SQL Server, you should see the instance name listed in Object Explorer.
In this example, my SQL Server is using a "default instance" and is named "GP2015", which is the same as the server name.
But you may have a server that has a "named instance", in which case you will see the server name followed by a backslash, followed by the instance name.
In this example, my server is named GPDEV2, but I have a named SQL Server Instance called "GPDEV2\GP2010".
As mentioned before, you need to be careful about getting the instance name from Management Studio because there could be multiple SQL Servers and multiple SQL instances. Just because you see a particular instance when you log into Management Studio doesn't necessarily mean that it is the one being used by Dynamics GP.
When in doubt, just find a machine running Dynamics GP that you are sure points to the SQL Server that you want, and then check the ODBC DSN settings on that machine.
Hopefully that helps explain what a SQL Server Instance Name is and how to find it in a Dynamics GP environment.
Steve Endow is a Microsoft MVP
for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.
He is the owner of Precipio Services, which provides Dynamics GP
integrations, customizations, and automation solutions.
Also worth pointing out many of us use internal DNS to hide the GP server name behind a DNS alias as the algorithm that encrypts the password includes the User ID and the Server name from the ODBC DSN (not case sensitive) in the encryption key. Thus abstracting the sever name like this prevents us having to reset any passwords when we move which SQL server is hosting GP.
ReplyDelete(see why GP encrypts passwords: https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/10/01/why-does-microsoft-dynamics-gp-encrypt-passwords/)
This DNS alias doesn't matter most of the time, except for some specific tasks like trying to manage SQL replication, where the real server name is required for the connection, or it will not work. Or finding the machine on the ESX virtual host for example.
Not an important point, but complimentary to your back to basics discussion.
You're better using %windir%\SYSWOW64\odbcad32.exe as the Windows folder isn't guaranteed to be C:\Windows
ReplyDelete