• Introduction
• Connect to Microsoft SQL Server
• Setup ODBC
• Troubleshooting
• More Information

Introduction

This article explains how to connect to Microsoft SQL Server with ODBC from Windows 10.

It covers the following topics:

1. How to connect to Microsoft SQL Server (with code samples).
2. How to setup ODBC:
   • Configure ODBC using the ODBC Data Source Administrator:

     We recommend using system DSNs (available to all users).

Connect to Microsoft SQL Server [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples.

Follow these steps to connect:

1. Create a new channel to try out the code:
   • Create a channel like this:
     • Name: DB SQL Server Connection (or similar)
     • Source: From Translator
     • Destination: To Channel
   • Alternatively use an existing channel:

     Your channel will need a From/To Translator or a Filter component.

2. Connect to a database using ODBC:
   • Paste this code into the Translator:

     If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote Microsoft SQL Server database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

     function main()
           local conn = db.connect{   
           api=db.SQL_SERVER, 
           name='your_odbc_server_name',
           user='',      -- use empty string for integrated security
           password='',  -- use empty string for integrated security
           use_unicode = true,
           live = true
        }
     
        conn:execute{sql='SELECT * FROM <your table>', live=true}
     end

   • The name is the name of an ODBC source that you create using the ODBC Administrator.
3. Adapt the code to your requirements.

Setup ODBC [top]

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

1. Install the latest Microsoft SQL Server ODBC drivers:
   • Download the latest drivers from the Microsoft ODBC download site.
   • Run the installer and accept the defaults.
2. Open the 64 bit ODBC Administrator:

   Windows 10 (64 bit version) supports 32 and 64 bit ODBC sources — always use the 64 bit ODBC Administrator.

   • Search for ODBC and choose ODBC Data Sources (64 bit):
     
   • Right Click and choose Run as Administrator:
     
3. Open the System DSN tab and click Add:
   
4. Choose the latest SQL Server ODBC driver and click Finish:
   
5. Enter the ODBC credentials:
   • Use any Name and Description you prefer.
   • Enter the Network Name of the SQL Server in the Server field:
     Note: If you are unsure of the name ask your DBA (database administrator).
   • Click Finish to create the ODBC data source:
     
6. This will open the the ODBC Microsoft SQL Server Setup dialog box that lists the chosen settings:

   The ODBC source will use all the standard defaults which will work in most cases. There are two settings that you may need to change: Use Integrated Security and Database. Speak to your DBA (database administrator) about changing these if necessary.

   • Use Integrated Security: Sometimes you might want to use a SQL Server user and password
   • Database: You can specify the database that you want to use by default.
     
7. Test the data source connection:
   • Click Test Data Source…:
     
   • And if it works you are finished:
     
   • If it does not work speak to your DBA about how to login to the database (user authentication).

If you need to modify the ODBC source follow these steps:

1. These are the two most common things to change:
   • Default Database: Change to Test
   • User Login: Change to user billsmith with password something-secret
2. Modify the ODBC source to change Database and User:
   • Choose the ODBC source and click Configure and click Next:
     
   • Choose SQL Server Authentication, enter User and Password and click Next:
     
3. Change the default database:
   • Select the Change the default database option:
   • Choose your default database from the drop list and click Next:
     
4. Finally test the connection to see that the changes worked.

Troubleshooting [top]

• Problems with connection settings:

  Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

• Iguana may not always support the very latest version of the ODBC drivers:

  Please contact us at support@interfaceware.com if you need to know the latest supported version of the ODBC drivers.

• Incorrect port number:

  SQL Server defaults to port 1433, but a different port can be used. Speak to your database administrator (DBA) or network administrator.

• Using integrated security:

  Using integrated security is the usual way to connect to a Microsoft SQL Server database. If this does not work speak to your DBA (database administrator) about the user credentials you should use to connect to the database.

If you need more help please contact us at support@interfaceware.com.

More Information [top]

• Using Database Connection Objects
• Forum thread on Windows ODBC
• Using Integrated Authentication, Microsoft SQL Server documentation (kerberos etc)
• Connect to Microsoft SQL Server from Linux or Mac with ODBC