Setting up an ODBC Data Source

• Create ODBC Data Source
• Change Iguana Service Logon
• Troubleshooting

Introduction

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.

Create the ODBC Data Source [top]

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 5.6.16 added support for the SQL Server Native Client (SNAC) drivers, e.g.:

  • The SQL Server Native Client (SNAC)
  • Or the Microsoft ODBC Driver 11 for SQL (the name for SNAC in version 11)

  Older versions of Iguana only support MDAC ODBC drivers, i.e., SQL Server (as shown in the screenshot above). If you have any questions please contact us at support@interfaceware.com.

• 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 [top]

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 [top]

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'