Configuring ODBC To Connect To A SQL Server Back End

As anyone who has worked with database systems and front-end applications knows, ODBC is a protocol that is used to allow for communication between applications on a front-end server and database management systems. ODBC, short for Open Database Connectivity, is a standardized application programming interface and is independent of both the database system being used and the operating system.  This essentially allows for a Linux operating system to communicate with a Microsoft SQL Server database or an Oracle database to communicate with an OS X operating system.

Though the interface for configuring ODBC varies slightly across operating systems, the concept is basically the same.  Below I will demonstrate configuring ODBC on a Windows Server to point to a SQL Server back-end.

For starters it’s worth mentioning that there is both a 32-bit ODBC and a 64-bit ODBC in all 64-bit versions of Windows Server.  Depending on the application and database requirements you may need to use one or the other.  However, if you’re not sure, you can always open up both instances and configure the ODBC setting in one instance to mirror the other.

The first step is to open the ODBC Data Source Administrator.  This can be found on the Start menu by navigating to All Programs, Administrative Tools and then clicking on Data Sources (ODBC).  By default, this will open the 64-bit version.  If you also want to open the 32-bit version its usually easier to navigate to it’s directory.  In most cases it should be found in C:\Windows\SysWOW64\odbcad32.exe

data_sources_admin_window

This window has several tabs but for simply allowing the OS to communicate with the database you really only need to be concerned with the User DSN and System DSN tabs.  Both of these can be used to connect to a back-end system.  The difference between these is that the User DSN will only expose the configured data source to the user that is currently logged into the system while the System DSN will be available to any user or account logged into the system.

To begin configuring a new data source select the System DNS tab and click Add…

The Create New Data Source window will appear.  Depending on the applications installed on your server, you may have a variety of drivers to choose from.  In this case I only have two options to choose from, SQL Server or SQL Server Native Client.  Deciding which driver to use really depends on the application that will be communicating with the database.  In most cases you should probably use SQL Server Native Client as it contains both the SQL OLE DB provider and the SQL ODBC driver to support native connectivity to SQL Server.  Simply put, its more robust.

create_new_data_source

Once you have selected the appropriate driver click Finish.

A new window will appear which will be used to create a new data source to SQL Server.  Enter a name and description for the data source and then specify the Server that will be used for the connection.

In the example below I am creating a connection to a Dynamics GP 2015 SQL Server lab environment.

sqlent_gp_odbc

Once you have provided the name, description and server, click Next.

The next window allows you to specify how the ODBC will connect.  By default the option With Integrated Windows authentication is selected.  If you want the connection to use a domain account then use this option.  If you want to use SQL Server authentication then select With SQL Server authentication using a login ID and password entered by the user. I typically use this option.

In the example below I am providing the ‘sa’ credentials so that I can test the connection.  Typically, with most connection strings, you want to avoid using ‘sa’ but in this case its fine as the credentials are not stored after the DSN is created.  Also, with some applications, specifically Dynamics GP, the user passwords are encrypted so if you want to test the connection to the Dynamics database you must use the ‘sa’ password.

By default the option to Connect to SQL Server to obtain default settings for the additional configuration options will be checked.  It is best to leave this checked.

odbc_credentials_window

Once the credentials have been added click Next.

Depending on the application and level of visibility you want to provide to the data source you can choose to select a default database or further configure the connection settings.  In most cases you can keep the default settings intact and simply click Next to finish configuring the data source.

default_db_options_window

The final screen, shown below, allows you to set the default language for SQL Server system messages as well as configure several other options for regional settings and log files.  I generally do not make any changes to this screen with the exception of checking the box next to Save long running queries to the log file.  However, I only do this on production servers.  If you choose to do this, you will also want to change the path to a directory that is available to the users as it will default to your user account directory.

language_log_setup_window

Once you have reviewed the options and made any desired changes, click Finish.

A window will appear displaying a summary of the configured settings.  Review these to make sure everything appears correct.  If all is good go ahead and click the button to Test Data Source…

If the credentials you provided are correct and are able to log into the specified server you should received the following message.

odbc_test_complete

Click OK to close out the test screen and then click OK to close out the Summary screen.  You should now see the newly created data source under the System DSN tab in the ODBC Data Source Administrator.new_odbc_created_display

Your front end server can now communicate with the SQL Server database.  To test this just launch the application and attempt to log in (if necessary).  If you are able to connect and retrieve data from the back-end database then you’re all set.