So, I figured in this article, I would go back to basics to walk through some of the common issues and demistify the Microsoft SQL Server DSN configuration options that the setup gracefully takes care of.
1. First up, if you are on Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2, you should be using the Microsoft SQL Server Native Client driver. At this point, if you have any legacy ODBC drivers for systems that have been upgraded from SQL Server 7.0 or SQL Server 2000, these should not be used to connect to SQL Server 2005 or SQL Server 2008, because a) you are not taking full advantage of the Native Client driver's performance, and b) simply because the old driver is not designed to be used with the new.
Clearly name your connection so you can identify which version of GP or server you are targetting. If you have multiple physical environments, for example, test and production, clearly name the driver to distinguish which environment you are targeting. Also, note that you can setup a driver that points to an instance of SQL Server by using the MACHINE\INSTANCE_NAME convention. Starting with version 10.0, the ODBC name must be exactly the same across all workstations where Microsoft Dynamics GP is installed.
2. Microsoft Dynamics GP only supports SQL Server authentication. As much as you complain or rant about the system not supporting Integrated Windows Authentication, you cannot set the authentication method to anything other than SQL Server authentication, in which case you will need a login ID and password only to test your connection. You can certainly avoid going through the other steps of the setup if you choose to uncheck the Connect to SQL Server to obtain default settings for the additional configuration options checkmark.
|Setting up the Authentication method|
Also note the checkmark option's prompt. The settings are read from SQL Server to obtain the default ODBC connection settings. This might sound redundant, but you will understand what this means next.
Some frequently asked questions as well are:
a) Why can't I use my Microsoft Dynamics GP account to authenticate my ODBC connection?
Because Microsoft Dynamics GP encrypts passwords on SQL Server. Since the password is encrypted on Microsoft SQL Server you would have to enter the sequence of characters that are a part of the encrypted password to authenticate and this is simply not feasible as well, you don't know the encrypted password to begin with. For more information see Why does Microsoft Dynamics GP encrypt passwords over at Developing for Dynamics GP.
b) Does the password I enter here get stored with the Connection?
Categorically No! The user Id and password information entered here is only used for verification of the SQL Server default settings and testing of the connection itself. They are never stored with the setup.
3. With newer versions of Microsoft Dynamics GP, there's no need to set the default database to Microsoft Dynamics GP system database, DYNAMICS. However, since I am an old timer and still have my own quirks, I do it as a standard practice. The default database is the master database when no other database is specified in the connection.
|Microsoft SQL Server Properties window - Connections tab|
Until next post!
Mariano Gomez, MVP