MS SQL ODBC Connection Information

Setting up an ODBC Data Source

In cases where you want to use an ODBC Database API for your channel, you will need to ensure that you have configured an appropriate ODBC data source.

Note: This example shows you how to set up a new ODBC connection to an MS SQL Server database. Note that the steps and screenshots will be similar but slightly different when you set up other supported databases like MS Access.

Iguana supports some databases, such as MySQL and Oracle, through their native interfaces (i.e. not through ODBC). In such cases, the Data Source name specified in the channel configuration only needs to be the name of the database itself and no ODBC data source needs to be configured.

Tip: It is always good idea to make sure that you have latest ODBC drivers for your version of SQL Server (and other databases you are using).

Create the ODBC Data Source

To set up a new ODBC data source for MS SQL Server:

  • Click Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC). The ODBC Data Source Administrator window appears

  • Click the System DSN tab
  • Click Add. The Create New Data Source window appears

  • Highlight SQL Server and click Finish. The Create a New Data Source to SQL Server window appears

    Note: Iguana also supports the SQL Server Native Client (SNAC) driver as well as the older MDAC ODBC drivers.

  • Enter the Name of the Data Source. This is the name that will appear under System Data Sources in the System DSN tab
  • (Optional) Enter a Description of the data source, if desired
  • Specify the SQL Server that you want to connect to, and click Next

  • Ensure that With SQL Server authentication using a login ID and password entered by the user and Connect to SQL Server to obtain default settings for the additional configuration options are selected
  • Enter the username and password information for the SQL account you are using, and click Next. This account should have full access to the database

  • Select the Change the default database to checkbox, and select the applicable database using the drop-down arrow

    Note: The Microsoft SQL Server ODBC driver does not remember the password, whereas some other ODBC drivers do remember the user password.

    See Troubleshooting for more information.

  • Click Next > Finish to complete the setup

A summary of your MS SQL Server configuration is displayed.

  • Click Test Data Source to ensure that you can connect to the specified MS SQL Server. A window displays the results of the test, as shown below:

  • Click OK to close the results window
  • Click OK again to close the summary of your MS SQL Server configuration. The ODBC Data Source Administrator window displays your created data source, as shown below

The next step is to modify the Iguana Log On properties so that you can connect to your SQL Server database.

Change the Iguana Service Logon

When running Iguana, you may encounter issues when trying to access resources such as VMD files and database files that are located on network shares. If you ever encounter such an issue, you must modify the Iguana Log On properties so you can connect to and access your database, as well as other resources.

To change the Iguana Log On properties:

  • Open the Services window by clicking Start > Settings > Control Panel > Administrative Tools > Services. The Services window appears.

  • Right-click iNTERFACEWARE Iguana, and select Properties. The iNTERFACEWARE Iguana Properties window appears.

  • Click the Log On tab.
  • Select This account.
  • In the This account field, type the user name that the Iguana service will log on as, or use the Browse button to select a user name.

  • In the Password and Confirm password fields, type the password for this user name.
  • Click Apply to save your changes, and then click OK.

After changing the Iguana Log On properties, you can now easily add and configure a new channel that uses MS SQL Server for Iguana. For details, see Iguana Channel Settings.

Troubleshooting

These are some solutions to some related customer problems:

Storing a password in a DSN:

  • The Microsoft SQL Server driver does not store the user password in the DSN, therefore you must supply the password each time you connect
    • You can supply the password (as plain text) in your Lua code, this may not be secure enough
    • You can retrieve a user password from a database (or web service etc.), this is more secure
    • Microsoft Best Practice: Microsoft recommends using Windows Authentication to identify the user (AD or Windows account)
  • However, just to confuse the issue, some ODBC drivers do store user passwords:
    • For example: The Oracle driver for MySQL and PostgreSQL store user passwords
    • Therefore you will need to check/test your ODBC drivers to see if they store user passwords
  • This forum thread discusses various ODBC access issues including passwords

MS SQL ODBC Error “Invalid Column Name”

  • This is not really an ODBC error, it occurs when data is not escaped correctly and is therefore “mistaken” for a field name
  • The issue was raised in this forum thread
  • There are a couple of solutions (from the forum thread)
    • Not recommended: Turn off “ANSI Quoted Identifiers” in the ODBC driver
    • Recommended: Escape the string correctly in Lua
      local R = conn:query('SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = "'..key..'"')
      -- > SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = "FIVE"
      -- this query fails as the double quotes are not ANSI standard
      
      -- the alternatives below produce the same working ANSI standard query using single quotes
      local R = conn:query("SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = '"..key.."'") 
      local R = conn:query([[SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = ']]..key..[[']])
      --> SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = 'FIVE'

Using 64 bit ODBC [top]

Quick Problem

On 64 bit Windows you need to use 32 or 64 bit ODBC, to match the version of Iguana that is installed.
Note: This is not an issue if you are running 32 bit Windows.

Quick Solution

Always create a 32 and 64 bit version for each ODBC source, this way Iguana will “choose” the correct one.
Note: This allows you to run a test machine with 32 and 64 bit Iguana installed.

  1. Use the default (64 bit) ODBC Admin Tool to create your 64 bit data source
  2. Close the default (64 bit) ODBC Admin Tool, do not omit this step
  3. Use the 32 bit ODBC administrator to create a 32 bit data source using the same name
    • Run from here: %SystemRoot%\SysWow64\odbcad32.exe. (default install: C:\windows\SysWow64\odbcad32.exe)

Warning: Differentiating between which tool is running can be confusing, because:

  • The 32 and 64 bit ODBC Admin Tools look identical
  • The 32 bit and 64 bit executable are both called odbcad32.exe, which can be confusing…
    • 64 bit executable: %SystemRoot%\System32\odbcad32.exe. (default install: C:\windows\System32\odbcad32.exe)
    • 32 bit executable: %SystemRoot%\SysWow64\odbcad32.exe. (default install: C:\windows\SysWow64\odbcad32.exe)
  • All the ODBC DLLS are in the “System32” directory and called odbcXX32.dll (the same DLLs are used for 32 and 64 bit ODBC)
  • If you forget step 2. (closing the 64 bit tool before running the 32 bit tool), the 64 tool remains active and can fool you into thinking it is the 32 bit tool
  • Both of the ODBC Admin Tools use the same DLLs, therefore:
    • Comparing the DLLs the About tab does not help, as they are identical
    • Comparing the DLLs the Drivers tab does not help much, as the same driver DLLs are used (though different ones may be loaded)
  • Also see this Microsoft support page: http://support.microsoft.com/kb/942976

Background Information

Until we completely get rid of Chameleon there are some aggravations which come up for people using the 64 bit version of Iguana on windows to talk to Sybase and SQL Server. There are some easy workarounds to the problems you face though.

Here’s some of the pitfalls.

For Chameleon

Chameleon is a 32-bit application. Yep, that’s a pain. It’s written in Delphi, which doesn’t support 64-bit yet and probably never will. This is one of the many reasons it would be nice to not have the Translator depend on Chameleon. That’s our plan.

In the meantime, if you are running on a 64-bit version of Windows, which is increasingly becoming the norm, you’re going to love that Microsoft have a 32-bit ODBC manager and a 64-bit ODBC manager.

To get to the 32-bit ODBC Administrator tool, you’ll need to look in: %SystemRoot%\SysWow64\odbcad32.exe. Depending on your setup, that may be C:\windows\SysWow64\odbcad32.exe.

(We have a 64 bit Chameleon run-time library, but it’s just for people running Chameleon-based applications.)

For Iguana

If you installed the 64-bit version of Iguana, you’ll need to use the standard ODBC database manager. Otherwise, if you installed the 32-bit version of Iguana, you’ll need to use the 32-bit ODBC manager.

Other Caveats

  • Remember to install your datasource as a System DSN
  • Make sure that the user ID that Iguana runs under can see the ODBC sources you set up
  • Be careful to to pass the full path to obdbcad32.exe. We had one user who just executed odbcad32.exe and it turned out to be a link to the 64 bit ODBC manager which caused no end of confusion

One good thing is that you can often set up SQL Server connections so that you don’t need to put usernames and passwords in. That can be handy.

If you do run into issues with ODBC during your evaluation of Iguana, let us know. We’re willing to help out. We know that it’s not the easiest problem to solve.

We’ve had this information around for a while – it’s just becoming the norm now rather than the exception:ODBC Connection Issues Using 64-Bit Windows

Sometimes the questions comes up whether we have plans to support other database connection standards like OleDB. This question usually comes from Microsoft SQL users.

We probably are not likely to do that. To understand why you need to look at what we would be seeking to achieve by clearly understand the problems with ODBC. Firstly these are not problems:

  1. ODBC is not slow. We support native direct to the wire interfaces to Oracle and MySQL interfaces using their native library APIs. We find these interfaces are not any faster than using ODBC with these databases.
  2. ODBC doesn’t have any size limitations – any limitations come about from the underlying database drivers. Under the hood we use parameter binding for the data so it means we can handle very large messages with no problem.

The problems that do exist with ODBC:

  1. It’s a bit of hassle setting up ODBC connections – for sites which have 100’s of interfaces this can be bit of chore although with tricks like exporting your registry this problem can be overcome.
  2. It has a bad brand.
  3. There is the annoyance like we see above with 32 bit vs. 64 bit systems – although that will go away once Iguana can edit and import table schema directly within it’s GUI without requiring Chameleon – at that point 64 bit users can use the 64 bit version of Iguana and not experience the issues above.

Our most immediate priority on the database side is therefore to eliminate Chameleon’s role in the whole process. It would greatly improve the usability of the Translator to not require Chameleon.

ODBC Connection Issues Using 64-Bit Windows [top]

Problem

When attempting to use an ODBC data source in Iguana on a 64-bit Windows machine, you may encounter the following error:

ODBC Error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Symptoms

This error only occurs when attempting to use a channel that uses the ODBC data source.

To reproduce this error:

  • Set up Iguana on a Windows 64-bit machine. It should run normally.
  • Create a new channel that uses that data source. You should encounter the error message shown above when you try to run the channel, or view the logs.

Solution

The error message itself indicates that the data source was not found. The error occurs at this point because the data source was created using the 64-bit ODBC Administrator tool, and Iguana cannot find the sources made by that tool.

To fix this issue:

  • Set up an ODBC connection using the 32-bit ODBC Administrator tool located in the following directory:

%SystemRoot%\SysWow64\odbcad32.exe

On a default install, this tool is located in the following directory:

C:\WINDOWS\SysWOW64\odbcad32.exe

  • Create a new channel that uses the data source you created using the ODBC Administrator tool. The error message should no longer appear.

Tip: Did you know?

The 64-bit version of Windows includes two ODBC Administrator tools:

  • 64-bit ODBC Administrator tool – Invoked from the Control Panel to manage user DSNs and system DSNs that are used by 64-bit processes.
  • 32-bit ODBC Administrator tool – Invoked directly from the SysWoW64 folder to manage user DSNs and system DSNs that are used by WOW64 processes.

Tuning Tips [top]

The following tuning tips may help improve the performance of your database in an Iguana environment.

Setting up a Database Maintenance Plan for MS SQL

If you are using an MS SQL database, a Database Maintenance Plan is recommended. This will ensure that your database is safe and properly maintained. The following resources provide more information on creating a Database Maintenance Plan:

Installing MS SQL on a Separate Partition

If you are using MS SQL, you should not install your database on the system. A better solution is to install your database on a separate partition with a large amount of available disk space. This will ensure that there will be enough space to store your database tables as they grow.

Leave a Reply