Microsoft SQL Server DSN Configuration

As I wrapped up fellow MVP Victoria Yudin's book just a week aback and prepared to write a review, I was reminded of the importance of properly configuring your Microsoft Dynamics GP DSN connection -- the artifact that allows the client software to read and write data to your company databases and the system database -- something the Microsoft Dynamics GP installation gracefully setup automatically nowadays for you. So, if this is the case, why is it that so many people still have issues with DSN connections and the Microsoft Dynamics GP client not "seeing" the server, I wondered.

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.


Getting started


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.

Choosing default database

What is still standard though is to disable the Use ANSI quoted identifiers and Use ANSI nulls, paddings and warnings in your connection settings. Now, keep in mind from my previous observation, that these connection settings are defaulted from Microsoft SQL Server settings at first. Why I emphasize this? Because I always get asked, Why are these checkmarks on? When in doubt, ask your Microsoft SQL Server administrator to show you the SQL Server properties for Connections.

Microsoft SQL Server Properties window - Connections tab

Also, you will want to note that at this point you can define a Mirroring server if you are running a mirrored database environment for your Microsoft Dynamics GP databases. For more information on Mirrroring, see KB article 926490 - Description of the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP (access to CustomerSource or PartnerSource is required to view this article).


These are some frequently asked questions:

a) Why can't I enable ANSI quoted identifiers and ANSI nulls, warnings, and pads?

The answer lays with Microsoft Dexterity. Dexterity (through the Runtime Engine) does not support quoted identifiers for character strings or hetorogeneous transactions -- the function of ANSI nulls, warnings, and pads is to maintain consistency of transactions and queries across distributed platforms. Since Microsoft Dynamics GP is a client/server based application and the system database, DYNAMICS was designed to live on the same SQL Server with the company databases, there was really no need to maintain this compatibility. After all, no heterogeneous query would ever be issued to begin with. 

4. Some more settings that are key to keep in mind reside with this wizard page. Among them Perform translation for character data, which receives most of the attention among Microsoft Dynamics GP consultants and database adminstrators alike. This setting was designed to do, as it name suggests, translations of characters between the client code pages and the server code page.

Defining connection settings

In earlier versions of MDAC, i.e., MDAC 2.1 or later version of the SQL Server ODBC driver (version 3.70.0623 or later) or the OLEDB provider (version 7.01.0623 or later), under some circumstances you could experience translation of character data from the client code page to the server code page, even when Autotranslation is disabled for the connection. Autotranslation is not the only mechanism that can result in code page conversion. The SQL Server 7.0 ODBC driver and OLEDB provider introduced a behavior when connecting to MSDE 1.0, SQL Server 7.0, or later versions of either. All SQL statements sent as a language event are converted to Unicode on the client before being sent to the server. The end effect of this is similar to an Autotranslation of all data flowing from the client to the server through a language event, regardless of the current Autotranslation setting for the connection. This will not introduce any difficulties except when trying to store non-translated character data from a code page other than SQL Server's code page.

So why do we talk about code pages? Because a) Microsoft Dynamics GP does not support Unicode characters. If you are a database developer it means, there is no such thing as NCHAR, NVARCHAR, or NTEXT table columns defined throughout the system or company databases, that would otherwise allow for Unicode character storage. Since this has been the case since version 1 of Microsoft Dynamics GP, it's easy to see how a different client code page (as in, a different language being ran at the Windows operating system level) could cause issues if this option were to be enabled). I have ran my own tests with a Russian locale (code page 1251) and 1250 code page on SQL Server and when this option is enabled, I get jumbled data in my GP tables when submitting Russian characters to be stored on the server. That's because, while there are subtle differences between code page 1251 and code page 1250, the latter only supports Eastern European languages that are based on the latin character set.

5. Once you have completed the settings, it's now time to review the summary for all the options you have chosen. 

Reviewing connection configuration

6. Finally, you can test and you should now be good to go.

Testing connection

I hope this review of ODBC settings for the SQL Server Native driver have served as a good first step in understanding the configuration. With so many settings it's easy to see why any subtle change would cause issues across the board..

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/