Troubleshooting SQL connection

Owned by Darcy Curtin
Last updated: Jul 21, 2020 by Rakesh
3 min read

Some of the common issues seen when NLINK tries to connect to SQL database are documented here.

  1. NLINK fails to find ODBC DSN on 64-bit OS
    On Windows 64 bit OS, Windows Server 2016 64 bit or Windows 2019, if there is a SQL External System which uses a ODBC DSN, NLINK 6.x may fail to find ODBC DSN (e.g. ‘FooBar’) even though it is configured properly in ODBC Admin.

    NLINK fails with one of the following messages:

Error Message
Driver Manager] Data source name not found and no default driver specified Number = 0x80004005; Native Error = 0; DSN = 'FooBar' SQLState = IM002 Source = Microsoft OLE DB Provider for ODBC Drivers ] 
Error Message
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Solution
For NLINK 7.x (or later)
NLINK 7.x, NLINK Server is a 64-bit application, where as NCM, NMM are 32 bit applications. So any ODBC DSN should be created with both 64 bit ODBC DSN (for use with NLINK Server) and 32 bit ODBC DSN (for use with NCM, NMM).

For NLINK 6.x (or earlier)
NLINK 6.x (or earlier) is a 32-bit application that can be installed and run on 64-bit operating system. As with any 32-bit application, 32-bit ODBC DSN should be used for NLINK to recognize ODBC DSN. To setup ODBC DSN for 32 bit applications, use 32 bit ODBC manager “C:\Windows\SysWOW64\odbcad32.exe”

2. NMM (or NCM) connection test fails with “Format of the initialization string does not conform to specification starting at index 0.”

Similar issue can occur with NCM SQL Wizard.

Solution
For a SQL external system, using NMM (or NCM) Test connection or NCM SQL wizard will fail if the ODBC data source is used and “Use Connection String” is set to “Yes”. If “Use Connection String” is set to Yes, then OLE DB string should be used. See https://junotsystems.atlassian.net/wiki/spaces/JWH/pages/66253657 for details on setting up using connection strings

3.NLINK Server fails to connect with database with error “Login failed for user ''“

Even if UserId is properly configured NLINK Server fails to connect to database.

Solution
For SQL External System if “Use Connection String” is set to “Yes”, the make sure proper connection string is used with place holder for User ID. See https://junotsystems.atlassian.net/wiki/spaces/JWH/pages/66253657 for details on setting up using connection strings.

4. About ODBC Data Sources in 64-bit OS

In Windows operating systems, the ODBC Data Sources are managed using Microsoft ODBC Data Source Administrator. This application is typically listed as “Data Sources (ODBC)” in Control Panel->Administrative Tools.

When using Windows Server 2012 or Windows Server 2016 64 bit operating system, there are 2 versions of “ODBC Data Source Administrator”, one for use by 64-bit applications and another for use by 32-bit applications. The 64-bit ODBC Administrator tool can be invoked from Control Panel to manage user DSNs and system DSNs that are used by 64-bit processes. 

All 32-bit applications that need ODBC Data Source need to use 32-bit ODBC Administrator tool. The 32-bit ODBC Administrator tool must be used to to manage user DSNs and system DSNs that are used by WOW64 processes.

Note: WOW64 is for 32-bit applications

WoW64 (Windows 32-bit On Windows 64-bit) is a subsystem of the Windows operating system that is capable of running 32-bit applications and is included on all 64-bit versions of Windows.

  • 64-bit ODBC Administrator tool is located in folder “C:\Windows\System32\odbcad32.exe”

  • 32-bit ODBC Administrator tool is located in folder “C:\Windows\SysWOW64\odbcad32.exe”